[C#.NET][LINQ] Left Outer Join
準備動作
安裝 LINQPad
安裝資料庫,如下圖:
實作 Inner Join
LINQ 的 Join 預設是 Inner Join
查詢運算式(Query Expression):
var query = from c in Categories
join p in Products on c.CategoryID equals p.CategoryID
select new
{
c.CategoryID,
c.CategoryName,
p.ProductName
};
query.Dump();
方法架構查詢(Method-Based Query):
var query = Categories.Join(Products,c => c.CategoryID, p => p.CategoryID,
(c,p) => new
{
c.CategoryID,
c.CategoryName,
p.ProductName
});
query.Dump();
兩者執行的結果應該要一致,如下圖:
查看一下 T-SQL ,如下圖:
實作 Left Outer Join
Left Outer Join 就是左邊的資料全部都要有,即便右邊沒有資料也要以 null 代替
查詢運算式(Query Expression):
var query = from c in Categories
join p in Products on c.CategoryID equals p.CategoryID into cp
from p in cp.DefaultIfEmpty()
select new
{
c.CategoryID,
c.CategoryName,
p.ProductName
};
query.Dump();
方法架構查詢(Method-Based Query):
這裡是用 GroupJoin + SelectMany 來實現
var query = Categories.GroupJoin(Products , c => c.CategoryID , p => p.CategoryID, (c,p) => new
{
Categories = c,
Products = p
}).SelectMany(c => c.Products.DefaultIfEmpty(), (c, p) => new
{
c.Categories.CategoryID,
c.Categories.CategoryName,
p.ProductName
});
query.Dump();
運行結果如下圖
查看一下 T-SQL ,如下圖:
文章出自:http://www.dotblogs.com.tw/yc421206/archive/2014/07/11/145907.aspx
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET