efcore icon indicating copy to clipboard operation
efcore copied to clipboard
verified publisher icon dotnet

Sum over owned collection with property selector in nested Select inside SelectMany cannot be translated

Open Xriuk opened this issue 11 months ago • 2 comments

Bug description

From what I saw it seems that the bug is somewhere in NavigationExpandingExpressionVisitor, where it is causing the Sum expression to pass from c => c.Likes.Amount to o => Property(o, "Likes").Amount first and then o0 => Property(o, "Likes").Amount. So it seems that somewhere the new argument of the lambda is not being replaced inside the previous lambda body.

Your code

public class Blog {
	[Key]
	public int Id { get; set; }


	public ICollection<Post> Posts { get; set; } = null!;
}


public class Post {
	[Key]
	public int Id { get; set; }


	public ICollection<Comment> Comments { get; set; } = null!;
}


[Owned]
public class CommentLikes {
	public int Amount { get; set; }
}

[Owned]
public class Comment {
	[Required] // Does not make a difference if nullable or not
	public CommentLikes Likes { get; set; } = null!;
}


public class BlogContext : DbContext {
	public DbSet<Blog> Blogs { get; set; } = null!;
	public DbSet<Post> Posts { get; set; } = null!;

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		=> optionsBuilder.UseSqlServer("...");

	protected override void OnModelCreating(ModelBuilder modelBuilder) {
		modelBuilder.Entity<Post>()
			.OwnsMany(p => p.Comments);
	}
}


dbContext.Set<Blog>().SelectMany(b => b.Posts.Select(p => p.Comments.Sum(c => c.Likes.Amount))).ToList();

Stack traces

System.InvalidOperationException: 'The LINQ expression 'o' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitParameter(ParameterExpression parameterExpression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 1038
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 799
   in Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.SqlServer/Query/Internal/SqlServerSqlTranslatingExpressionVisitor.cs: riga 205
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 744
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 156
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression, Boolean applyDefaultTypeMapping) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 124
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression, Boolean applyDefaultTypeMapping) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 1836
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateAggregateWithSelector(ShapedQueryExpression source, LambdaExpression selectorLambda, Func`2 methodGenerator, Boolean throwWhenEmpty, Type resultType) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 2529
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSum(ShapedQueryExpression source, LambdaExpression selector, Type resultType) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 1234
   in Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.TranslateSum(ShapedQueryExpression source, LambdaExpression selector, Type resultType) in /_/src/EFCore.SqlServer/Query/Internal/SqlServerQueryableMethodTranslatingExpressionVisitor.cs: riga 160
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 464
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 269
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 62
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 84
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 549
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 1011
   in Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.SqlServer/Query/Internal/SqlServerSqlTranslatingExpressionVisitor.cs: riga 205
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 156
   in Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping) in /_/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs: riga 138
   in Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs: riga 233
   in Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) in /_/src/EFCore.Relational/Query/Internal/RelationalProjectionBindingExpressionVisitor.cs: riga 62
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 1080
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 412
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 269
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelectMany(ShapedQueryExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 1094
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 420
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 269
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 132
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 269
   in Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in /_/src/EFCore/Query/QueryableMethodTranslatingExpressionVisitor.cs: riga 62
   in Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression) in /_/src/EFCore.Relational/Query/RelationalQueryableMethodTranslatingExpressionVisitor.cs: riga 84
   in Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in /_/src/EFCore/Query/QueryCompilationContext.cs: riga 164
   in Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in /_/src/EFCore/Storage/Database.cs: riga 66
   in Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in /_/src/EFCore/Query/Internal/QueryCompiler.cs: riga 82
   in Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in /_/src/EFCore/Query/Internal/QueryCompiler.cs: riga 66
   in Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in /_/src/EFCore/Query/Internal/CompiledQueryCache.cs: riga 66
   in Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in /_/src/EFCore/Query/Internal/QueryCompiler.cs: riga 62
   in Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in /_/src/EFCore/Query/Internal/EntityQueryProvider.cs: riga 64
   in Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() in /_/src/EFCore/Query/Internal/EntityQueryable`.cs: riga 78
   in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) in /_/src/libraries/System.Private.CoreLib/src/System/Collections/Generic/List.cs: riga 83
   in System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) in /_/src/libraries/System.Linq/src/System/Linq/ToCollection.cs: riga 29
   in Program.<Main>$(String[] args) in Program.cs: riga 13

EF Core version

8.0.11

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8.0

Operating system

Windows 10

IDE

Visual Studio 2022 17.11.4

Xriuk avatar Jan 14 '25 13:01 Xriuk

Confirmed.

Minimal repro 
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

_ = await context.Set<Blog>()
    .SelectMany(b => b.Posts.Select(p => p.Comments.Sum(c => c.Likes.Amount)))
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public ICollection<Post> Posts { get; set; } = null!;
}

public class Post
{
    public int Id { get; set; }
    public ICollection<Comment> Comments { get; set; } = null!;
}

[Owned]
public class CommentLikes
{
    public int Amount { get; set; }
}

[Owned]
public class Comment
{
    public CommentLikes Likes { get; set; } = null!;
}

roji avatar Jan 15 '25 10:01 roji

Placing in backlog for now as a lower-priority bug (requires specific query operator operator, only one user report, only affects owned entity modeling).

roji avatar Jun 14 '25 07:06 roji