mrkt 的程式學習筆記: EF Code First - 多對多關係 - 取得擁有指定系統角色的所有系統使用者

這一篇所使用的範例程式將會延續之前文章所使用的程式內容：

ASP.NET MVC 使用 Entity Framework Code First - 基礎入門
 ASP.NET MVC 使用 Entity Framework Code First - 變更多對多關聯資料
 ASP.NET MVC 實做具有多個角色權限的登入功能

另外文章裡將會使用到「devart LINQ Insight」這個工具，之前也曾經有寫文章介紹過：

LINQ 工具 - devart LINQ Insight

對於一直習慣操作資料庫 T-SQL 語法來存取資料的開發人員來說，因為在開發系統時，對於程式裡需要什麼樣的資料都會使用 T-SQL 語法組合出查詢指令碼然後放到程式裡，最後就是直接使用 ADO.NET 去取得想要的資料，但是對於要轉用 EF 去使用 LINQ 下查詢，在各個物件關聯裡去取得想要的資料，很多人就會有相當大的觀念衝突出現，尤其是這種多對多的資料查詢與取得，就會讓許多人卡很久。

在這篇文章作簡單的說明，如何在多對多關係裡去取得擁有指定系統角色的所有系統使用者資料。

雖然一開始就有跟大家說明這篇文章會延續使用之前文章的範例程式，但是就我觀察很多看過我文章的朋友，大多數都不會去看，所以就先來個簡單的前情提要。

之前文章是以 ADO.NET Entity Framework 的 Code First 建立 ASP.NET MVC 的資料模型，而這個資料模型裡有兩個主要的類別，一個是「SystemRole（系統角色）」另一個是「SystemUser（系統使用者）」，兩者是多對多的關係，也就是一個系統角色會有多個系統使用者，而一個系統使用者可以擁有多個系統角色，所以在使用 Code First 建立模型的時候就會為多對多關係的兩個物件再去建立一個存放多對多關係的 Table，如下所示：

EF 資料模型的物件檢視

這個範例專案所使用的是 LocalDB，資料庫的 Table 為使用 EF Code First 所建立的，其中 SystemUserSystemRoles 這個 Table 並沒有出現在上面的 EF 物件檢視裡，甚至於我們也沒有在 Models 資料夾中發現到任何有關這個 Table 所對應的類別，這在前面就有說過，使用 EF Code First 建立物件時，如果兩個物件有多對多關係的時候，就會在資料庫裡建立存放多對多關係資料的 Table，而並不會另外去建立對應的類別，

在網站的部分，也分別有管理 SystemRole 與 SystemUser 資料的功能操作介面，

其實在之前已經建立好的功能裡就已經有一個類似的功能，就是取得多對多關係的關聯資料，在編輯 SystemUser 的功能裡，可以指定 SystemUser 可以被指派哪些系統角色，而如果已經有被指派的系統角色，在進入編輯頁面裡就會被選取，如下：

不過這邊的資料是先取出所有的系統角色資料之後，然後再逐一比對該系統使用者是否有某一筆系統角色資料，

如果我們現在想要在 SystemRole 的 Details 裡去顯示擁有該 SystemRole 的系統使用者名稱的話，應該怎麼做呢？

以 T-SQL 來處理的話，就會是使用以下的方式：這是去找出有被指派 SystemAdmin 角色的系統使用者名稱

這是去找出有被指派 Normal 角色的系統使用者名稱

使用 T-SQL 就可以馬上寫出查詢指令碼，但如果要改成在程式裡使用 LINQ 去操作資料模型裡的物件，應該有很多開發者就會開始遇到問題，首先第一個會想的就是「我怎麼將這一段 T-SQL 轉換成 LINQ 呢？」又或者是「要怎麼在 LINQ 裡面去使用 LEFT JOIN 呢？」

取得擁有指定系統角色的所有系統使用者

操作 LINQ 時，如果物件有關聯的話，那麼在寫程式的時候在使用到「. (點)」，Visual Studio 的 Intellisense 就會帶出與物件有關聯的方法、屬性、欄位以及關聯的物件，

這是因為我們在一開始建立 SystemUser 類別時就已經定義好與 SystemRole 的關聯，而在 SystemRole 類別裡也同樣有定義好與 SystemUser 的關聯，因為雙方都有定義了互相的關聯，所以才會有多對多的關係，

所以當我們要找出某一個系統角色被指派給那些系統角色的時候就可以用關聯的方式，如下：

一開始就指定把有關聯的 SystemUsers 給包含在查詢結果，當有找到指定 ID 的 SystemRole 時也會帶出關聯的 SystemUser 資料（其中第一段的 LINQ 程式寫法為 Method Syntax），

執行結果

如果對於方法語法（Method Syntax）的寫法看不習慣的話，可以改用查詢語法（Query Syntax）的方式來執行，寫法上會與 SQL 的查詢語法稍微相近，這時候可以用 LINQ Insight 在 Visual Studio 裡做執行並且查看執行結果以及觀察產生的 T-SQL 指令碼。

執行結果

SQL 指令碼

SELECT     [Project1].[Sort] AS [Sort],     [Project1].[ID] AS [ID],     [Project1].[Name] AS [Name],     [Project1].[IsEnable] AS [IsEnable],     [Project1].[CreateBy] AS [CreateBy],     [Project1].[CreateOn] AS [CreateOn],     [Project1].[UpdateBy] AS [UpdateBy],     [Project1].[UpdateOn] AS [UpdateOn],     [Project1].[C1] AS [C1],     [Project1].[ID1] AS [ID1],     [Project1].[Account] AS [Account],     [Project1].[Password] AS [Password],     [Project1].[Name1] AS [Name1],     [Project1].[Email] AS [Email],     [Project1].[IsEnable1] AS [IsEnable1],     [Project1].[CreateBy1] AS [CreateBy1],     [Project1].[CreateOn1] AS [CreateOn1],     [Project1].[UpdateBy1] AS [UpdateBy1],     [Project1].[UpdateOn1] AS [UpdateOn1]    FROM ( SELECT         [Extent1].[ID] AS [ID],         [Extent1].[Name] AS [Name],         [Extent1].[Sort] AS [Sort],         [Extent1].[IsEnable] AS [IsEnable],         [Extent1].[CreateBy] AS [CreateBy],         [Extent1].[CreateOn] AS [CreateOn],         [Extent1].[UpdateBy] AS [UpdateBy],         [Extent1].[UpdateOn] AS [UpdateOn],         [Join1].[ID] AS [ID1],         [Join1].[Account] AS [Account],         [Join1].[Password] AS [Password],         [Join1].[Name] AS [Name1],         [Join1].[Email] AS [Email],         [Join1].[IsEnable] AS [IsEnable1],         [Join1].[CreateBy] AS [CreateBy1],         [Join1].[CreateOn] AS [CreateOn1],         [Join1].[UpdateBy] AS [UpdateBy1],         [Join1].[UpdateOn] AS [UpdateOn1],         CASE WHEN ([Join1].[SystemUser_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]        FROM  [dbo].[SystemRoles] AS [Extent1]        LEFT OUTER JOIN  (SELECT [Extent2].[SystemUser_ID] AS [SystemUser_ID], [Extent2].[SystemRole_ID] AS [SystemRole_ID], [Extent3].[ID] AS [ID], [Extent3].[Account] AS [Account], [Extent3].[Password] AS [Password], [Extent3].[Name] AS [Name], [Extent3].[Email] AS [Email], [Extent3].[IsEnable] AS [IsEnable], [Extent3].[CreateBy] AS [CreateBy], [Extent3].[CreateOn] AS [CreateOn], [Extent3].[UpdateBy] AS [UpdateBy], [Extent3].[UpdateOn] AS [UpdateOn]            FROM  [dbo].[SystemUserSystemRoles] AS [Extent2]            INNER JOIN [dbo].[SystemUsers] AS [Extent3] ON [Extent3].[ID] = [Extent2].[SystemUser_ID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[SystemRole_ID]        WHERE cast('3a7e3ddf-898c-40d7-87d4-74bc50fe4f36' as uniqueidentifier) = [Extent1].[ID]    )  AS [Project1]    ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

又或者是在第一次取得 SystemRole 資料的時候不包含關聯的 SystemUser 資料，而是在稍後再下一次查詢，針對 SystemUser 資料去找出關聯的 SystemRole 資料是否為指定的系統角色，

在 LINQ Insight 裡改用 Query Syntax 做觀察，

查詢結果

SQL 指令碼

SELECT     [Extent1].[ID] AS [ID],     [Extent1].[Account] AS [Account],     [Extent1].[Password] AS [Password],     [Extent1].[Name] AS [Name],     [Extent1].[Email] AS [Email],     [Extent1].[IsEnable] AS [IsEnable],     [Extent1].[CreateBy] AS [CreateBy],     [Extent1].[CreateOn] AS [CreateOn],     [Extent1].[UpdateBy] AS [UpdateBy],     [Extent1].[UpdateOn] AS [UpdateOn]    FROM [dbo].[SystemUsers] AS [Extent1]    WHERE  EXISTS (SELECT         1 AS [C1]        FROM [dbo].[SystemUserSystemRoles] AS [Extent2]        WHERE ([Extent1].[ID] = [Extent2].[SystemUser_ID]) AND (cast('3a7e3ddf-898c-40d7-87d4-74bc50fe4f36' as uniqueidentifier) = [Extent2].[SystemRole_ID])    )

有很多人會把 LINQ 與 T-SQL 的觀念給攪和在一起，因為同樣都是對資料進行操作，而 LINQ 是透過 Provider 的處理後才可以產生出相對應的 SQL Script，不管是 T-SQL 或是 PL/SQL，只要有使用支援資料庫對應的 Provider 就可以。

我並不會把 LINQ 與 SQL 給攪和在一起，這邊所謂的「攪和」就是指「SQL 這樣寫而應該怎麼改用 LINQ 表達出來」或「LINQ 怎麼寫才能轉出我所想的那個 SQL Script」這樣的想法，我一直強調的是 LINQ 不等於 SQL Script，LINQ 也不是取代 SQL Script，LINQ 也不是只單純去做為轉換出 SQL Script 的工具或功能。

T-SQL 的操作是去處理資料，面對的是一堆的 Table 與欄位，然後運用各種語法來存取物件，而 LINQ 呢？也同樣是處理資料，但是所面對的是一堆的物件與關聯，常常我們使用 SQL 去存取資料時都是在想，怎麼串語法然後找出我們所需要的資料，而 LINQ 的操作則是使用與 SQL 相似的語法在物件與物件關聯之間去找出資料，以往在沒有使用 LINQ 的時候，在一堆物件集合裡要怎麼找出符合某些條件的資料呢？多半都是迴圈來迴圈去（for 迴圈或 foreach 迴圈），而有了 LINQ 操作之後，用類似 SQL 查詢語法的方式去找出物件裡符合條件的資料，讓所謂的物件操作更加的容易與直覺。

在沒有資料庫的情況下一樣可以用 LINQ 嗎？
當然可以，前面說過，LINQ 是用類似 SQL 查詢語法的操作方式去對物件資料來做操作，如果開發人員過往只對 DataSet, DataTable 打交道，而對物件導向的操作比較陌生時，就會容易在 LINQ 與 SQL 之間的使用觀念上攪和。

總之就是觀念與使用情境上的問題，不要老想著怎麼讓 LINQ 去實現 T-SQL 的查詢語法，在程式裡使用 LINQ 就要以物件導向的方式去思考，不然永遠是跳不出思維的。

最後補充一下，LINQ 同樣也是有 JOIN 的操作方法，但同樣的不要很直覺的認為 LINQ 的 JOIN 能完全去轉換為 T-SQL 的 JOIN，還是那句話，一個是對物件做操作，一個是對存於 Table 的資料去做操作。