Quick Tip: Outer joins with LINQ
The syntax for a flat left outer join in LINQ is a bit different from the corresponding SQL. Using Lambda syntax it looks like this.
(Note: to make these samples work in LINQPad you have to remove the references to “context”. See this post for details.)
var q =
context.PCProjectItems
.GroupJoin
(
context.PCProjectEntries,
item=>item.PCProjectItemID,
entry=>entry.ProjectItemID,
(item,entry)=>new{item.PCProjectItemID,entry}
)
.SelectMany
(
itemEntry=>itemEntry.entry.DefaultIfEmpty(),
(itemEntry,entry)=>new
{
itemEntry.PCProjectItemID,
Goods=(decimal?)entry.GoodsAmountInBaseCurrency
}
);
In comprehension syntax, the same query looks like this:
var q = from item in context.PCProjectItems
join entry in context.PCProjectEntries
on item.PCProjectItemID equals entry.ProjectItemID into itemEntries
from itemEntry in itemEntries.DefaultIfEmpty()
select new
{
item.PCProjectItemID,
Goods = (decimal?)itemEntry.GoodsAmountInBaseCurrency
};
Take your pick which syntax you prefer, or you can mix them in the same query if you like! In any case, both queries result in the same SQL being issued to the database:
SELECT [t0].[PCProjectItemID], [t1].[GoodsAmountInBaseCurrency] AS [Goods]
FROM [PCProjectItem] AS [t0]
LEFT OUTER JOIN [PCProjectEntry]
AS [t1] ON [t0].[PCProjectItemID] = [t1].[ProjectItemID]
