[C#.NET][LINQ] Inner Join & Left Outer Join

  • 20871
  • 0
  • LINQ
  • 2015-12-28

[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

Image result for microsoft+mvp+logo