Grouping by multiple columns in EFCore results in InvalidOperationException-CodePudding

Say we have models A, B and C. A has a many-to-many relationship with B and with C. I want to create following aggregation:

 -------- ---------------- -------------- --------- 
|  a_id  |  b_value_sum   | c_value_sum  | c_count |
 -------- ---------------- -------------- --------- 
| 1      |  20            | 10           | 40      | 
 -------- ---------------- -------------- ---------

Query:

var query = 
    from a in context.A
    from b in a.B.DefaultIfEmpty()
    from c in b.C.DefaultIfEmpty()
    group new { b, c } by new { a.Id } into g
    select new
    {
        Id = g.Key.ToString(),
        ValueBSum = g.Sum(y => y.b.Value),
        ValueCSum = g.Sum(y => y.c.Value),
        ValueCCount = g.Count()
    };

Generated query:

SELECT 
    [c].[Id], 
    COALESCE(SUM([t].[Value]), 0.0), 
    COALESCE(SUM([t0].[Value]), 0.0), 
    COUNT(*)
FROM [A] AS [c]
LEFT JOIN (
    SELECT 
        [b].[Value], 
        [c1].[A_Id]
    FROM [AB] AS [c1]
    INNER JOIN [B] AS [b] ON [c1].[B_Id] = [b].[Id]
) AS [t] ON [c].[Id] = [t].[A_Id]
LEFT JOIN (
    SELECT 
        [s0].[Value], 
        [s].[A_Id]
    FROM [AC] AS [s]
    INNER JOIN [C] AS [s0] ON [s].[C_Id] = [s0].[Id]
) AS [t0] ON [c].[Id] = [t0].[A_Id]
GROUP BY [c].[Id]

Results in

System.InvalidOperationException: Operation is not valid due to the current state of the object.

One thing to notice is, that the outer select is missing the AS statements. E.g. AS BValueSum.

Replacing new { a.Id } with a.id resolves the issue. However I want to group by multiple columns, which always gives me the above error.

Stacktrace:

fail: UseCase[0]
       MyProject Request: Unhandled Exception for Request UseCase
      System.InvalidOperationException: Operation is not valid due to the current state of the object.
         at System.Linq.Expressions.ExpressionExtensions.GetConstantValue[T](Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.GetProjectionIndex(ProjectionBindingExpression projectionBindingExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
         at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, LambdaExpression& relatedDataLoaders, Int32& collectionId)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
         at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
         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.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
         at MyProject.Shared.Models.PaginatedList`1.CreateAsync(IQueryable`1 source, Int32 pageNumber, Int32 pageSize) in MyProject\Shared\Models\PaginatedList.cs:line 0
         at MyProject.Shared.Behaviours.ValidationBehaviour`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next) in MyProject\Shared\Behaviours\ValidationBehaviour.cs:line 35
         at MyProject.Shared.Behaviours.UnhandledExceptionBehaviour`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next) in MyProject\Shared\Behaviours\UnhandledExceptionBehaviour.cs:line 18

CodePudding user response：

Probably EF Core could not create projection for .ToString() from anonymous object.

Try to remove .ToString() and, if it is needed, post process records after materialization on the client side.