How to do outer join in LINQ to Entities?

Question

I need to do a left outer join on two 'tables'. The standart way to do it is use DefaultIfEmpty() method:

Code Snippet

from lPnpItem in PnpItems

join lPnpItemValue in PnpItemValues on lPnpItem.Id equals lPnpItemValue.PnpItem.Id into lValues

from lValue in lValues.DefaultIfEmpty()

select new

{

PnpItemName = lPnpItem.Name,

PnpItemValue1Planned = lValue.PlannedValue,

PnpItemValue1Actual = lValue.ActualValue

}

 

But DefaultIfEmpty() is not supported by LINQ to Entities. What can I do? Is there some workaround?

 

PnpItems

--------

Id: int

Name: string

 

PnpItemValues

-------------

Id: int

PnpItem: PnpItem

PlannedValue: decimal

ActualValue: decimal

 

There could be one or zero value for an item. I need to get list of items with their values (or null if for an item no value). I cannot join 'tables' into one table. Actually my case is more complex but this sample illustrates my problem nicely.

Answer 1

Here is a simple alternative (using the inverse navigation property):

 

Code Snippet

from lPnpItem in PnpItems

select new { Item = lPnpItem, ItemValues = lPnpItem.PnpItemValues };

 

 

Thanks,

-Colin

Answer 2

All LINQ to Entity group joins perform a left outer join by default:  http://msdn.microsoft.com/en-us/library/bb399367.aspx.  (In C#, the syntax for group join is simply 'join'). 

This should generate a left outer join if you're using LINQ to Entity:

from lPnpItem in PnpItems

join lPnpItemValue in PnpItemValues on lPnpItem.Id equals lPnpItemValue.PnpItem.Id into lValues

select new

{

PnpItemName = lPnpItem.Name,

PnpItemValue1Planned = lValue.PlannedValue,

PnpItemValue1Actual = lValue.ActualValue

}