Entity Framework Inner Join and Left Join

Inner Join 

就是合併兩張表中有交集的資料

Left Join 

也是在合併兩張表,但不同於Inner Join的是, 若在右方表格找不到對應於左方表格的資料時, 預設會給null代替

在Entity Framework中如何實現Inner Join

from c in Students

join o in Addresses on c.StudentId equals o.StudentId

select new { c.StudentName , o.AddressName}

對應的T-SQL

SELECT [t1].[StudentName], [t0].[AddressName]

FROM [Students] AS [t0]

INNER JOIN [Addresses] AS [t1] ON [t1].[StudentId] = [t0].[StudentId]

在Entity Framework中如何實現Left Join

from c in Categories

join o in Products on c.CategoryID equals o.CategoryID  as ps

from p in ps.DefaultIfEmpty()

select new { c.CategoryName ,p.ProductID, p.ProductName }

ps.DefaultIfEmpty 表示join後的結果會存在ps, 若在Products中找不到對應資料就會回傳null

對應的T-SQL

SELECT [t0].[Categories], [t1].[Products]
FROM [Categories] AS [t0]
LEFT OUTER JOIN [Products] AS [t1] ON [t1]. CategoryID = [t0].[CategoryID]