[SOLVED] EF Core (LINQ) – The Query expression could not be Translated

Issue

I have the following query for searching records by parameter.

DateTime CurrentDate = DateTime.Now;
var pastDueInvoices =  Context.Invoice.AsNoTracking()
                .Select(i => new InvoiceDTO
                {
                    ID = i.ID,
                    InvoiceNumber = i.InvoiceNumber
                    DaysPastDue =  i.Balance <= 0 ? 0 : CurrentDate.Subtract(i.InvoiceDate.AddDays(i.ProductNav.DiscountDays.GetValueOrDefault())).Days,
                });

Then I used this query to display only invoices where DaysPastDue > 0

 if (request.ShowPastDueInvoices)
{                    
  pastDueInvoices =  pastDueInvoices.Where(pd => pd.DaysPastDue > 0);
}

when i search for past due invoices I get the following error

.Join( outer: DbSet, inner: i =>
EF.Property<Nullable>(i, "ProductID"), outerKeySelector: p =>
EF.Property<Nullable>(p, "ID"), innerKeySelector: (o, i) => new
TransparentIdentifier<Invoice, Product>( Outer = o, Inner = i ))
.Where(i =>
__CurrentDate_0.Subtract(i.Outer.InvoiceDate.AddDays((double)i.Inner.DiscountDays.GetValueOrDefault())).Days > 0)’ 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 either AsEnumerable(), AsAsyncEnumerable(),
ToList(), or ToListAsync().

Any Help?

Solution

Most likely EF Core cannot translate the Subtract method to SQL. Accoring to this code EF core has a DateTime.AddDays provider built in but I was unable to find such a provider for Substract or DateDiff in the official documentation. So, you can try using EF.Functions which gives you access to DbFunctions class with helper methods for SQL operations EF.Functions.DateDiffDay i.e.

DateTime CurrentDate = DateTime.Now;
var pastDueInvoices =  Context.Invoice.AsNoTracking()
            .Select(i => new InvoiceDTO
            {
                ID = i.ID,
                InvoiceNumber = i.InvoiceNumber
                DaysPastDue =  i.Balance <= 0 ? 0 : EF.Functions.DateDiffDay(CurrentDate, i.InvoiceDate.AddDays(i.ProductNav.DiscountDays.GetValueOrDefault())),
            });

I’m not sure about this GetValueOrDefault() method if it will be translated to SQL. If it is some custom extension method written by you most likely not. Maybe you should try to replace it with something like i.ProductNav.DiscountDays ?? 0 or maybe you don’t need to manually convert nullable days column value to 0, there is a good chance that SQL server will directly translate a null column value to 0 for use in DATEADDSQL function. Try both cases.

Also, the original EF 6 (not core) version of this will use DbFunctions

DateTime CurrentDate = DateTime.Now;
var pastDueInvoices =  Context.Invoice.AsNoTracking()
        .Select(i => new InvoiceDTO
        {
            ID = i.ID,
            InvoiceNumber = i.InvoiceNumber
            DaysPastDue =  i.Balance <= 0 ? 0 : DbFunctions.DiffDays(CurrentDate, DbFunctions.AddDays(i.InvoiceDate, i.ProductNav.DiscountDays)),
        })

Answered By – Anton Kovachev

Answer Checked By – David Goodson (BugsFixing Volunteer)