EF Core 7 - Select抛出错误后应用where()

Question

我有一个查询，它做一个组，然后选择，然后尝试在选择上应用Where()过滤器。但它抛出错误后，我应用过滤器的OpenBalance上最新选定的行

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

在此查询

之后抛出错误

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:ProjectsLegacy HealthCare - Vendor Payment Management SystemVPMSVPMS.PersistenceRepositoriesReportsVendorReportRepository.cs:line 565    
at VPMS.Application.Reports.Services.ReportService.APAgingDetailReport(Paginatorpaginator, APAgingReportFilter filter, CancellationToken token) in C:ProjectsLegacy HealthCare - Vendor Payment Management SystemVPMSVPMS.ApplicationReportsServicesReportService.cs:line 59
at VPMS.Api.Controllers.V1.Reports.PaymentReportsController.APAgingDetailReport(Paginatorpaginator, APAgingReportFilter filter, CancellationToken token) in C:ProjectsLegacy HealthCare - Vendor Payment Management SystemVPMSVPMS.ApiControllersV1ReportsPaymentReportsController.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:ProjectsLegacy HealthCare - Vendor Payment Management SystemVPMSVPMS.ApiMiddlewareExceptionHandlerMiddleware.cs:line 25

Answer 1

尝试用这个替换finalQuery:

var finalQuery = 
from d in billAsAtDateQuery.Select(d => new { g.BillId }).Distinct()
from b in billAsAtDateQuery
.Where(b => b.BillId == d.BillId)
.OrderByDescending(o => o.UpdatedOn)
.Take(1)
select b;