Is an outer join possible with Linq to Entity Framework

Question

There are many examples of outer join using Linq to Sql, all of them hinging on DefaultIfEmpty() which is not supported with Linq to Entity Framework.

Does this mean that outer join is not possible with Linq to Entity using .NET 3.5 (I understand that DefaultIfEmpty is coming with 4.0 --- but that's not an option at this time for me)

Could somebody please provide a concise example using Linq to EntityFramework.

Answer 1

In LINQ to Entities, think in terms of relationships rather than SQL joins. Hence, the literal equivalent of a SQL outer join on an entity Person with a one to zero or one relationship to CustomerInfo would be:

var q = from p in Context.People
        select new
        {
            Name = p.Name,
            IsPreferredCustomer = (bool?)p.CustomerInfo.IsPreferredCustomer
        };

L2E will coalesce the join, so that if CustomerInfo is null then the whole expression evaluates to null. Hence the cast to a nullable bool, because the inferred type of non-nullable bool couldn't hold that result.

For one-to-many, you generally want a hierarchy, rather than a flat, SQL-style result set:

var q = from o in Context.Orders
        select new 
        {
            OrderNo = o.OrderNo,
            PartNumbers = from od in o.OrderDetails
                          select od.PartNumber
        }

This is like a left join insofar as you still get orders with no details, but it's a graph like OO rather than a set like SQL.

Answer 2

Excellent answer! I was looking for a solution to use an outer join in L2E, but your response is the real solution: "think in terms of relationships rather than SQL joins". Thanks.