The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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

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

由於大多數人都是先學會 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 JOINLEFT 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 值。

相關連結