logo头像

Edward.K Thinking

使用Linq的Join / right Join / Left Join

用Join來做資料聯集,在T-SQL指令是常會用到的一種語法,不過,對於常寫SQL語法的人要轉換到Linq語法時,往往對於Join這語句的操作就常會感到比較陌生也不習慣要怎樣使用。因此,這邊簡單說明一下,要如何操作Linq的join語法

資料表A
資料表B

Join


先來做兩個資料的Join,把資料表B去顯示對應的資料表A的WeekName,Join是算是Linq比較簡單易懂的,其條件為透過資料表A的Week與資料表B的WeekSQL這兩個欄位相等之關係之資料集

1
2
3
4
5
from ss in Config_WeekMappings
join aa in S_Student_Curriculums
on new { WeekID=ss.Week} equals
new {WeekID=aa.WeekSQL}
select new { WeekSQL=ss.Week,WeekName=ss.WeekName,week=aa.Week}

跟我們熟悉的SQL語法是相同的

1
2
3
SELECT [t0].[Week] AS [WeekSQL], [t0].[WeekName]
FROM [Config_WeekMapping] AS [t0]
INNER JOIN [S_Student_Curriculum] AS [t1] ON [t0].[Week] = [t1].[WeekSQL]

若是要這Joine過程中去過濾一些資料呢?例如我只要取資料表A中的Week等於1的資料,其語法如下:

1
2
3
4
5
6
from ss in Config_WeekMappings
join aa in S_Student_Curriculums
on new { WeekID=ss.Week} equals
new {WeekID=aa.WeekSQL}
where ss.Week==1
select new { WeekSQL=ss.Week,WeekName=ss.WeekName}

而除了一個過濾條件外,我們又想在資料表B中取出某一個學號且WeekSQL為1的資料,其語法如下:

1
2
3
4
5
6
from ss in Config_WeekMappings
join aa in S_Student_Curriculums
on new { WeekID=ss.Week} equals
new {WeekID=aa.WeekSQL}
where aa.S_Num=="S20142001" && ss.Week==1
select new { WeekSQL=ss.Week,WeekName=ss.WeekName}

這樣就可以採用兩個條件做篩選,且可以分別篩選出不同資料表的條件。因此,即使在Linq中也可以很輕鬆的透過這樣語法做過濾條件,Linq轉成SQL語句是跟我們再用SQL指令的方式是相同的。

Right Join


使用Right Join在Linq中並無真正去區分Right Join或是Left Join的關鍵字。這一點跟SQL指令不太一樣,在SQL中還可以採用Right Join & Left Join去指定資料表的聯集方式。但是,在Linq這邊語法就沒有這樣直覺,Linq語法中都只有Join這指令而已,因此,先來講Right Join作法,後面再接著說Left Join,個人認為right Join比較需要換個方向思考才能與SQL相同

想要把資料表A去Right Join資料表B,其SQL語法可以這樣下:

1
2
3
4
5
6
7
8
SELECT  [t0].[Week] AS [WeekSQL] ,
[t0].[WeekName] ,
t1.Week
FROM [dbo].[Config_WeekMapping] [t0]
RIGHT JOIN ( SELECT WeekSQL ,
Week
FROM [dbo].[S_Student_Curriculum]
) t1 ON t0.Week = t1.WeekSQL

在Linq並無Right Join語法,因此,我做法則是把資料表兩個做對調,其意義為用資料表B去Left Join資料表A,這樣就可以達到資料表A做Right Join資料表B的功能,其與法如下:

1
2
3
4
5
6
from ss in S_Student_Curriculums
join aa in Config_WeekMappings
on new { WeekID=ss.WeekSQL} equals
new {WeekID=aa.Week} into temp
from ds in temp.DefaultIfEmpty()
select new { WeekSQL=ds.Week,WeekName=ds.WeekName,week=ss.Week}

而當我們在Linq語法中,無論是right join或是left Join中,要取得第一個資料表S_Student_Curriculums的欄位,直接使用上述的變數ss指向欄位即可,但是,若是要取得第二個資料表欄位Config_WeekMappings,則不是直接取用變數aa對應欄位,而是必須使用temp這個變數去對映相關欄位才可以,因此,上面語法對照的SQL語法如下:

1
2
3
SELECT [t1].[Week] AS [WeekSQL], [t1].[WeekName] AS [WeekName], [t0].[Week] AS [week]
FROM [S_Student_Curriculum] AS [t0]
LEFT OUTER JOIN [Config_WeekMapping] AS [t1] ON [t0].[WeekSQL] = [t1].[Week]

若是這邊要做一些資料篩檢的話,就跟Join方式的是相同的概念,下面篩選案例與Join中的案例相同,其語句如下:

1
2
3
4
5
6
7
from ss in S_Student_Curriculums
join aa in Config_WeekMappings
on new { WeekID=ss.WeekSQL} equals
new {WeekID=aa.Week} into temp
from ds in temp.DefaultIfEmpty()
where ss.S_Num=="S20142001" && ds.Week==3
select new { WeekSQL=ds.Week,WeekName=ds.WeekName,week=ss.Week}

Left Join


資料表A做left Join資料表B其語法如下,這部份跟right join是相同的,就把剛剛資料表B再與資料表A做對調即可

1
2
3
4
5
6
from ss in Config_WeekMappings 
join aa in S_Student_Curriculums
on new { WeekID=ss.Week} equals
new {WeekID=aa.WeekSQL} into temp
from ds in temp.DefaultIfEmpty()
select new { WeekSQL=ss.Week,WeekName=ss.WeekName,week=ds.Week}

當然這邊SQL語法也是跟right join是相同的,只有資料表對調而已

以上就是針對Linq做Join系列的簡單作法,不過,上述案例只有針對一個條件做Join,也就是on A.XX=B.XX,若是要針對兩個欄位進行Join呢?只要針對下圖的紅框部分,依序加入欄位就可以。

上一篇