程式碼學習不歸路

多對多其實在一、兩年前就已經遇過，最近又遇到類似的問題，剛好把這部份的資料整理起來跟大家分享

當資料有做正規化的話，有時候就會遇到多對多的處理情況

什麼是多對多呢？

最簡單的例子就是，每一個台灣人都可以擁有多重國藉

所以你會有使用者(Users, key: UserID), 國藉(Nations, Key: NationID)

以及使用者對應國藉(User2Nation, Key: UserID + NationID)這三個資料表

很習慣地我們會把這三個資料表加到EntityFramework designer頁面去

然而你會發現一個奇怪的景象

User2Nation不見了!!!
User2Nation不見了!!!
User2Nation不見了!!!

取而代之的只有Users與Nations這兩個資料表，但這不是Bug

在navigation property中，也可以很清楚發現這是沒有問題的

    public partial class Users
    {
        public Users()
        {
            this.ExpenseDetail = new HashSet<ExpenseDetail>();
            this.Nations = new HashSet<Nations>();
        }
    
        public int userID { get; set; }
        public string userName { get; set; }
        public virtual ICollection<ExpenseDetail> ExpenseDetail { get; set; }
        public virtual ICollection<Nations> Nations { get; set; }
    }

我們馬上就來測試這個方便的功能

                using (StreamWriter sw = new StreamWriter(string.Format(@"{0}\log.txt", logPath)))
                {
                    using (var db = new NorthwindEntities())
                    {
                        db.Database.Log = s => Log(sw, s);
                        //db.Database.Log = Console.Write;

                        var result = db.Users
                            .Where(m => m.Nations.Count > 1)
                            .Select(m => new
                            {
                                Nmae = m.userName,
                                lstNation = m.Nations
                            }).ToList();

                        foreach (var elment in result)
                        {
                            Console.WriteLine("User:" + elment.Nmae);
                            foreach (var nation in elment.lstNation)
                                Console.WriteLine("=====>" + nation.NationName);
                        }
                    }
                }

若對於把sql log寫到folder 或是sql效能有疑問者可以參考前一篇 EntityFramework 效能檢視

效能上因為有加FK，所以沒有太大問題，在使用上又更上一層樓

但請注意天下沒有白吃的午餐，這麼好用的東西當然也會有要付出代價的地方

Create

新增的方式其實很簡單

如果你要針對一個新的User增加新的國藉

只要使用newUser.Nations.Add( new country object ) 即可

EntityFramework就會在User2Nation與Nation這兩個表格做Insert

如果我們用以下的方法Create一個新的User，會發生什麼事呢？

                        Nations tw = new Nations() { NationID = "TW", NationName = "Taiwan" };
                        Nations gr = new Nations() { NationID = "GR", NationName = "Greece" };
                        //insert user and nations
                        Users newUser = new Users() { userID = 99, userName = "new99" };
                        //TW exist
                        newUser.Nations.Add(tw);
                        //GR not exist
                        newUser.Nations.Add(gr);
                        db.SaveChanges();

Insert TW這個國藉的時候就會失敗，為什麼呢？

原因很簡單，因為TW這個國藉在Nations已經存在

在處理多對多的時候，最麻煩的就是我們得知道哪些資料是已經存在，哪些是不存在

因為TW已經存在於Nations，所以我們得改tw的來源

                        Nations tw = db.Nations.Where(m => m.NationID == "TW").FirstOrDefault();
                        Nations gr = new Nations() { NationID = "GR", NationName = "Greece" };
                        //insert user and nations
                        Users newUser = new Users() { userID = 99, userName = "new99" };
                        //TW exist
                        newUser.Nations.Add(tw);
                        //GR not exist
                        newUser.Nations.Add(gr);
                        db.SaveChanges();

這樣子EntityFramework才知道TW是從已經存在的資料抓出來的，它沒必要去Insert這筆資料

以這個例子來看，Users會Insert 一筆，User2Nation會Insert 2筆 (TW, GR)，而Nations 也只會Insert一筆 (GR)

 

Delete

在講Update之前，我們先來看看Delete

Users user99 = db.Users.Where(m => m.userID == 99).FirstOrDefault();
user99.Nations.Clear();
db.SaveChanges();

User2Nation會Delete 2筆 (TW, GR), Users跟Nations不變

Update

之所以會先講Delete再講Update，你可能猜到為什麼了

由於是多對多的關係，User2Nation的欄位通常為兩個FK所組成的Key

在EntityFramework中Key是無法被更改的

Update = Delete + Insert

由於Entity Framework是有包Trasaction的，所以並不會有Delete成功，但Insert failed的情況，要嘛就是全部成功全部失敗

所以Update我們可以這樣做，就會長成如下方這樣的形式 (假設我們要更新10號User的國藉, 再假設TW, GR, JP這三個國家都存在)

                         //update 
                        //delete and then insert back
                        Users user10 = db.Users.Where(m => m.userID == 10).FirstOrDefault();
                        Nations tw = db.Nations.Where(m => m.NationID == "TW").FirstOrDefault();
                        Nations gr = db.Nations.Where(m => m.NationID == "GR").FirstOrDefault();
                        Nations jp = db.Nations.Where(m => m.NationID == "JP").FirstOrDefault();
                        user10.Nations.Clear();
                        user10.Nations.Add(tw);
                        user10.Nations.Add(gr);
                        user10.Nations.Add(jp);
                        db.SaveChanges();

並把SQL log抓出來看看

Opened connection at 2017/4/17 下午 01:26:16 +08:00
SELECT TOP (1) 
    [Extent1].[userID] AS [userID], 
    [Extent1].[userName] AS [userName]
    FROM [dbo].[Users] AS [Extent1]
    WHERE 10 = [Extent1].[userID]
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 13 ms with result: SqlDataReader

Closed connection at 2017/4/17 下午 01:26:16 +08:00
Opened connection at 2017/4/17 下午 01:26:16 +08:00
SELECT TOP (1) 
    [Extent1].[NationID] AS [NationID], 
    [Extent1].[NationName] AS [NationName]
    FROM [dbo].[Nations] AS [Extent1]
    WHERE 'TW' = [Extent1].[NationID]
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 7 ms with result: SqlDataReader

Closed connection at 2017/4/17 下午 01:26:16 +08:00
Opened connection at 2017/4/17 下午 01:26:16 +08:00
SELECT TOP (1) 
    [Extent1].[NationID] AS [NationID], 
    [Extent1].[NationName] AS [NationName]
    FROM [dbo].[Nations] AS [Extent1]
    WHERE 'GR' = [Extent1].[NationID]
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 33 ms with result: SqlDataReader

Closed connection at 2017/4/17 下午 01:26:16 +08:00
Opened connection at 2017/4/17 下午 01:26:16 +08:00
SELECT TOP (1) 
    [Extent1].[NationID] AS [NationID], 
    [Extent1].[NationName] AS [NationName]
    FROM [dbo].[Nations] AS [Extent1]
    WHERE 'JP' = [Extent1].[NationID]
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 32 ms with result: SqlDataReader

Closed connection at 2017/4/17 下午 01:26:16 +08:00
Opened connection at 2017/4/17 下午 01:26:16 +08:00
SELECT 
    [Extent2].[NationID] AS [NationID], 
    [Extent2].[NationName] AS [NationName]
    FROM  [dbo].[User2Nation] AS [Extent1]
    INNER JOIN [dbo].[Nations] AS [Extent2] ON [Extent1].[NationID] = [Extent2].[NationID]
    WHERE [Extent1].[UserID] = @EntityKeyValue1
-- EntityKeyValue1: '10' (Type = Int32, IsNullable = false)
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 23 ms with result: SqlDataReader

Closed connection at 2017/4/17 下午 01:26:16 +08:00
Opened connection at 2017/4/17 下午 01:26:16 +08:00
Started transaction at 2017/4/17 下午 01:26:16 +08:00
INSERT [dbo].[User2Nation]([UserID], [NationID])
VALUES (@0, @1)
-- @0: '10' (Type = Int32)
-- @1: 'GR' (Type = AnsiString, Size = 10)
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 23 ms with result: 1

INSERT [dbo].[User2Nation]([UserID], [NationID])
VALUES (@0, @1)
-- @0: '10' (Type = Int32)
-- @1: 'JP' (Type = AnsiString, Size = 10)
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 13 ms with result: 1

INSERT [dbo].[User2Nation]([UserID], [NationID])
VALUES (@0, @1)
-- @0: '10' (Type = Int32)
-- @1: 'TW' (Type = AnsiString, Size = 10)
-- Executing at 2017/4/17 下午 01:26:16 +08:00
-- Completed in 12 ms with result: 1

Committed transaction at 2017/4/17 下午 01:26:17 +08:00
Closed connection at 2017/4/17 下午 01:26:17 +08:00

在這邊我們可以發現，如果User2Nation筆數夠大的話，效率並不會太快

因為Entity Framework是一筆一筆塞的，並不是採用Bulk Insert的方式來做

但若是筆數不多的情況下，用這樣的方式處理並不會有太大的效能問題

 

參考資料

https://www.codeproject.com/tips/893609/crud-many-to-many-entity-framework