ExecuteUpdate over subquery does not translate after efcore 10.0.1 #37771
Description
Bug description
ExecuteUpdate over subquery working properly with efcore 10.0.1, but after update to efcore 10.0.2 or 10.0.3 does not translate.
my datebase is PostgreSQL but It seems error is in efcore not efcore.pg
sql query generate with efcore 10.0.1
UPDATE pieces AS p
SET used = b2."Qty"
FROM (
SELECT b.piece_id AS "PieceId", COALESCE(sum(b.qty - (
SELECT COALESCE(sum(b1.qty), 0)::int
FROM build_dones AS b1
WHERE b1.work_id = b.id)), 0)::int AS "Qty"
FROM build_works AS b
WHERE b.qty > (
SELECT COALESCE(sum(b0.qty), 0)::int
FROM build_dones AS b0
WHERE b0.work_id = b.id)
GROUP BY b.piece_id
) AS b2
WHERE p.id = b2."PieceId"Your code
await using var context = new FactoryContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
await (from p in db.Pieces
join n in from t in from w in db.BuildWorks
where w.Qty > db.BuildDones.Where(d => d.WorkId == w.Id).Sum(d => d.Qty)
select new { w.PieceId, Qty = w.Qty - db.BuildDones.Where(d => d.WorkId == w.Id).Sum(d => d.Qty) }
group t by t.PieceId into g
select new { PieceId = g.Key, Qty = g.Sum(p => p.Qty) } on p.Id equals n.PieceId
select new { p.Used, n.Qty }).ExecuteUpdateAsync(u => u.SetProperty(p => p.Used, p => p.Qty));
public class FactoryContext : DbContext
{
public DbSet<Piece> Pieces { get; set; }
public DbSet<BuildWork> BuildWorks { get; set; }
public DbSet<BuildDone> BuildDones { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql("Host=localhost;Username=test;Password=test")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class Piece
{
public int Id { get; set; }
public string Name { get; set; }
public int Qty { get; set; }
public int Used { get; set; }
}
public class BuildWork
{
public int Id { get; set; }
public int PieceId { get; set; }
public int Qty { get; set; }
}
public class BuildDone
{
public int Id { get; set; }
public int WorkId { get; set; }
public int Qty { get; set; }
}Stack traces
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The LINQ expression 'DbSet<Piece>()
.Join(
inner: DbSet<BuildWork>()
.Where(b => b.Qty > DbSet<BuildDone>()
.Where(b0 => b0.WorkId == b.Id)
.Sum(b0 => b0.Qty))
.GroupBy(b => b.PieceId)
.Select(g => new {
PieceId = g.Key,
Qty = g
.AsQueryable()
.Sum(e => e.Qty - DbSet<BuildDone>()
.Where(b1 => b1.WorkId == e.Id)
.Sum(b1 => b1.Qty))
}),
outerKeySelector: p => (object)p.Id,
innerKeySelector: e0 => (object)e0.PieceId,
resultSelector: (p, e0) => new TransparentIdentifier<Piece, <>f__AnonymousType51<int, int>>(
Outer = p,
Inner = e0
))
.Where(ti => False || (int?)ti.Outer.PhaseId == @phaseid)
.Select(ti => new {
Used = ti.Outer.Used,
Qty = ti.Inner.Qty
})
.ExecuteUpdate(new Tuple<Delegate, object>[]{ new Tuple<Delegate, object>(
ti => ti.Used,
ti => ti.Qty
) })' could not be translated. Additional information: The following lambda argument to 'SetProperty' does not represent a valid property to be set: 'ti => ti.Used'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteUpdateAsync[TSource](IQueryable`1 source, Action`1 setPropertyCalls, CancellationToken cancellationToken)
Verbose output
EF Core version
10.0.3
Database provider
Npgsql.EntityFrameworkCore.PostgreSQL
Target framework
.NET 10.0.3
Operating system
Windows 11
IDE
Visual Studio Code 1.109.5
Metadata
Metadata
Assignees
Type
Fields
Give feedbackNo fields configured for Bug.