After upgrading my project from (dotnet core 2/ef core 2) to (dotnet core 3/ef core 3) almost all of my entity framework LINQ queries are broken. while I already read this it's still unclear to know what to do.
Here are some examples that I have problem with:
var league = await dbContext.League.LastAsync();
While this code worked just fine in ef core 2 it throws exception in ef core 3. The only workaround I could find about this was the following code which is still not what I want since it's not async like before.
var league = dbContext.League.AsEnumerable().Last();
Another example that throws the same exception is the following code:
var user = await dbContext.User.FirstOrDefaultAsync(u => u.UserId == userId && string.Equals(u.Token, token, StringComparison.InvariantCulture));
I still can use AsEnumerable() but the async version of FirstOrDefault is not available there so that's not an option. can somebody guide me with this ?
Here's the exception:
System.InvalidOperationException: The LINQ expression 'Last<League>(DbSet<League>)' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Answer to your question is going to be really verbose because it's corresponds to some different changes happened in EF Core 3.0; So, let's just consider a little part of it.
As you've mentioned in your question, microsoft has a somehow confusing description about the changes on version 3.0 in this post.
The first part of the above post is: 'LINQ queries are no longer evaluated on the client'. It says, before developers used to write queries that have two parts in it; One part, was a query on database, and another part was an expression that was only know for client code. In this situation
client evaluation of potentially expensive expressions only triggered a warning. But in the new version, EF core only allows the last Select() call to be evaluated on the client, and throws an exception when there is expressions that cannot be converted to either SQL or parameter.
To clear this part up let's take a look at an example described by Diego Vega in his EF Core 3.0 announcement blog post.
Switch to client evaluation explicitly: If your query filters data based on an expression that cannot be translated to SQL, you may need to switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync() in the middle of the query. For example, the following query will no longer work in EF Core 3.0 because one of the predicates in the where clause requires client evaluation:
var specialCustomers = context.Customers .Where(c => c.Name.StartsWith(n) && IsSpecialCustomer(c));
But if you know it is reasonable to process part of the filter on the client, you can rewrite the query as:
var specialCustomers = context.Customers .Where(c => c.Name.StartsWith(n)) .AsEnumerable() // Start using LINQ to Objects (switch to client evaluation) .Where(c => IsSpecialCustomer(c));
In the above example
IsSpecialCustomer(c) is a method that cannot be converted to SQL because it's a C# method that it's only available in client code. So developers should either rewrite the query in a form that can be translated, or query on database and then evaluate database results to client using
.AsEnumerable() and then It's possible to filter results based on
IsSpecialCustomer(c) returned value.
This is why still you can access
AsEnumerable() in your code.
Now, let's peek on why
FirstOrDefaultAsync() method is not available?
Well, there is two reasons that causes this situation.
I've answered first reason before: The code to detect non-composable SQL was removed in version 3.0.
And the second one is: query pipeline does not understand async queryable operators in the expression tree (e.g.: when you're trying to access it on
All in all, there is a couple of interesting posts you can read:
OrderByDesc() for some property and then
the invariant compare is not translated, was probably client evaluated previously. Depending on your database collation setting you could probably just do a normal equals here.
AsEnumerable() on your
DbSet without any filters will pull down all data locally, not something you want to do in production. Try rewriting to above and monitor the generated SQL to ensure that you get performant queries.