Why your EF Core code is now breaking aka the pesky .ToList()

tl;dr;

Some things that used to happen by magic in EF Core now need to happen explicitly by calling AsEnumerator() or ToList()
https://docs.microsoft.com/en-us/ef/core/querying/client-eval

Context

This post is purely anecdotal and really only reflects my opinions and past experiences, so I may have missed something completely obvious and for that I apologise in advance.

In entity framework anything that can be done on the server i.e in SQL will be for performance.

This is things such as this string == that string or this id == that id or this list contains this thing etc etc etc

Anything that cannot be converted to SQL i.e custom methods or string manipulation will be done on the client (at a basic level in C#).

These client side evaluations do require that any data you are trying to manipulate is loaded up into memory and this is where the problem begins as how this is handled has changed between EF Core 2 & 3.

EF Core < 3

Client side evaluations in EF Core before 3 would have been implicitly loaded into memory on the fly and executed without you seeing any difference.

This comes with a host of issues in itself. Imagine you are unknowingly loading millions of rows into memory to perform some kinda string.Equals() on a row to get down to 1 record. That's a highly hypothetical problem that I'm sure we have all encountered in one of our darker days that we no longer speak of in public spaces.

EF Core 3 tries to solve this issue

EF Core > 3

EF Core 3 and beyond will enforce a call to .AsEnumerable() or .ToList() or their async counterparts to explicitly load this stuff into memory.

If the call to explicitly pull rows into memory is absent then dotnet will throw a run time exception which is actually fairly helpful as it will tell you that the query you are running cannot be translated to SQL for execution on the server and that you should add a call to .ToList() or the like

Lets look at code (that i took from msdn :) )

public static string StandardizeUrl(string url)
{
    url = url.ToLower();

    if (!url.StartsWith("http://"))
    {
        url = string.Concat("http://", url);
    }

    return url;
}

var blogs = context.Blogs
    .Where(blog => StandardizeUrl(blog.Url).Contains("dotnet"))
    .ToList();

In EF Core < 3 the above would run perfectly and you would be fairly ignorant to its method of execution

However with the new changes in EF Core 3 and beyond the above would throw a run time exception because the StandardizeUrl method cannot be translated to SQL (and also that dotnet sees this as a massive performance drain, which it is).

Now the sucky thing about run time exceptions is if you have architected the solution poorly, maybe missed some SOLID principles or even just have a massive data integration layer you will have to regretion test the hell out of each possible path/LINQ query on the context but if you have some pretty nice integration testing or automated testing this should alleviate some of the pain.

To get the above to work in EF Core > 3 you would write something like this

var blogs = context.Blogs
    .AsEnumerable()
    .Where(blog => StandardizeUrl(blog.Url).Contains("dotnet"))
    .ToList();

The AsEnumerable call would load up the entirety of context.Blogs and then perform the Where clause on the client side.

What can I do before migrating?

You can add the following code to your OnConfiguring method within your db context:

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

This will prevent the ability to perform client query evaluations in EF Core 2 and in turn will let you see with better clarity the impact the code you are writing is having on performance

Conclusion

This change although slightly frustrating gives us more insight as developers and I think has certainly made me more aware of the LINQ that I am writing to pull data.

Where possible lean on the server side query and chunk down data as much as possible (using ids or compare using server friendly code) before you jump to client side

Notes from msdn

You may need to force into client evaluation explicitly in certain cases like following

• The amount of data is small so that evaluating on the client doesn't incur a huge performance penalty.
• The LINQ operator being used has no server-side translation.

By using AsEnumerable you would be streaming the results, but using ToList would cause buffering by creating a list, which also takes additional memory. Though if you're enumerating multiple times, then storing results in a list helps more since there's only one query to the database. Depending on the particular usage, you should evaluate which method is more useful for the case.

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

Thanks for reading