Open shaulbehr opened 4 years ago
Hello @shaulbehr ,
This LINQ would have been valid for SQL Server but unfortunately, that's not currently valid for PostgreSQL
.
We already tried in the past to support it a few times but we never succeed. At this moment, unless someone provides us a solution, we don't plan to pass more time to try it again.
Best Regards,
Jonathan
Performance Libraries
context.BulkInsert(list, options => options.BatchSize = 1000);
Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus
Runtime Evaluation
Eval.Execute("x + y", new {x = 1, y = 2}); // return 3
C# Eval Function • SQL Eval Function
Hey, I'm the guy working on the Npgsql EF Core provider for PostgreSQL. I'm not aware of anything specific in PostgreSQL for supporting bulk updates: regular, standard SQL UPDATE commands are fully supported like on SQL Server or any other database. What solution exactly are you waiting for someone to provide?
Hello @shaulbehr , @roji
My developer looked at it and we might be able now to support it (and a lot more case that we were not able previously)
We are currently developing a new feature named InsertFromQuery
(should be released tomorrow) which works like the Batch Update
but for inserting. In this new feature, we had to push the limit of our code further by supporting anonymous query.
So when my developed checked it, he suggested to me that we adjust the Batch Update
to support an anonymous query as well.
The way you wrote it will not be supported. However, you will be able to achieve a similar result by moving the logic in the select part:
await db.Foo.Where(f => f.Status == 1).Select(f => new { Rank = db.Foos.Count(f2 => f2.GroupId == f.GroupId && f2.Score > f.Score) + 1 })
.UpdateAsync(f => new Foo {
Rank = f.Rank
});
I will keep you updated but we will definitely look at it very soon while everything from the InsertFromQuery
is still fresh in our mind ;)
Hi @JonathanMagnan
Thanks for following up on this!
I'm confused by your proposed replacement syntax. If you use a .Select()
before the .UpdateAsync()
, then it's no longer an IQueryable<Foo>
, but rather an IQueryable
of an anonymous class with a Rank
property. So how would you join the Foo
table to this anonymous class?
You are right, you will probably need to include your key in the anonymous type such as:
await db.Foo.Where(f => f.Status == 1).Select(f => new {
RankID = f.RankID,
Rank = db.Foos.Count(f2 => f2.GroupId == f.GroupId && f2.Score > f.Score) + 1
})
.UpdateAsync(f => new Foo {
Rank = f.Rank
});
Since we know the Foo
keys, we only need to check if that's part of the anonymous type as well. If that's the case then we make the join.
So no matter what is in the Query
, the only thing that matters is being able to make the join. I guess there is probably a few more issues but we have not yet started to look at it ;)
Yeah, just at a glance I can see a few things in that snippet that won't work, but I have total confidence that you'll work it out. Kudos for producing this library; I'm getting huge benefit out of it!
Description
I'd like to do an update that translates roughly to the following SQL (in Postgres flavor):
In C#, I'd expect it to look something like:
Exception
This is what happens when I execute the code above:
Further technical details