Left Outer Join with 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 ?

this issue has been recently fixed - problem was that EF was making some incorrect assumptions about queries with DefaultIfEmpty and if one wanted to compose anything on top of DIE() call, EF would throw. This fix will be available in the next release or the nightly builds. — Maurycy, Jan 11 '16 at 23:47

score 3 · Accepted Answer · answered Dec 10 '15 at 20:49

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.

Left Outer Join with Entity Framework Core

1 Answers1

Linked