EF Linq Error after change from dotnet Core 2.2.6 to 3.0.0

Question

I'm trying to upgrade a solution to the new Core Framework 3.0.0. Now I'm having a small issue I don't understand.

Look, this method was unproblematic in 2.2.6:

public async Task<IEnumerable<ApplicationUser>> GetBirthdayUsersCurrentMonth()
    {
        return await ApplicationDbContext.Users
            .Where(x => x.Gender != ApplicationUser.GenderTypes.generic)
            .Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)
            .Where(x => x.RetireDate == null)
            .OrderBy(x => x.BirthDate.GetValueOrDefault())
            .ToListAsync();
    }

Now in 3.0.0 I get a Linq Error saying this:

InvalidOperationException: The LINQ expression 'Where( source: Where( source: DbSet, predicate: (a) => (int)a.Gender != 0), predicate: (a) => a.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)' 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()

When I disable this line:

.Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)

The error is gone but off course I get all users. And I can't see an error in this query. Could this perhaps be a bug in EF Core 3.0.0?

Answer 1

The reason is that implicit client evaluation has been disabled in EF Core 3.

What that means is that previously, your code didn't execute the WHERE clause on the server. Instead, EF loaded all rows into memory and evaluated the expression in memory.

To fix this issue after the upgrade, first, you need to figure out what exactly EF can't translate to SQL. My guess would be the call to GetValueOrDefault(), therefore try rewriting it like this:

.Where(x => x.BirthDate != null && x.BirthDate.Value.Month == DateTime.Now.Month)

Answer 2

As you are trying to upgrade your solution's .netCore version to 3.0, I will answer your question in the scope of a person performing an upgrade:

By referencing the EF Core 3.0 breaking changes official docs, you will find the line

LINQ queries are no longer evaluated on the client

Your query below will no longer be evaluated client side because GetValueOrDefault() cannot be interpreted by EF:

.Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)

The reason this was working prior to 3.0 was because it evaluates everything prior to the segment where it cannot translate to raw SQL, then evaluate on the client (c#) side the rest of the segments. This means that your code is roughly evaluated to:

return (await ApplicationDbContext.Users
            .Where(x => x.Gender != ApplicationUser.GenderTypes.generic).ToListAsync()) //sql evaluated till here
            .Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)
            .Where(x => x.RetireDate == null)
            .OrderBy(x => x.BirthDate.GetValueOrDefault())
            .ToList();

This is no longer allowed in EF Core 3.0 because the rationale was that hiding away client side evaluation is disadvantageous in production with larger datasets whereas in development, performance hits may be overlooked.

You have 2 solutions.

The preferred is to rewrite the affected line to be something like this, with the defaultMonthValue being a const int with some default month integer that was used inside your GetValueOrDefault() extension.

.Where(x => (x.BirthDate != null && x.BirthDate.Value.Month == DateTime.Now.Month) || (x.BirthDate == null && defaultMonthValue == DateTime.Now.Month))

The second, but not recommended solution is to explicitly add .AsEnumerable() before the problem segment here to force EF to evaluate the prior statements.

.AsEnumerable() // switches to LINQ to Objects
.Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)

Some tips for people who intend to migrate to 3.0 from 2.2 and want to test the client evaluation breaking change in your 2.2 codebase prior to actual migration:

As from Microsoft docs, add the following to your startup.cs to simulate 3.0 client side query throws.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
        .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

Answer 3

As Daniel Hilgarth wrote his solution is fine and works. The Addition of Wiktor Zychla seems to work, too. I rewrote the method as follows:

public async Task<IEnumerable<ApplicationUser>> GetBirthdayUsersCurrentMonth()
    {
        return await ApplicationDbContext.Users
            .Where(x => x.Gender != ApplicationUser.GenderTypes.generic)
            //.Where(x => x.BirthDate.GetValueOrDefault().Month == DateTime.Now.Month)
            .Where(x => x.BirthDate.Value.Month == DateTime.Now.Month)
            .Where(x => x.RetireDate == null)
            .OrderBy(x => x.BirthDate)
            .ToListAsync();
    }

So, as it seems in Core 3.0.0 it's not a good idea to use as mentioned evaluation-methods event if these are standard methods served by the classes itself.

Thanks for your help.