I'm migrating a Sofware from EF 6 to EF Core. During testing I noticed a difference on how the Linq is interpreted.
app.Deputies .Include(d => d.User) .Where(d => d.User == null) .ToList()
IN EF 6 it results in a query (simplified for reading purposes) like this
SELECT d.* FROM Deputy d LEFT JOIN User u ON u.Id = d.UserId WHERE u.Id IS NULL
IN EF Core the SQL Looks like this
SELECT d.* FROM Deputy d LEFT JOIN User u ON u.Id = d.UserId WHERE d.UserId IS NULL
Even if I do
.Where(d => d.User.Id == null) doesn't change the generated query.
The Configuration for EF 6 looks like this:
.HasOptional(d => d.User).WithMany().HasForeignKey(d => d.UserId);
The Configuration for EF Core looks like this:
.HasOne(d => d.User).WithMany().HasForeignKey(d => d.UserId);
Did I miss something in the config or any Idea an how to achieve the same SQL like in EF 6?
(I'm using SQL Server)
EDIT: There's no FK between Deputy and User on the DB. (Only in the model)
(To turn my comments into an answer)
This is an interesting example of how a seemingly innocent change in implementation may have unexpected side effects.
EF6 filters the join at the right-hand side of the join:
SELECT d.* FROM Deputy d LEFT OUTER JOIN User u ON d.UserId = u.Id WHERE u.Id IS NULL
EF-core filters on the left-hand side:
SELECT d.* FROM Deputy d LEFT OUTER JOIN User u ON d.UserId = u.Id WHERE d.UserId IS NULL
The SQL query optimizer isn't crazy and it spots that the second query can be reduced to:
SELECT d.* FROM Deputy WHERE d.UserId IS NULL
The query plans of query 2 and 3 are identical: only an index scan, whereas query 1 contains a nested loop to combine deputy and user results.
So in the normal situation where there is a foreign-key constraint between
Deputy.UserId the EF-core implementation is better than the former one. But in your case there is no FK. So
Deputees may have
UserIds that don't match any
User and they're filtered out by the second query, not by the first query, while the LINQ queries are identical.
The difference can be really significant, so normally we should benefit form this improved query generation in EF-core (assuming that it's deliberate). However, we have to face it, the EF6 version is a better translation of what the LINQ query expresses semantically.
You can work around the issue either by explicitly coding the outer join:
from d in db.Deputees join u in db.Users on d.UserId equals u.Id into ug from u in ug.DefaultIfEmpty() // LINQ eqivalent of outer join where u == null select d
...which filters on
u.Id, or by using
db.Deputees.Where(d => !db.Users.Any(u => u.Id == d.UserId))
...which translates into a