Is it feasible to do an outside join with Linq to Entity Framework?

entity-framework left-join linq

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.

1
18
11/20/2009 1:58:59 PM

Accepted Answer

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.

36
11/20/2009 2:45:59 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow