How to do a bulk update with a join on other tables?

[shaulbehr]

Description

I'd like to do an update that translates roughly to the following SQL (in Postgres flavor):

update Foo f
set Rank = (select count(*) from Foo f2 where f2.GroupId = f.GroupId and f2.Score > f.Score) + 1,
  Status = 2
where f.Status = 1

In C#, I'd expect it to look something like:

await db.Foo.Where(f => f.Status == 1)
    .UpdateAsync(f => new Foo {
        Rank = db.Foos.Count(f2 => f2.GroupId == f.GroupId && f2.Score > f.Score) + 1
    });

Exception

This is what happens when I execute the code above:

System.InvalidOperationException : variable 'f' of type 'Foo' referenced from scope '', but it is not defined
   at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
   at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at Z.EntityFramework.Plus.BatchUpdate.GetInnerValues[T](IQueryable`1 query, Expression`1 updateFactory, IEntityType entity)
   at Z.EntityFramework.Plus.BatchUpdate.Execute[T](IQueryable`1 query, Expression`1 updateFactory)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.Update[T](IQueryable`1 query, Expression`1 updateFactory, Action`1 batchUpdateBuilder)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.<>c__DisplayClass2_0`1.<UpdateAsync>b__0()

Further technical details

• EF version: 3.1.0 preview 3
• EF Plus version: 3.0.23
• Database Provider: Npgsql

[JonathanMagnan]

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

[roji]

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?

[JonathanMagnan]

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 ;)

[shaulbehr]

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?

[JonathanMagnan]

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 ;)

[shaulbehr]

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!