在LINQ中實踐多條件LEFT JOIN
6 | 76,453 |
同事今天問了一個LINQ下使用多條件比對產生LEFT JOIN的問題,讓我也學到了新東西,特地PO文備忘。
這回不寫程式,直接用威到不行的LINQPad做示範。
假想的題目是有個員工資料表Employee,Primary Key是DeptId及UserId,除了UserName,另有SubstituteDeptId及SubstituteUserId指向該員工的代理人。公司共有三人,工程部的Jeffrey及Darkthread互為代理人,業務部的Mouth Cannon先生因火力強大,無人能代理,故代理人從缺。我們希望產生一個清單,列出部門、員編、姓名及代理人姓名,由於有人無代理人,故需要用LEFT JOIN處理。
依據MSDN文件的介紹,LINQ要產生LEFT JOIN的關鍵在於先將JOIN結果形成群組,再借重DefaultIfEmpty()方法容忍比對不符時的狀況,一般是傳回屬性值為null的空物件。
在此案例中還有一個特別之處,是必須同時比對DeptId及UserId兩個欄位做JOIN(不要問我為什麼不讓UserId唯一就好? 不這樣假設要怎麼示範多條件?),由於join ... on時只接受單一equals關鍵字作比對,因此當有多條件時,要將多欄位各自組一個匿名類別(如: new { s.DeptId, s.UserId }),再用equals比對匿名類別。但由於是同一資料表自己JOIN自己,JOIN時比對欄位名稱不相同,名因此要重新命名匿名類別的屬性名稱(如: new { DeptId = e.SubstituteDeptId, UserId = e.SubstituteUserId }),否則會產生The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.錯誤!
完整的LINQ語法如下:
from e in Employees
join s in Employees
on new { DeptId = e.SubstituteDeptId,
UserId = e.SubstituteUserId } equals
new { s.DeptId, s.UserId } into subGrp
from s in subGrp.DefaultIfEmpty()
select new
{
e.DeptId, e.UserId, e.UserName,
Substitute = s.UserName ?? "NA"
}
大功告成!
順便附上LINQ語法所產生的T-SQL如下:
-- Region Parameters
DECLARE @p0 NVarChar(2) = 'NA'
-- EndRegion
SELECT [t0].[DeptId], [t0].[UserId], [t0].[UserName],
COALESCE([t1].[UserName],@p0) AS [Substitute]
FROM [Employee] AS [t0]
LEFT OUTER JOIN [Employee] AS [t1]
ON ([t0].[SubstituteDeptId] = [t1].[DeptId])
AND ([t0].[SubstituteUserId] = [t1].[UserId])
Comments
# by Alan
太好了!之前我還以為Left join壞了!只好又用組語法的方式使用 今日得見黑大這篇文章,幫小弟解惑也~ 黑大~請看2010.7月引爆的新書 http://4.bp.blogspot.com/_RpHq_ZXK7QM/S96WkfquuDI/AAAAAAAAAwQ/Vw50zlRKa1k/s1600/Final_VS_2010-DM.jpg 是不是感覺少了什麼呢^_^沒錯!正是少了jQuery + Silverlight 4 黑大靠你了!
# by Jeffrey
多謝Alan兄真情相挺! 這幾年承蒙一些朋友、先進抬愛,聽過不少出書、開講座的勸進聲及邀請,不過短期內應是無力分身做好這些額外的壯舉。 說一件有趣的事,前些時候帶小朋友去看小熊維尼音樂劇,第一次坐在TICC的大會堂台上沒有講師及Live Demo。我感慨地跟女王說: 如果我敢豁出去,不要命地賭一把 ,也許真有機會實現夢想,站在這間會議中心的台上講課哩!(當然,並不保證不會被噓下台或全場睡成一片啦! XD)... 女王默默地握住我的手,凝視著我說: ..... 請你愛惜生命~~~ 不過世事多變,或許還真會有那麼一天,到時再會再通請大家情義相挺。(宣傳海報應該會比照選舉做成黑旗,疾呼"搶救黑暗執行緒!"之類的)
# by Alan
確實是這樣!我第一本ASP.NET VS2005啟蒙書就是看聖殿祭司的書 http://blog.sina.com.tw/dotnet/ 當然書一出之後! 講座邀約不斷,時間也不太夠用了~ 最後也因為生涯規劃而離開原本的工作,blog上都有寫^^ 所以我才想說以黑大幽默風趣的筆法,相信一定可以吸引不少讀者 不然小弟有一個建議,使用投票方式決定,黑大可以試辦一個投票統計!看看支持率多少再考慮一下也不遲!! 加油~
# by 為這問題苦惱的人
太感謝您了
# by winfLin
真不錯, 感謝黑大 可是當我用黑大教的方法, 進行 Group by 以獲得 Count 數時 那個原來沒有配對到的項目, Count 數應為 0 就一律變成 1 了 (例如, 若黑大這題, 改成: 計算出每個員工的代理人數量) 當然這不是 Left Join 的錯 只是想順便問一下大家, 如何解這個需求?
# by winfLin
呵呵, 我自己出的 Count 問題已經解了 只要在 Count() 中, 傳入 t => t !=null 的條件, 即可獲得正確值 謝謝黑大 :)