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?

Manning

instead of adding 3 'Where' condition why don't you use the '&&' operation? – Spavined

@Dr.Roggia: the translation handles both scenarios correctly. It's not the problem here. – Lalonde

Peter B, no, I've tried it. This brings up a compiler error. Lambda Expression may not be a null propagating operator – Manning

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)

Waitress

Assuming EF Core's translation is comparable to EF6, the first clause (x.BirthDate != null) could possibly be redundant as the second should capture only rows that have a value. – Lalonde

@WiktorZychla: Good point, that is possible. However, EF Core is a completely new development and has had a lot of known differences from EF6, so that assumption might be incorrect – Waitress

I just can't seem to get this working with a nullable DateTime. This won't work ...&& DateTime.Now.Subtract(p.LastTest.Value).TotalDays / 30 > p.PAssetCategory.PATIntervalMonths)) whereas this will: ... && 120 / 30 > p.PAssetCategory.PATIntervalMonths . Am I missing something obvious, as it seems @Monsee has it working? (I'm assuming you're BirthDate is nullable?) – Ineslta

do you know if it's still the same things with the 3.1 version? – Lancinate

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

Colon

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.

Manning

No, this is a very good change by EF Core 3. Implicit client evaluation is potentially a huge performance problem. ORM - like EF - will never be able to translate all C# code to SQL. Just in case you didn't know, that's what's happening under the hood: EF takes your C# code and builds a SQL query out of it. – Waitress

Sometimes there are no way to translate al the sentence to SQL. F.E: When you want to format a value or make a complex formula. Then you know there is an impact in the performance. Is not better to show a warning or a "deprecating" instead and don't break the compatibility? – Delossantos

Answer 4

For future readers.

I got this same issue.

By making a simple substitution for an "inline date calculation", I was able to get past the issue.

"BEFORE" IQueryable (below) (does not work):

(just focus on this part in the "before"

"perParentMaxUpdateDate.UpdateDateStamp < DateTime.Now.Add(cutOffTimeSpan)"

)

    IQueryable<MyChildEntity> filteredChildren = from chd in this.entityDbContext.MyChilds
                                                 join perParentMaxUpdateDate in justParentsAndMaxUpdateTs
                                                 on new { CompoundKey = chd.UpdateDateStamp, chd.MyParentUUID } equals new { CompoundKey = perParentMaxUpdateDate.UpdateDateStamp, perParentMaxUpdateDate.MyParentUUID }
                                                 where magicValues.Contains(chd.MyChildMagicStatus)
                                                 && perParentMaxUpdateDate.UpdateDateStamp < DateTime.Now.Add(cutOffTimeSpan) /* <- FOCUS HERE */
                                                 select chd;

---

"AFTER" IQueryable (below) (that works fine):

    DateTime cutOffDate = DateTime.Now.Add(cutOffTimeSpan); /* do this external of the IQueryable......and use this declaration of the DateTime value ..... instead of the "inline" time-span calcuation as seen above */

    IQueryable<MyChildEntity> filteredChildren = from chd in this.entityDbContext.MyChilds
                                                 join perParentMaxUpdateDate in justParentsAndMaxUpdateTs
                                                 on new { CompoundKey = chd.UpdateDateStamp, chd.MyParentUUID } equals new { CompoundKey = perParentMaxUpdateDate.UpdateDateStamp, perParentMaxUpdateDate.MyParentUUID }
                                                 where magicValues.Contains(chd.MyChildMagicStatus)
                                                 && perParentMaxUpdateDate.UpdateDateStamp < cutOffDate /* <- FOCUS HERE */
                                                 select chd;

I'm guessing that because of my issue and the issue of the original question.......DateTimes are a little trickier.

So while (yes) it is annoying that previously working code..stops working......understanding the "defaults to NOT running code on the client" is very important for performance.

PS, I'm on 3.1

My package references for completeness.

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.3" />

Cozy

Answer 5

I had the same issue when i upgraded to efcore 3.0. What has changed in efcore 3.0 is that when he can not execute the query, he will just stop and throw an error.

The behaviour was the same if the previous versions of efcore, but when he did not recognized the parameters, he would just execute the query without the parameters (get more data then u wanted) but maybe you never noticed.

You can check that in the logs.

If you want to execute queries in efcore 3.0 you'll have to use fields in your query that are also in your db tables. He will not recognize object properties that are not mapped to de db

it's a pain right now to re-write queries but it will be a win in a while

Reaganreagen

Answer 6

In my case the issue was by using Ticks from DateTime

 x.BirthDate.Ticks

Slaw

Answer 7

If your list is IQueryable is where, you need to use Tolist ().

list.ToList().Where(p => (DateTime.Now - p.CreateDate).Days <= datetime).AsQueryable();

Ibrahim