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

莊創偉
4 min readNov 13, 2017

建立三個資料表,如下

//類SQL語法var x = from o in SLISTfrom p in ScoreListfrom j in TLISTwhere o.ID == p.StudentID && j.Classroom==o.Classroom &&j.Class==p.Classorderby o.Classroom, o.ID, p.Class, p.score descendingselect new { o.ID, o.Classroom, o.Name, p.Class, p.score,j.name };

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

//類SQL 語法 2var xxx = from o in SLISTjoin p in ScoreListon o.ID equals p.StudentIDjoin j in TLISTon new {o.Classroom,p.Class } equals new { j.Classroom,j.Class} //* equals 是物件的比對,要把多欄位值組成一個新物件來比對orderby o.Classroom, o.ID, p.Class, p.score descendingselect 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);//多了一層的物件

產出的結果如下圖

--

--

莊創偉

學海無涯。但是為了生計還是得下海的風塵男子