The Problem
If you’ve had the privilege of updating EF Core from version 2.x, you’ve no doubt enjoyed all kinds of Linq to SQL errors and null reference exceptions. There’s a big change in 3.x that’s behind this. In older versions, when your Linq to SQL couldn’t successfully be translated to SQL at run time, parts would be run in SQL, and the rest brought into memory to be executed.
This could lead to very significant performance problems. For a simple example, if you were querying from a large SQL table with a where clause, this behaviour meant that at run time it was possible that the entire table would be read into memory first, and only then any effort made to select the correct record(s).
Admittedly, there was always a warning when this happened, and I know all developers definitely play close attention to runtime warnings (cough).
So the change in versions 3+ means that when the query can’t be translated to SQL, an exception is thrown, unless you specifically intended it to be brought into memory (by casting it ToList(), for example). Suddenly, you have to be a lot more on-the-ball with writing your queries in Linq. On the whole that’s a good thing, but it doesn’t feel like great news when you’re looking after a lot of code that’s just broken instead of performing poorly.
A bunch of slow solutions
No getting around this one, you’re cleaning up your Linq to SQL. Here are some things that used to (appear to) work, but now won’t.
Using projected values in where clauses
One thing you can’t do is use a value that’s only calculated in the final “Select” projection as part of a where clause, for example. If you think about how this translates to SQL this makes perfect sense. How can you compare something to a projected value unless you project that value for all records first? For example, say you have a DateTime field in your table, but in your final Select you calculate another string “Expired”, derived from this DateTime. That’s fine, but if you try to add a where clause that uses this “Expired” string, it first has to be calculated for all records. Instead, write your comparison using the original database fields, in this case the original datetime field.
Joining in-memory objects to SQL tables
Especially when using Linq’s query syntax (which looks a little like SQL), it’s not uncommon to see an in-memory collection like a List joined to a SQL table. This cannot be translated to SQL.
Often this is used to carry out a “where-in” type query, comparing to see if a database field matches any values within a known collection. If this is the intended functionality, you can achieve the same thing by checking whether your in-memory List contains the value of the database field in your where clause. This can be translated to SQL successfully at runtime as a where clause:
myListInMemory.Contains(table.fieldValue)
Calling C# functions against values from the database
Trying to call C# methods against database field values also won’t work. For example, if your comparisons use .Equals() or .GetValueOrDefault() against database fields, they can’t be translated to SQL. Most of these type of operations have approximate equivalents that will achieve the intended result, such as == instead of Equals(), or an explicit cast to a nullable type instead of GetValueOrDefault().
Referencing an in-memory collection inside a Select statement
If you use your Select projection to choose an additional value from an in-memory list, this is functionally the same as a join between in-memory and database objects. As shown above, this can’t be done.
Instead, you can run your database query and pull it into memory first, then add the values from your in-memory list. There shouldn’t be any significant performance problem, since this doesn’t result in getting anything unnecessary from the database.
Saving related entities in a single transaction
In EF Core 2.x, when creating two or more related records in a single transaction, it was possible to write the creation of the first item, then assign its ID, directly to the foreign key field of the database table, e.g:
var dog = new Dog { Name = "Fido" };
var dogBone = new Bone { Flavour = "Chicken", DogId = dog.Id };This technique doesn’t seem to work in 3.x, and instead you need to assign the entire first entity to the relevant parameter of the second entity (this parameter is defined by EF Core in the context based on the foreign key relationship):
var dog = new Dog { Name = "Fido" };
var dogBone = new Bone { Flavour = "Chicken", Dog = dog };Good luck
I’m sure I’ve forgotten a whole lot more of these, but I hope you might get some quick wins from the examples above.
This migration is quite tricky if you have a lot of complex Linq to SQL. Bear in mind that these simple issues are sometimes very difficult to track down because often the SQL is intentionally only executed some time after the query was initialised and modified by business logic. The exception will only be thrown when the query is finally executed.
Ultimately, for new code it’s much better this way. We have a clear indication of whether the Linq is being evaluated in SQL correctly or in memory, and therefore we understand the performance implications of our decisions. But if you’ve a lot of complicated queries to migrate that were performing “well enough”, you have my sympathy.