EF Core 7 - Apply where() after Select throws error-CodePudding

I have a query which does a group by and then selects and then tries to apply a Where() filter on the select. but it throws error after I apply the filter for OpenBalance on the latest selected row

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 error is thrown after this query

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

Error msg is

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

CodePudding user response：

Try to replace finalQuery with this one:

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;