LINQ學習筆記(6) Join — 多表單多條件式

建立三個資料表，如下

//類SQL語法
var x = from o in SLIST
from p in ScoreList
from j in TLIST
where o.ID == p.StudentID && j.Classroom==o.Classroom &&j.Class==p.Class
orderby o.Classroom, o.ID, p.Class, p.score descending
select new { o.ID, o.Classroom, o.Name, p.Class, p.score,j.name };

跟T-SQL沒有太大的分別，清楚明瞭又好寫

//類SQL 語法 2
var xxx = from o in SLIST
join p in ScoreList
on o.ID equals p.StudentID
join j in TLIST
on new {o.Classroom,p.Class } equals new { j.Classroom,j.Class} //* equals 是物件的比對，要把多欄位值組成一個新物件來比對
orderby o.Classroom, o.ID, p.Class, p.score descending
select new { o.ID, o.Classroom, o.Name, p.Class, p.score,j.name };

除了equals 的地方需注意一下，其餘跟方法一的差異不大

//lambda語法
var y = SLIST.Join(ScoreList, o => o.ID, p => p.StudentID,
(c, s) => new { c.ID, c.Classroom, c.Name, s.Class, s.score })
//將join完的新集合再做一次join
.Join(TLIST,o=>new {o.Class,o.Classroom },p=>new {p.Class,p.Classroom },//組成新物件做比對
(o,p)=>new { o.ID, o.Classroom, o.Name, o.Class, o.score, p.name })
.OrderByDescending(o=> o.score).OrderBy(o => o.Class).OrderBy(o => o.ID).OrderBy(o=>o.Classroom);

lambda語法的優點是物件的變化順序清楚，但稍嫌繁瑣

var y = SLIST.Join(ScoreList, o => o.ID, p => p.StudentID, (c, s) =>
new { c.ID, c.Classroom, c.Name, s.Class, s.score })
.Join(TLIST, o => new { o.Class, o.Classroom }, p => new { p.Class, p.Classroom }, (o, p) => new { o, p })//直接將物件輸出
.OrderBy(o => o.o.Classroom);//多了一層的物件

三者得出的結果相同

需注意如果在最後的結果輸出時，不用列舉的方式，會變成階層式的物件，再LINQ 轉T-SQL對資料庫查詢時效能會很差

var y = SLIST.Join(ScoreList, o => o.ID, p => p.StudentID, (c, s) =>
new { c.ID, c.Classroom, c.Name, s.Class, s.score })
.Join(TLIST, o => new { o.Class, o.Classroom }, p => new { p.Class, p.Classroom }, (o, p) => new { o, p })//直接將物件輸出
.OrderBy(o => o.o.Classroom);//多了一層的物件

產出的結果如下圖