建立三個資料表,如下
//類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);//多了一層的物件
產出的結果如下圖