Using LINQ’s FluentAPI, left/outer joins are difficult. With a little work, it’s possible to perform left outer joins, though.
If you have a Parent/Child table setup we can use a combination of “SelectMany” and the “DefaultIfEmpty” IEnumerable method to directly perform a left outer join. This works well with EF too. Imagine if I have typical Employee/Transactions parent-child relationship (think sales). We could perform a left outer join from Employee to Invoices in order to filter out the child Transactions while also filtering out Employees:
var list1 =
dbContext.Employees.Where(x => x.FirstName.Contains("a"))
.SelectMany(x => x.Transactions.DefaultIfEmpty(),
(x, y) => new ProjectionClass { Employee = x, Transaction = y })
.Where(x => x.Transaction == null || x.Transaction.InvoiceNumber.Contains("whatever"))
.ToList();
This will yield something pretty close to what we want. We will get Employees, filtered, and then Transactions filtered. When this gets translated by Entity Framework, though, some Employees will get filtered out if they have other matches against Transacitons. This is due to the way EF constructs the WHERE clause. The Employees would have to have NO transactions or have some Transactions that match our WHERE clause. It becomes more akin to an INNER join in this regard.
Another interesting thing we can do to have the outer join’s “ON” clause perform filtering, which means that our Employees don’t get filtered based on our child collections filtering, is to use LINQ’s GroupJoin.
var list2 = dbContext.Employees.Where(x => x.FirstName.Contains("a"))
.GroupJoin(dbContext.Transactions.Where(x.Transaction.InvoiceNumber.Contains("whatever")),
e => e.Id,
t => t.EmployeeId,
(e, g) =>
g.Select(t => new ProjectionClass() { Employee = e, Transaction = t })
.DefaultIfEmpty(new ProjectionClass() { Employee = e, Transaction = null }))
.SelectMany(x => x)
.ToList();
Note the use of a simple ProjectClass and “DefaultIfEmpty” again. There are a couple of interesting things about the GroupJoin. Again, EF will put the child (Transaction) filtering into the left join’s “ON” clause/filter. This makes it so that Employees are only filtered upon that which we explicitly have told LINQ to filter.
Finally, we can replicate the “GroupJoin” behavior with our strongly-typed navigation properties by using a “SelectMany” within our original model’s Transactions collection.
var list3 =
dbContext.Employees.Where(x => x.FirstName.Contains("a"))
SelectMany(x => x.Transactions.AsQueryable().Where(t => t.Transaction.InvoiceNumber.Contains("whatever")).DefaultIfEmpty(),
(x, y) => new ProjectionClass { Employee = x, Transaction = y })
.ToList();
You can see that we remove the “lower level,” or final, WHERE clause. This effectively rolls the filtering into our left join just like the “GroupJoin” did.