SQL Difference EF 6 vs EF Core

c# entity-framework entity-framework-core sql sql-server

Question

I'm migrating a Sofware from EF 6 to EF Core. During testing I noticed a difference on how the Linq is interpreted.

My Linq

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)

1
2
1/10/2018 12:26:36 PM

Popular Answer

(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 User.Id and 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 Any:

db.Deputees.Where(d => !db.Users.Any(u => u.Id == d.UserId))

...which translates into a NOT EXISTS.

3
1/13/2018 11:34:00 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow