# 分享幾個 LINQ to SQL 執行各種 Join 查詢的技巧

我發現許多人對使用 LINQ 執行 JOIN 查詢並不是那麼的熟悉，而且語法也不見得像 T-SQL 那樣直覺，但事實上只是我們比較熟悉 T-SQL 的語法與資料庫架構而已，當我們換成 [ORM](http://en.wikipedia.org/wiki/Object-relational_mapping) (Object-relational mapping) 技術後所有對資料的操作必須全部改以「物件」與「實體」來思考，所以在這轉換的過程經常轉不過去，連我自己也有時會忘記怎麼寫，也經常利用 [Linq Samples and the Sample Query Explorer](/post/2008/05/05/About-Linq-Samples-and-the-Sample-Query-Explorer.aspx) 幫我查出適當的寫法。

由於大多數人都是先學會 T-SQL 然後才學會 LINQ 的，因此兩者的觀念難免有些模糊不清的地方，尤其是在開發的時候都會不斷的以 **T-SQL 語法如何翻譯成 LINQ 語法** 這樣的思路在寫 LINQ，因此多少會經歷一些痛苦的轉換期，不過，學習新技術最好的方式是先暫時放下既有的經驗，試圖去瞭解為什麼要有這個新技術的出現，再來思考之間如何做轉換以及應注意的地方。

我們先來一段最基本的 LINQ to SQL 使用類似 T-SQL 的 INNER JOIN 資料查詢語法：

```
from c in Categories
from o in c.Products
select new { c.CategoryName, o.ProductID, o.ProductName }
```

我們可以從上述 LINQ 語法上就知道有兩個資料來源（因為有兩個 from 子句），而第二個 from 使用的是第一個 from 資料來源的關連屬性，因此產生 JOIN 需求，這段 LINQ 語法所產生的 T-SQL 語句如下：

```
SELECT [t0].[CategoryName], [t1].[ProductID], [t1].[ProductName]
FROM [Categories] AS [t0], [Products] AS [t1]
WHERE [t1].[CategoryID] = [t0].[CategoryID]
```

接著我們來看看另一個 INNER JOIN 的資料查詢語法，我們使用語意更為明確的 join 子句：

```
from c in Categories
join o in Products on c.CategoryID equals o.CategoryID 
select new { c.CategoryName, o.ProductID, o.ProductName }
```

我們可以從上述 LINQ 語法上知道有一個 Categories 資料來源，並且 join 另一個 Products 資料來源，當中用這個兩個實體的其中一個 CategoryID 屬性進行 join 動作，這是一個非常明確的 join 語法，其語法與 T-SQL 的 join 語法非常相近，這段 LINQ 語法所產生的 T-SQL 語句如下：

```
SELECT [t0].[CategoryName], [t1].[ProductID], [t1].[ProductName]
FROM [Categories] AS [t0]
INNER JOIN [Products] AS [t1] ON ([t0].[CategoryID]) = [t1].[CategoryID]
```

這兩段 LINQ 語法所執行的結果資料一模一樣，不過產生的 T-SQL 不一樣，所以透過 LINQ to SQL 會自動將設定的 LINQ 語法產生出合適的 T-SQL 語法。不過，我們都知道資料庫查詢的 SQL 語法有很多調校的技巧，透過 LINQ to SQL 之後自然會變的不容易調校，所以針對某些 LINQ 語法還是有可能產生效能不彰的狀況需要自行處理。

舉個例子來說，以下 LINQ 語法除了 join 外還在 select 的地方有隱含的使用了另一個 JOIN 條件：

```
from c in Categories
join o in Products on c.CategoryID equals o.CategoryID 
select new { o.Category.CategoryName ,o.ProductID, o.ProductName }
```

因此會產生出一個詭異且無效率的 T-SQL 語法如下，同時有 INNER JOIN 與 LEFT OUTER JOIN 的狀況：

```
SELECT [t2].[CategoryName], [t0].[ProductID], [t0].[ProductName]
FROM [Products] AS [t0]
INNER JOIN [Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])
LEFT OUTER JOIN [Categories] AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]
```

這時我們修正 LINQ 語法如下：

```
from c in Categories
join o in Products on c.CategoryID equals o.CategoryID 
select new { c.CategoryName, o.ProductID, o.ProductName }
```

這時就會產生**預期的** T-SQL 語法：

```
SELECT [t0].[CategoryName], [t1].[ProductID], [t1].[ProductName]
FROM [Categories] AS [t0]
INNER JOIN [Products] AS [t1] ON ([t0].[CategoryID]) = [t1].[CategoryID]
```

雖然 LINQ 出現的目的就是希望你改用「物件」與「實體」的角度來思考資料的查詢方式，不過如果你能瞭解從 LINQ 轉換到 T-SQL 的過程將有助於你寫出更有效率的 LINQ 查詢語法。

瞭解 INNER JOIN 之後，我們再來看一個 T-SQL 中常見的 LEFT JOIN 語法的 LINQ 要如何撰寫：

```
from o in Products
select new { o.Category.CategoryName, o.ProductID, o.ProductName }
```

這段 LINQ 語法我們只取出一個 Products 資料來源，但是在 select 子句卻額外取得 Category 關連實體的資料，這個關連是「多對一」的關連，所以一個 Product 實體只會取得一個 Category 實體，所以轉換 T-SQL 之後你會發現 LEFT JOIN 的左邊就是 Products，而右邊就變成 Categories，其所產生的 T-SQL 語句如下：

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

雖然你看到的是一個 LEFT JOIN 語法，不過這並不是我們常見的那種 JOIN 狀況，比較複雜的狀況是當我們左側的實體為 Categories 以及右側的實體為 Products 的狀況，這是「一對多」的關連，複雜度相對提高一些。

由於 INNER JOIN 是 **交集**(**AND**) 的概念，當兩個 JOIN 的實體都有資料時才有資料。而 LEFT JOIN 的概念卻是「左邊的資料全部都要有，即便右邊沒有資料也要以 null 代替」，因此我們的 LINQ to SQL 語法如下就要特別注意資料來源出現的順序性：

我們先重寫文章稍早出現的過的 INNER JOIN 語法如下：

```
from c in Categories
join o in Products on c.CategoryID equals o.CategoryID 
select new { c.CategoryName, o.ProductName }
```

如果要改成 LEFT JOIN 語法，則必須修改成以下 LINQ 語法：

```
from c in Categories
join o in Products on c.CategoryID equals o.CategoryID into ps
from o in ps.DefaultIfEmpty()
select new { c.CategoryName, o.ProductName }
```

因為撰在 LINQ 撰寫 LEFT JOIN 時的觀念與 T-SQL 不太一樣，要用「物件」的角度比較能理解其語法所代表的意義，所以上述的語法可以這樣解釋：

> 取出 Categories 資料並放入 c 變數，接著再與 Products 做 JOIN 並將 JOIN 後的結果放入 ps 變數（注意：並非第二行的 o 變數），這時再宣告一個資料來源 o 其資料來自於「修改過的 ps 變數」，且修改的方式為 ps.DefaultIfEmpty()，意即代表若 ps ( 也就是 Products 經過 join 篩選後的資料 ) 無任何資料時，還是回傳一筆 Default 的資料（也就是預設的 null 資料）。

這一段 LINQ 所產生的 T-SQL 如下：

```
SELECT [t0].[CategoryName], [t1].[ProductName] AS [ProductName]
FROM [Categories] AS [t0]
LEFT OUTER JOIN [Products] AS [t1] ON ([t0].[CategoryID]) = [t1].[CategoryID]
```

因此當你需要撰寫「多對一」關連情況下正確的使用 LINQ 查詢語法做 LEFT JOIN 就必須這樣來撰寫！

不過有一點必須注意，那就是當 o 取得的物件為 null 時，雖然 select 子句中的 o.ProductName 不會引發例外，而且會回傳 null，但是若你在 select 子句中設定 o.ProductID 的話，那就會引發例外，因為 null 並無法正確轉型成 Int32 型別，錯誤訊息是：InvalidOperationException - 無法將 Null 值指派給型別 System.Int32 的成員，該型別不可為 Null 值。

**相關連結**

-   [Linq Samples and the Sample Query Explorer](/post/2008/05/05/About-Linq-Samples-and-the-Sample-Query-Explorer.aspx)
-   [LINQ to SQL Samples](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/)
-   [Linq to SQL - Left Join examples (Common mistake and Tricks) - Tiki Wan - MSDN Blogs](https://web.archive.org/web/20101214233346/http://blogs.msdn.com:80/b/tikiwan/archive/2010/07/11/linq-to-sql-left-join-examples-common-mistake-and-tricks.aspx)
-   [HOW TO：編寫聯結和叉積查詢 (LINQ to SQL)](http://msdn.microsoft.com/zh-tw/library/bb399397\(VS.90\).aspx)
-   [SQL Joins with C# LINQ](https://web.archive.org/web/20260607021853/https://www.dotnet-tricks.com/Tutorial/linq/UXPF181012-SQL-Joins-with-C)