CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkentity-framework-core
gsharp
gsharp

Reputation: 27927

SQL Difference EF 6 vs EF Core

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)

Upvotes: 2

Views: 6307

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109119

(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.

Upvotes: 3

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

These two queries 

SELECT
  d.*
FROM Deputy d
LEFT JOIN User u ON u.Id = d.UserId
WHERE u.Id IS NULL

and

SELECT
  d.*
FROM Deputy d
LEFT JOIN User u ON u.Id = d.UserId
WHERE d.UserId IS NULL

Are semantically identical if a Deputy has a Foreign Key on UserId.

The only difference between the queries is the case where Deputy has a non-null UserId, but that UserId does not exist in the User table. That can't happen if you have a Foreign Key on Deputy.

So EF's code generation in both cases is correct. EF Core's query is better as the filter can be evaluated before the join.

Upvotes: 3

Related Questions