Aggregate function on owned type fails in EF7

[ajcvickers]

Code below works in EF Core 6.0.

var query = context.Books
    .GroupBy(post => post.Author.Id)
    .Select(grouping => new
    {
        Author = grouping.Key,
        TotalCost = grouping.Sum(post => post.Detail.Price),
    });

Unhandled exception. System.InvalidOperationException: Translation of 'EF.Property<BookDetail>(EntityShaperExpression:
    Book
    ValueBufferExpression:
        ProjectionBindingExpression: Outer
    IsNullable: False
, "Detail")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateAggregateMethod(EnumerableExpression enumerableExpression, MethodInfo method, List`1 scalarArguments)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryTranslateAggregateMethodCall(MethodCallExpression methodCallExpression, SqlExpression& translation)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 103

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
}

public class Book
{
    public int Id { get; set; }
    public Author Author { get; set; } = default!;
    public BookDetail Detail { get; set; } = default!;
}

[Owned]
public class BookDetail
{
    public int Price { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var toast = new Author { Name = "Toast" };
            var alice = new Author { Name = "Alice" };
            
            context.AddRange(
                new Book { Author = alice, Detail = new() { Price = 10 } },
                new Book { Author = alice, Detail = new() { Price = 10 } },
                new Book { Author = toast, Detail = new() { Price = 12 } },
                new Book { Author = toast, Detail = new() { Price = 12 } },
                new Book { Author = toast, Detail = new() { Price = 14 } });
            
            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var query = context.Books
                .GroupBy(post => post.Author.Id)
                .Select(grouping => new
                {
                    Author = grouping.Key,
                    TotalCost = grouping.Sum(post => post.Detail.Price),
                });

            foreach (var group in query)
            {
                Console.WriteLine(group);
            }
        }
    }
}

[smitpatel]

var query = context.Books
    .GroupBy(post => post.Author.Id, e => new { e, e.Detail })
    .Select(grouping => new
    {
        Author = grouping.Key,
        TotalCost = grouping.Sum(post => post.Detail.Price),
    });

[ajcvickers]

Note from triage: this is the result of a design change in query and cannot easily be fixed. We will document as a breaking change in 7.0.

[MoazAlkharfan]

Is this a workaround? if it is, it doesn't seem to work on json columns too :(

var query = context.Books
.SelectMany(x => x.Histories)
.Where(x => x/*JsonColumn*/.Snapshot /*JsonColumn*/.Published)
.GroupBy(grouping => grouping.Snapshot.PublishDate, e => new { e, e.Snapshot })
.Select(group => EF.Functions.DateDiffDay(group.Key, group.OrderByDescending(a => a.Snapshot.LastPublishDate).FirstOrDefault()!.Snapshot.LastPublishDate)))
.Sum();

Translation of 'EF.Property<BookHistory>(EntityShaperExpression: 
    app.entities.BookHistory
    ValueBufferExpression: 
        ProjectionBindingExpression: Inner
    IsNullable: False
, "Snapshot")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.

[ajcvickers]

@smitpatel The workaround query still throws the same exception.

[smitpatel]

Exception only happens for owned type since when expanding lambdas over aggregate function, we don't expand owned navigations (which are not expanded by nav expansion). There is no way to expand beforehand, since it is 2nd level lambda. The expansion in subquery form would happen automatically when translating but the issue here is that we end up throwing exception from EF.Property before we conclude that it cannot be translated in aggregate form. To fix this, we should stop throwing directly in https://github.com/dotnet/efcore/blob/main/src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs#L729 rather use error details which will revert the query back to subquery form translation (what happens for non-owned navigation).
For better fix in future, we should expand out the navigation from sql translator and try to put in aggregate only if it didn't cause additional table to be added else go to subquery form. (Till we figure out how to lift join tables.

[Dwipraj]

I am not sure what I am doing wrong, below code is throwing the following error.

I am using EF Core 8.0.4

QUERY

var result = await _dataContext.Parts.Include(x => x.Ac)
    .Where(x => x.AcId == acNo)
    .GroupBy(x => new { x.AcId, x.Ac.AcName }, y => new { y, y.PartBloDetail })
    .Select(x => new
    {
        AcId = x.Key.AcId,
        AcName = x.Key.AcName,
        PartCount = x.Count(),
        BloCount = new BloCountDto
        {
            TotalBloCount = x.Count(y => !string.IsNullOrEmpty(y.PartBloDetail.BloName))
        }
    }).FirstOrDefaultAsync();

MODEL CLASS

public class Part : AuditableEntityWithId<int>
{
    public int PartNo { get; set; }
    public string PartName { get; set; } = default!;
    public int AcId { get; set; }
    public Ac? Ac { get; set; }

    public PartBloDetail? PartBloDetail { get; set; }
}

public class PartBloDetail
{
    public string? BloName { get; set; }
}

public class Ac : AuditableEntity
{
    public int AcId { get; set; }
    public string AcName { get; set; } = default!;
}

ERROR

Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: Translation of 'EF.Property<PartBloDetail>(StructuralTypeShaperExpression:
          Api.Entities.DbEntities.Part
          ValueBufferExpression:
              ProjectionBindingExpression: Outer
          IsNullable: False
      , "PartBloDetail")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.ProcessPredicate(EnumerableExpression enumerableExpression, LambdaExpression lambdaExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryTranslateAggregateMethodCall(MethodCallExpression methodCallExpression, SqlExpression& translation)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.TryTranslateAggregateMethodCall(MethodCallExpression methodCallExpression, SqlExpression& translation)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
         at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
         at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         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.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         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__DisplayClass12_0`1.<ExecuteAsync>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         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.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at Api.Services.PlaceInformationService.GetAcInformationByAcNo(Int32 acNo) in C:\Users\PC\source\repos\Project\Src\Api\Services\PlaceInformationService.cs:line 35
         at Api.Routes.PlaceInformationRoute.GetAcInformationByAcNo(Int32 id, IPlaceInformationService placeInformationService) in C:\Users\PC\source\repos\Project\Src\Api\Routes\PlaceInformationRoute.cs:line 30
         at Microsoft.AspNetCore.Http.RequestDelegateFactory.ExecuteTaskResult[T](Task`1 task, HttpContext httpContext)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
         at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
         at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
         at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareImpl.<Invoke>g__Awaited|10_0(ExceptionHandlerMiddlewareImpl middleware, HttpContext context, Task task)

[roji]

@Dwipraj please open a new issue for that.

[Dwipraj]

Ok, but it seems I am missing something with the Count() method because replacing Count with Sum() not throwing any exception and also returns the result as expected.

var result = await _dataContext.Parts.Include(x => x.Ac)
    .Where(x => x.AcId == acNo)
    .GroupBy(x => new { x.AcId, x.Ac.AcName }, y => new { y, y.PartBloDetail })
    .Select(x => new
    {
        AcId = x.Key.AcId,
        AcName = x.Key.AcName,
        PartCount = x.Count(),
        BloCount = new BloCountDto
        {
            TotalBloCount = x.Sum(y => !string.IsNullOrEmpty(y.PartBloDetail.BloName) ? 1 : 0)
        }
    }).FirstOrDefaultAsync();

Should I open a new issue for that or not? @roji