Migrating from EF Core 2 to EF Core 3

1. use OrderByDesc() for some property and then FirstAsync(). (https://github.com/aspnet/EntityFrameworkCore/issues/18211)

2. 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.

Calling 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.

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 EF.CompileQuery()).

All in all, there is a couple of interesting posts you can read:

40 breaking bad changes in ef core 3

Announcing entity framework core 3.0 preview 9 and entity framework 6.3 preview 9

EF core issues on github