EF Core 7中GroupBy后Select再用Where过滤抛出翻译异常

问题场景 #

编写EF Core查询时，先执行GroupBy分组，再进行Select投影，之后尝试对投影结果的OpenBalance字段应用Where()过滤，添加过滤条件后抛出LINQ表达式无法翻译的异常。

相关代码 #

var billTable = _context.Set<Bill>();

var billQuery = billTable
    .Where(w => w.PaymentStatus != PaymentStatus.None)
    .Where(w => w.OperationalStatus == OperationalBillStatus.Approved &&
    (
        w.FinancialStatus == FinancialBillStatus.Pending ||
        w.FinancialStatus == FinancialBillStatus.Approved ||
        w.FinancialStatus == FinancialBillStatus.OnHold ||
        w.FinancialStatus == FinancialBillStatus.PartiallyApproved
    ))
    .AsQueryable();

billQuery = ApplyOptionalFilters(filter, billQuery);

var temporalBillQuery = billTable
    .TemporalAll()
    .Where(w => billQuery.Select(s => s.Id).Contains(w.Id))
    .Where(w => w.UpdatedOn!.Value.Date <= filter.AsAtDate.Date)
    .OrderByDescending(o => o.DueDate!.Value)
    .AsQueryable();

var billAsAtDateQuery = temporalBillQuery.Select(s => new
{
    UpdatedOn = s.UpdatedOn,
    BillId = s.Id,
    Aging = (filter.AsAtDate.Date - s.DueDate!.Value.Date).Days,
    s.InvoiceNumber,
    s.InvoiceDate,
    DueDate = s.DueDate,
    OpenBalance = (s.LineItemTotal + s.VATAmount - s.TotalPaidAmount)
}).AsQueryable();

var finalQuery = billAsAtDateQuery
    .GroupBy(g => g.BillId)
    .Select(s => s.OrderByDescending(o => o.UpdatedOn).First())
    .AsQueryable();

if (filter.OpenBalanceMinAmount.HasValue)
{
    finalQuery = finalQuery.Where(w => w.OpenBalance >= filter.OpenBalanceMinAmount.Value);
}

抛出的错误信息 #

The LINQ expression 'DbSet<Bill>().TemporalAll()
    .Where(b => __ef_filter__CompanyIds_0.Contains(b.CompanyId) && b.IsDeleted == False)
    .Where(b => DbSet<Bill>()
        .Where(b0 => __ef_filter__CompanyIds_0.Contains(b0.CompanyId) && b0.IsDeleted == False)
        .Where(b0 => (int)b0.PaymentStatus != 1)
        .Where(b0 => (int)b0.OperationalStatus == 3 && (int)b0.FinancialStatus == 2 || (int)b0.FinancialStatus == 4 ||
(int)b0.FinancialStatus == 5 || (int)b0.FinancialStatus == 6)
        .Select(b0 => b0.Id)
        .Any(p => object.Equals(
            objA: (object)p, 
            objB: (object)b.Id)))
    .Where(b => b.UpdatedOn.Value.Date <= __filter_AsAtDate_Date_0)
    .OrderByDescending(b => b.DueDate.Value)
    .GroupBy(b => b.Id)
    .Select(g => g
        .AsQueryable()
        .OrderByDescending(e => e.UpdatedOn)
        .Select(e => new { 
            UpdatedOn = e.UpdatedOn, 
            BillId = e.Id, 
            Aging = (__filter_AsAtDate_Date_0 - e.DueDate.Value.Date).Days, 
            InvoiceNumber = e.InvoiceNumber, 
            InvoiceDate = e.InvoiceDate, 
            DueDate = e.DueDate, 
            OpenBalance = e.LineItemTotal + e.VATAmount - e.TotalPaidAmount
         })
        .First())
    .Where(e0 => e0.OpenBalance >= __filter_OpenBalanceMinAmount_Value_1)' 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. 

 Stack Trace:
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpressiontranslated, <>c__DisplayClass15_0&)    
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpressionmethodCallExpression)    
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpressionmethodCallExpression)    
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expressionquery)    
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expressionquery, Boolean async)    
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabasedatabase, 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](ObjectcacheKey, Func`1 compiler)    
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expressionquery, CancellationToken cancellationToken)    
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expressionexpression, CancellationToken cancellationToken)    
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationTokencancellationToken)    
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1source, CancellationToken cancellationToken)    
at VPMS.Persistence.Repositories.Reports.VendorReportRepository.APAgingDetailReport(Paginatorpaginator, APAgingReportFilter filter, CancellationToken token) in C:\Projects\Legacy HealthCare - Vendor Payment Management System\VPMS\VPMS.Persistence\Repositories\Reports\VendorReportRepository.cs:line 565    
at VPMS.Application.Reports.Services.ReportService.APAgingDetailReport(Paginatorpaginator, APAgingReportFilter filter, CancellationToken token) in C:\Projects\Legacy HealthCare - Vendor Payment Management System\VPMS\VPMS.Application\Reports\Services\ReportService.cs:line 59
at VPMS.Api.Controllers.V1.Reports.PaymentReportsController.APAgingDetailReport(Paginatorpaginator, APAgingReportFilter filter, CancellationToken token) in C:\Projects\Legacy HealthCare - Vendor Payment Management System\VPMS\VPMS.Api\Controllers\V1\Reports\PaymentReportsController.cs:line 48    
at lambda_method240(Closure, Object)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(ActionContextactionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object ] arguments)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvokerinvoker)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvokerinvoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealedcontext)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() 
--- End of stack trace from previous location ---    
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvokerinvoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvokerinvoker)    
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvokerinvoker)    
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpointendpoint, Task requestTask, ILogger logger)    
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContextcontext)    
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContextcontext)    
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContexthttpContext)    
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContexthttpContext, ISwaggerProvider swaggerProvider)    
at VPMS.Api.Middleware.ExceptionHandlerMiddleware.Invoke(HttpContextcontext) in C:\Projects\Legacy HealthCare - Vendor Payment Management System\VPMS\VPMS.Api\Middleware\ExceptionHandlerMiddleware.cs:line 25

解决方案 #

原因分析 #

EF Core无法将GroupBy后取First再过滤投影字段的逻辑转换成SQL，这种嵌套操作超出了EF Core的SQL翻译能力范围。

修复方案1：使用窗口函数替代GroupBy+First（推荐） #

用RowNumber()窗口函数为每个BillId的记录按UpdatedOn降序编号，筛选出编号为1的最新记录，之后再对OpenBalance进行过滤，这种写法EF Core可以正常翻译为SQL：

var billTable = _context.Set<Bill>();

var billQuery = billTable
    .Where(w => w.PaymentStatus != PaymentStatus.None)
    .Where(w => w.OperationalStatus == OperationalBillStatus.Approved &&
    (
        w.FinancialStatus == FinancialBillStatus.Pending ||
        w.FinancialStatus == FinancialBillStatus.Approved ||
        w.FinancialStatus == FinancialBillStatus.OnHold ||
        w.FinancialStatus == FinancialBillStatus.PartiallyApproved
    ))
    .AsQueryable();

billQuery = ApplyOptionalFilters(filter, billQuery);

var temporalBillQuery = billTable
    .TemporalAll()
    .Where(w => billQuery.Select(s => s.Id).Contains(w.Id))
    .Where(w => w.UpdatedOn!.Value.Date <= filter.AsAtDate.Date)
    .AsQueryable();

// 使用窗口函数获取每个BillId的最新记录
var billWithRowNumber = temporalBillQuery.Select(s => new
{
    UpdatedOn = s.UpdatedOn,
    BillId = s.Id,
    Aging = (filter.AsAtDate.Date - s.DueDate!.Value.Date).Days,
    s.InvoiceNumber,
    s.InvoiceDate,
    DueDate = s.DueDate,
    OpenBalance = (s.LineItemTotal + s.VATAmount - s.TotalPaidAmount),
    // 按BillId分组，UpdatedOn降序生成行号
    RowNumber = EF.Functions.RowNumber().Over(PartitionBy(s.Id).OrderByDescending(s.UpdatedOn))
});

var finalQuery = billWithRowNumber
    .Where(r => r.RowNumber == 1);

if (filter.OpenBalanceMinAmount.HasValue)
{
    finalQuery = finalQuery.Where(w => w.OpenBalance >= filter.OpenBalanceMinAmount.Value);
}

修复方案2：客户端过滤（仅适用于小数据量） #

如果数据量不大，可以先将GroupBy+First的结果加载到内存，再进行过滤。但注意这种方式会把所有符合分组条件的数据都拉到客户端，大数据量下会影响性能：

var finalQuery = billAsAtDateQuery
    .GroupBy(g => g.BillId)
    .Select(s => s.OrderByDescending(o => o.UpdatedOn).First())
    .AsEnumerable(); // 切换到客户端评估

if (filter.OpenBalanceMinAmount.HasValue)
{
    finalQuery = finalQuery.Where(w => w.OpenBalance >= filter.OpenBalanceMinAmount.Value);
}
// 后续调用ToListAsync等方法

内容的提问来源于stack exchange，提问作者thanzeel