首先我們先看一下Join
public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);
第一個參數outer就是你連接的左面的集合,第二個inner是你要與之連接的集合,第三個outerKeySelector就是要用outer的哪個鍵來進行連接,第四個innerKeySelector同理,最後一個就是返回的類型。用法如下:
NorthwindEntities dbContext = new NorthwindEntities(); var data = dbContext.Employees.Join(dbContext.Orders, e => e.EmployeeID, o => o.EmployeeID, (e, o) => new { EmployeeID = e.EmployeeID, OrderID = o.OrderID, Address = o.ShipAddress, EmployeeAddress = e.Address });
上面的代碼對應的SQL語句如下:
SELECT [Extent1].[EmployeeID] AS [EmployeeID], [Extent2].[OrderID] AS [OrderID], [Extent2].[ShipAddress] AS [ShipAddress], [Extent1].[Address] AS [Address] FROM [dbo].[Employees] AS [Extent1] INNER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[EmployeeID] = [Extent2].[EmployeeID]
很明顯是用的內連接方式,但是會不會有其他方式呢?那麼再來一段代碼看看:
var data = dbContext.Employees.Join(dbContext.Orders, e => e.EmployeeID, o => o.EmployeeID, (e, o) => new { EmployeeID = e.EmployeeID, OrderID = o.OrderID });
對應的SQL語句如下:
SELECT [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderID] AS [OrderID] FROM [dbo].[Orders] AS [Extent1] WHERE [Extent1].[EmployeeID] IS NOT NULL
看到了吧,沒有inner join了,本意是要查找Employee對應的訂單,但是上面的代碼只返回了EmpoloyeeID和OrderID,因為查找Employee對應的訂單,本來就可以直接在Orders中查找的,所以EF還是對此代碼做了優化,免去了inner join。
既然連接已經寫好了,那麼要對返回的結果進行篩選你可以調用Where等擴展方法了。
接下來看看GroupJoin
public static IQueryable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, IEnumerable<TInner>, TResult>> resultSelector);
來看個例子:Products表有一個CategoryID的屬性,但是Category中並沒要存儲對應的ProductID,如果要查找Category下的Product我們該怎麼做呢?如下:
var data = dbContext.Categories.GroupJoin(dbContext.Products, c => c.CategoryID, p => p.CategoryID, (c, p) => new { CategoryID = c.CategoryID, ProductList = p });
對應的SQL語句如下:
SELECT [Project1].[CategoryID] AS [CategoryID], [Project1].[C1] AS [C1], [Project1].[ProductID] AS [ProductID], [Project1].[ProductName] AS [ProductName], [Project1].[SupplierID] AS [SupplierID], [Project1].[CategoryID1] AS [CategoryID1], [Project1].[QuantityPerUnit] AS [QuantityPerUnit], [Project1].[UnitPrice] AS [UnitPrice], [Project1].[UnitsInStock] AS [UnitsInStock], [Project1].[UnitsOnOrder] AS [UnitsOnOrder], [Project1].[ReorderLevel] AS [ReorderLevel], [Project1].[Discontinued] AS [Discontinued] FROM ( SELECT [Extent1].[CategoryID] AS [CategoryID], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[SupplierID] AS [SupplierID], [Extent2].[CategoryID] AS [CategoryID1], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[Discontinued] AS [Discontinued], CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Categories] AS [Extent1] LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID] ) AS [Project1] ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC