Entity Framework Core - Client Vs Server Evaluation

EF Core along with the Database provider decides which part of the LINQ query will be executed in the database and which part gets executed on the client side. This blog post tries to explain Client Vs Sever evaluation along with improvements in EF Core 3.0

Let me show you a single line of LINQ code and ask you a question.

var albums = _dbContext.Albums.Where(a => a.Title.ToUpper() == "Facelift").ToList();

It's simple - All I am trying to do is get the list of Albums where Title is 'Facelift'

Now the question is,

• Will EF Core tries to get all the Albums from the Database to the Application and then filter the records by album title?
  ( Or )
• Will it try to filter the records in the database itself and get the filtered result to the application?

If your answer is ( b ), then you are absolutely correct!

Here is the SQL generated by database provider,

SELECT "a"."AlbumId", "a"."ArtistId", "a"."Title"
FROM "Album" AS "a"
WHERE upper("a"."Title") = 'Facelift'

Now, let me show you another piece of code ( again, it is just a single line ).

var albums = _dbContext.Albums.Where(a => a.ArtistId == new Random().Next()).ToList();

All am trying to do is to get an album by a random artist

Can you tell what happens in this case? 

a) Will EF Core load all the albums from the database into the application and then filter the records?

(or)

b) Will it filter the records in the database and then get the filtered result to the application? 

If your answer is (a) and you are very confident about it, then you have definitely tried this in EF Core < 3.0 versions. And yes, you are correct!

But you might have also observed that this LINQ statement has resulted in a warning.

With EF Core >= 3.0, You will get an exception like the one shown below,

And why is that?

Because the database provider (Microsoft.Entityframworkcore.Sqlite in my case) is unable to convert the LINQ where to SQL where as it has no idea about .NET's Random() method. But why can't it just fetch all the records and then filter the records at application (client) side? you might ask.  Yes, it can, but it chose NOT TO because of the performance reasons. It felt it is not a good idea to load all the records into the application and then filter. So, it threw an exception. But, through exception message, it is telling us the options we have,

1. Rewrite the query in a way that the db provider can translate it to a SQL query. 
2. Switch to client evaluation by explicitly calling AsEnumerable() or ToList()  or their async counterparts.

Let's go with option 1 and try to re-write our query and it works!

var _random = new Random().Next();
var albums = Albums.ToList().Where(a => a.ArtistId == _random);

But, there might be cases where we might not be able to re-write the query, and so, let's see second option.

With second option, we are telling EF Core to load all the records from the database and then filter ( and I take full responsibility of the application's poor performance ).

var albums = _dbContext.Albums.ToList().Where(a => a.ArtistId == _random);

Notice the position of ToList() in the above statement. And the generated SQL is,

SELECT "a"."AlbumId", "a"."ArtistId", "a"."Title"
FROM "Album" AS "a"

And that's it!  I personally like how EF Core >=3.0 versions throw an exception when the LINQ we wrote tries to get all the records from db (server) and then filter at application ( client ) side and asking us to re-look at the options available to reduce the record size from db.

So, the takeaway is this:

1. EF Core along with the Database provider decides which part of the query will execute on client-side (application) and which part is sent to database ( server ). This is called as Client Vs Server evaluation.
2. If EF Core figures out that the DB provider cannot translate certain part of the query and if it results in full data load, it will throw an exception in EF Core >=3.0 versions ( and a warning in <3.0 versions ).
3. If we are sure that loading full data into application will not cause any performance issues, try to switch to client evaluation by explicitly mentioning AsEnumerable or ToList.

IMPORTANT NOTE

The examples mentioned above were tried on SQLite database with Microsoft.EntityFrameworkCore.Sqlite package. These LINQ statements might work in other db providers if the db provider can translate the Random() method.

Further References: https://docs.microsoft.com/en-us/ef/core/querying/client-eval