Can't use .AddDays in EF core 3.1 query

Question

I'm trying to migrate my project from .net core 2.2 to 3.1,the following code worked fine but now gives that error:

System.InvalidOperationException: The LINQ expression 'DbSet .Where(h => h.DATAI.AddDays((double)Convert.ToInt32((object)h.NRZILE)) >= __date_0 && h.DATAI.AddDays((double)Convert.ToInt32((object)h.NRZILE)) < __AddDays_1 || h.DATAI <= __AddDays_1 && h.DATAI.AddDays((double)Convert.ToInt32((object)h.NRZILE)) >= __AddDays_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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

public async Task<IActionResult> getOp(int day, int month, int year, int daysToShow)
    {
    var date = new DateTime(year, month, day);

    return Ok(await db.HT_REZ
    .Where(r => (r.DATAI.AddDays(Convert.ToInt32(r.NRZILE)) >= date && r.DATAI.AddDays(Convert.ToInt32(r.NRZILE)) < date.AddDays(daysToShow))
     || (r.DATAI <= date.AddDays(daysToShow) &&  r.DATAI.AddDays(Convert.ToInt32(r.NRZILE)) >= date.AddDays(daysToShow))
    )
    .Select(r => new { r.ID,  .... })
    .ToListAsync()
    );
    }

I found that implicit client evaluation has been disabled in EF Core 3 and I have to use AsEnumerable before filtering data,but was not able to fix above query

thanks for any help

Answer 1

The problem is not the DateTime.AddDays call, which is supported.

Note that the exception message shows the whole Where operator, so the problem (unsupported) should be in some other expression inside the predicate.

In this specific case the unsupported is the Convert.ToInt32(r.NRZILE) expression, which for NRZFILE type int? matches Convert.ToInt32(object).

While EF Core supports a subset of Convert class methods, in general you should avoid them (except conversions from string) by using C# casts or null coalescing operator.

e.g. replace all occurrences of

Convert.ToInt32(r.NRZILE)

with

(int)r.NRZILE

or (in the context of DateAdd)

r.NRZILE ?? 0

and the issue will be solved.

Answer 2

You need to use the DbFunctions extension methods and think of a different approach: try to calculate the difference between dates instead (DateDiffDay method). Please see https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctions?view=efcore-3.1.

Also, I don't think that you can use Convert in an expression, so if the values are already numbers, just use them as such.