在EFCore中按多列分組導致InvalidOperationException

假設我們有模型 A、B 和 C。A 與 B 和 C 具有多對多關系。我想創建以下聚合：

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

詢問：

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

生成的查詢：

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]

結果是

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

需要注意的一件事是，外部選擇缺少 AS 陳述句。例如作為 BValueSum。

替換new { a.Id }為a.id解決問題。但是我想按多列分組，這總是給我上面的錯誤。

堆疊跟蹤：

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

uj5u.com熱心網友回復：

可能 EF Core 無法.ToString()從匿名物件創建投影。

嘗試洗掉.ToString()，如果需要，在客戶端實作后發布處理記錄。

標籤：C＃ 林克 实体框架核心

上一篇：使用kubernetes的Minio部署無法按預期作業

下一篇：如何在EFCore中選擇具有多個多對多關系和分組依據的多個