Efcore: How are we supposed to do LEFT (OUTER) JOIN in EF core 3+?

[jaoord]

The problem

In previous versions of EF/EF core, the way to do a left outer join was

c# from c in context.customers join o in context.orders on customer.Id equals orders.customerId into custOrderJoin from co in cusJoin.DefaultIfEmpty() where co == null select c.Id;

When performing the above query in EF core 3+, the following exception is generated:

InvalidOperationException: Processing of the LINQ expression 'DbSet .GroupJoin( outer: DbSet, inner: c => c.Id, outerKeySelector: o => o.CustomerId, innerKeySelector: (c, custOrderJoin) => new { c = c, custOrderJoin = custOrderJoin })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

The question

How can we do LEFT OUTER JOINS in EF core 3+ without having to do the joining client side? I expect joining thousands (or millions) of rows on the client to perfom very badly.

EF Core version: 3.1.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .net core 3.1

[roji]

You probably want to check out the documentation for LEFT JOINS, is that not working for you?

[jaoord]

Thanks. Isn't that what I already am doing? Except for the context.Set<Blog>() part but I don't see how that would change the result. EF has problems with the group join.

[jaoord]

Ok, so here's the problem.
In my original query I also did a where, so in pseudo code it was like

````c#
from a in _context.table1
join b in _context.table2 on a.id equals b.aid into bJoin
where a.status == Status.Joined
from b in bJoin.DefaultIfEmpty()
where b.Id == null
select {
...
}
`````

This resulted in the error above (InvalidOperationException: Processing of the LINQ expression by 'NavigationExpandingExpressionVisitor' failed).

However, when I move the where down, the query is working:

````c#
from a in _context.table1
join b in _context.table2 on a.id equals b.aid into bJoin
from b in bJoin.DefaultIfEmpty()
where b.Id == null
&& a.status == Status.Joined
select {
...
}
`````

I have no idea why that makes a difference because I cannot see the SQL generated by the original query (because it fails before that), but the error is gone now.

[roji]

/cc @smitpatel in case the above is interesting - possibly as an improved exception message.

[smitpatel]

Error message has been updated to client eval error in 5.0 already.