2012年1月12日 星期四

[C#] LINQ : JOIN Tables

這篇文章主要是討論如何將ADO.NET的JOIN多個表格的Sql，轉換成用LINQ的寫法。

舉例來說，一間小學的資料庫有以下的資料表

TB_STUDENT (學生)
NO(學號)
NAME(姓名)
AGE(年齡)
0001
小明
12
0002
美美
10

TB_SUBJECT (科目)
SN(代號)
SUBJECT(科目)
CH
國文
EN
英文
MA
數學
AT
美術

TB_GRADE_LIST (成績列表)
SN
NO
GRADE
CH
0001
90
EN
0001
50
MA
0001
100
CH
0002
55
EN
0002
95
MA
0002
90


1   這邊以ASP.NET 應用程式(C#)為例，Default.aspx頁面有一個DropDownList，可選擇學生的姓名；選擇完畢後，會在下面的GridView將該學生的成績顯示出來。

2           
2.1   建立專案後，在專案新加入一個『LINQ to SQL 類別』。
名稱取為：Class.dbml




2.2   加入你的Sql Server資料連結，打開Class.dbml，然後將三個表格(記得都要加入Primary Key!)拖曳進去。 請參考下圖。 然後編譯! 這樣DataContext才會產生~




2.3  接下來，我們選擇Default.aspx的DropDownList，加入資料來源…

Step1. 選擇資料來源


Step2.選擇新資料來源


Step3. 選擇LINQ DataSource


Step4. 選擇剛才建立的DataContext


Step5. 選擇TB_STUDENT資料表，然後選擇NAME (學生姓名) 為Select欄位。
按下「完成」，DropDownList便完成了~


3    我們先把DropDownList的AutoPostBack功能打開，然後開啟它的「SelectedIndexChanged」事件。
輸入以下程式碼： (LINQ Sql 在 4. 討論)


     String sel_Name = DropDownList1.SelectedItem.ToString();
ClassDataContext iCE = new ClassDataContext();
//INNER JOIN
var data = ….; → Linq (1)
//LEFT OUTER JOIN (以學生為母體)
var data = ….; → Linq (2)
//LEFT OUTER JOIN (以科目為母體)
var data = ….; → Linq (3)

DataTable dt_data = new DataTable();
DataColumn [] dc_data = new DataColumn[3];
dc_data[0] = new DataColumn("姓名");
dc_data[1] = new DataColumn("科目");
dc_data[2] = new DataColumn("成績");
dt_data.Columns.AddRange(dc_data);

foreach (var item in data)
{
    DataRow dr_data = dt_data.NewRow();
    dr_data["姓名"] = item.NAME;
    dr_data["科目"] = item.SUBJECT;
    dr_data["成績"] = item.GRADE;
    dt_data.Rows.Add(dr_data);
    dr_data = null;
}
GridView1.DataSource = dt_data;
GridView1.DataBind();
dt_data = null;



4   LINQ Sql討論

4.1  (1) INNER JOIN
var data =
                               from a in iCE.TB_STUDENT.AsEnumerable()
                               join b in iCE.TB_GRADE_LIST.AsEnumerable()
                               on  a.NO equals b.NO
                               join c in iCE.TB_SUBJECT.AsEnumerable()
                               on b.SN equals c.SN
                               where a.NAME == sel_Name
                               select new
                                {
                                    NAME =
                                        a.NAME,
                                    SUBJECT =
                                        c.SUBJECT,
                                    GRADE =
                                        b.GRADE
                                };


        

              說明：
              from x in 母體
               join y in 子表格1 on x.欄位 equals y.欄位
               join z in 子表格2 on x.欄位 equals z.欄位
               where 條件
               select new { 新欄位1, 新欄位2, … }
 

4.2  (2) LEFT OUTER JOIN (以學生為母體)
var data =
                    from x in
                    (
                               from a in iCE.TB_STUDENT
                               join b in iCE.TB_GRADE_LIST
                               on a.NO equals b.NO into group_GRADE
                               from item_GRADE in group_GRADE.DefaultIfEmpty()
                               where a.NAME == sel_Name
                               select new
                               {
                                   NAME = a.NAME,
                                   SN = item_GRADE.SN,
                                   GRADE = item_GRADE.GRADE
                               }
                    )
                    join c in iCE.TB_SUBJECT
                    on x.SN equals c.SN into group_SUBJECT
                    from item_SUBJECT in group_SUBJECT.DefaultIfEmpty()
                    select new
                    {
                         NAME = x.NAME,
                         SUBJECT = item_SUBJECT.SUBJECT,
                         GRADE = x.GRADE
                    };


             


說明：

我們先用 TB_STUDENT 去 Left outer join TB_GRADE_LIST 取得每位學生的成績，再去Left outer join TB_SUBJECT 取得科目的中文名稱。
所以第一次 Left outer join 的 LINQ 如下 (紅色部分為Left outer join的方法，藍色字為自訂)

from a in iCE.TB_STUDENT
join b in iCE.TB_GRADE_LIST
on a.NO equals b.NO into group_GRADE
from item_GRADE in group_GRADE.DefaultIfEmpty()
where a.NAME == sel_Name
         select new
         {
             NAME = a.NAME,
             SN = item_GRADE.SN,
             GRADE = item_GRADE.GRADE
         }

接下來把上面的結果當作母體，作第二次Left outer join…

from x in
 (
   //上面第一次Left outerj oin的LINQ
)
       join c in iCE.TB_SUBJECT
       on x.SN equals c.SN into group_SUBJECT
       from item_SUBJECT in group_SUBJECT.DefaultIfEmpty()
       select new
       {
           NAME = x.NAME,
           SUBJECT = item_SUBJECT.SUBJECT,
           GRADE = x.GRADE
       }

    這邊必須注意的地方是紅字的關鍵字，以及後面select新欄位時，要用Linq裡面暫存的group_XXX 的 item_XXX。
    附上實際上執行的TSQL：
   
-- Region Parameters
DECLARE @p0 VarChar(10) = N'美美'
-- EndRegion
SELECT [t2].[NAME], [t3].[SUBJECT] AS [SUBJECT], [t2].[value2] AS [GRADE]
FROM (
    SELECT [t0].[NAME], [t1].[SN] AS [value], [t1].[GRADE] AS [value2]
    FROM [TB_STUDENT] AS [t0]
    LEFT OUTER JOIN [TB_GRADE_LIST] AS [t1] ON [t0].[NO] = [t1].[NO]
    ) AS [t2]
LEFT OUTER JOIN [TB_SUBJECT] AS [t3] ON [t2].[value] = [t3].[SN]
WHERE [t2].[NAME] = @p0




4.3  (3) LEFT OUTER JOIN (以科目為母體)
var data =
                    from x in
                    (
                        from a in iCE.TB_SUBJECT
                        join b in iCE.TB_GRADE_LIST
                        on a.SN equals b.SN into group_GRADE
                        from item_GRADE in group_GRADE.DefaultIfEmpty()
                        select new
                        {
                            SUBJECT = a.SUBJECT,
                            NO = item_GRADE.NO,
                            GRADE = item_GRADE.GRADE
                        }
                    )

                    join c in iCE.TB_STUDENT
                    on x.NO equals c.NO into group_STUDENT
                    from item_STUDENT in group_STUDENT.DefaultIfEmpty()
                    where item_STUDENT.NAME == sel_Name || Nullable.Equals(item_STUDENT.NAME, null)
                    orderby x.GRADE descending
                    select new
                    {
                        NAME = item_STUDENT.NAME,
                        SUBJECT = x.SUBJECT,
                        GRADE = x.GRADE
                    };

          



說明：

這個Linq跟(2) 是很像的，只是母體換了，然後WHERE條件放到最後才判斷。
最後一個不同點，是將沒有任何學生分數的美術科目，也列印到結果。

所以我們的WHERE條件變成
where item_STUDENT.NAME == sel_Name || Nullable.Equals(item_STUDENT.NAME, null)
是的~~ 如果我們在Linq要判斷欄位值是否為空值，不能打 XXX is null 或 XXX == null 或 XXX==””或 …
而是要使用 Nullable.Equals( XXX, null) !


張貼者： 於

2 則留言:

  1. karatejb2012年1月13日 中午12:04

    在LINQ中實踐多條件LEFT JOIN
    DarkThread大大的文章
    http://blog.darkthread.net/post-2010-08-12-linq-left-join.aspx

    回覆刪除
  2. karatejb2012年7月13日 下午6:42

    Linq join on ...的條件如果有多個似乎不能寫成
    a.欄位X == b.欄位X && a.欄位Y == b.欄位Y

    而必須要用物件去做比較
    from a in XXX
    join b in YYY
    on
    new { a.欄位X, a.欄位Y }
    equals
    new { b.欄位X, b.欄位Y }

    回覆刪除

訂閱： 張貼留言 (Atom)