Recently when going through our shiny new ASP.NET Core application’s Seq logs, I accidentally spotted a few entries like this:
The LINQ expression ‘foo’ could not be translated and will be evaluated locally.
I dug around in the code and found the responsible queries. Some of them were quite complex with many joins and groupings, while some of the other ones were very simple stuff, like
You may have spotted the problem right away.
StringComparison.OrdinalIgnoreCase is a .NET concept. It doesn’t translate to SQL and you can’t blame EF Core for that. As a matter of fact if you run the same query in full-blown Entity Framework, you’ll get a
NotSupportedException telling you it can’t convert your perdicate to a SQL expression. And that’s a good thing! because it prompts you to review your query, and if you really want to have a predicate in your query that only makes sense in the CLR world, you can decide if doing a
ToList() at some point in your
IQueryable to pull down the results of your query from the database into memory makes sense. And when you have your results in memory you can continue with your query shenanigans without having to worry if the rest of your query is translatable to SQL. Or you may decide that you don’t need that
StringComparison.OrdinalIgnoreCase, because your database collation is case-insensitive anyway.
The point is, by default, you are in control and can make explicit decisions based on your circumstance.
Not anymore in Entity Framework Core. Apparently there’s this concept of mixed client/server evaluation in EF Core. What it effectively does is if you put stuff in an
IQueryable LINQ query that can’t be translated to SQL or a query in your underlying database, it tries to magically make it work for you, by taking the untranslatable bits out and running them locally! and it’s enabled by default!
That’s a huge and extremely dangerous behavior change compared to the full Entity Framework. Consider this familiar entity:
And imagine someone writing an EF query like this:
EF Core can’t translate
p.LastName.Equals("Amini", StringComparison.OrdinalIgnoreCase) into a query that can be run on the database, so what it does is, it pulls down the whole Persons table, as well as the whole Orders and Addresses tables from the database into the memory and then runs the
.Where(p => p.LastName.Equals("Amini", StringComparison.OrdinalIgnoreCase)) filter on the results. 🤦
It’s not hard to imagine the performance repercussions of that on any real-sized application with a significant number of users. It can easily bring down applications to their knees. Compare that to the full Entity Framework where you get an exception. The fact that this hugely different behavior is the default is mind blowing!
You could argue that it’s the developer’s fault for including something like
StringComparison.OrdinalIgnoreCase in the IQueryable prediate. While that’s true, you can’t blame people newer to EF for expecting something like that to magically work. Especially since it does! for more senior devs it can easily sneak past unnoticed when context switching between LINQ-to-entities and LINQ-to-objects.
Also, having untranslatable things like
StringComparison.OrdinalIgnoreCase in your query isn’t the only culprit that results in client evaluation. If you have too many joins or groupings, the query can become too complex for EF Core and make it fall back to local evaluation.
So you probably want to keep an eye on your logs if your EF Core queries are magically working without a hitch as you may be getting client evaluation. Or better yet, if you don’t want that additional cognitive overhead, disable it altogether and make it throw like the good old Entity Framework:
I believe languages and frameworks should always make it harder to make mistakes, especially ones like this with potentially devastating consequences, so I strongly disagree with this new default behavior and will be keeping it disabled.