Left Outer Join with Entity Framework Core

c# entity-framework-core

Question

I'm trying to perform a left outer join request with EF7 (7.0.0-rc1-final), vNext RC1 (rc1-final) and SQL Server 2014

Database :

Pet: Id, Name

User: Id, Name, #PetId

This one works:

var queryWorks = from u in _context.Users
                 join p in _context.Pets on u.PetId equals p.Id into pp
                 from p in pp.DefaultIfEmpty()
                 select new {
                     UserName = u.Name,
                     Pet = p
                 };

but this one doesn't work (Message = "Sequence contains no elements"):

var queryFails = from u in _context.Users
                 join p in _context.Pets on u.PetId equals p.Id into pp
                 from p in pp.DefaultIfEmpty()
                 select new {
                     UserName = u.Name,
                     PetName = (p == null ? "NULL" : p.Name)
                 };

SQL Server Profile 2014 shows me that the second request is not sent to the SQL Server. Why ?

Accepted Answer

I think it's your p.Name inside the projection of your second query that is not handled.

As of RC1, EF7 does not yet know how to do left outer joins. In short, they are aware that it's a really important thing to get right, and they are working on it.

It was reported in issue 3186 on github, and some of the devs commented on it.

I commented myself with another repro a little like yours.

Collaborator "maumar" remarks:

Problem is that in Linq (to objects) the concept of LOJ doesn't exist on its own.

Proposed fix is to represent optional navigation using SelectMany-GroupJoin-DefaultIfEmpty combination and then collapse this pattern into LOJ in our relational pipeline. Problem is that this creates much more complex queries (mainly due to introduction of subqueries) and currently breaks for majority of non-trivial cases. Those bugs need to be addressed before we can fix the problem with navigation property expansion.

We do recognize this as a high priority bug, as it potentially returns incorrect results.



Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why