NHibernate 多對多對映的資料更新

最近在用 NHibernate 做多對多更新時突然發現 NHibernate 更新的策略很差， 對多對多關係的更新居然是先全部刪除再插入全部資料， 感覺非常奇怪， 現在還原如下：

原來的實體類關係如下：

public class User {
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
    public User() {
        Roles = new HashSet<Role>();
    }
}

public class Role {
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
    public Role() {
        Users = new HashSet<User>();
    }
}

即一個使用者可以有多個角色， 一個角色也可以有多個人， 典型的多對多關係， 對應的對映程式碼如下：

public class UserMapping : ClassMapping<User> {
    public UserMapping() {
        Table("[User]");
        Id(m => m.Id, map => {
            map.Column("[Id]");
            map.Type(NHibernateUtil.Int32);
            map.Generator(Generators.Identity);
        });
        Property(m => m.Name, map => {
            map.Column("[Name]");
            map.Type(NHibernateUtil.String);
        });
        Bag(
            m => m.Roles,
            map => {
                map.Table("[User_Role]");
                map.Key(k => { k.Column("[UserId]"); });
            },
            rel => {
                rel.ManyToMany(map => {
                    map.Class(typeof(Role));
                    map.Column("[RoleId]");
                });
            }
        );
    }
}

public class RoleMapping : ClassMapping<Role> {
    public RoleMapping() {
        Table("[Role]");
        Id(m => m.Id, map => {
            map.Column("[Id]");
            map.Type(NHibernateUtil.Int32);
            map.Generator(Generators.Identity);
        });
        Property(m => m.Name, map => {
            map.Column("[Name]");
            map.Type(NHibernateUtil.String);
        });
        Bag(
            m => m.Users,
            map => {
                map.Table("[User_Role]");
                map.Key(k => { k.Column("[RoleId]"); });
                map.Inverse(true);
            },
            rel => {
                rel.ManyToMany(map => {
                    map.Class(typeof(User));
                    map.Column("[UserId]");
                });
            }
        );
    }
}

資料庫關係圖如下：

當向使用者新增或刪除角色是， 發現更新的效率特別低， 程式碼如下：

using (var session = sessionFactory.OpenSession()) {
    var user = session.Query<User>().First();

    var firstRole = user.Roles.First();
    user.Roles.Remove(firstRole);
    session.Update(user);

    var roleCount = session.Query<Role>().Count();
    var role = new Role { Name = "Role " + (roleCount + 1) };
    session.Save(role);

    user.Roles.Add(role);
    session.Update(user);

    session.Update(user);
    session.Flush();
}

上面的程式碼是將使用者的第一個角色刪除， 再新增一個新的角色， NHibernate 生成的 SQL 語句如下（僅包含對關係表 User_Role 的操作）：

DELETE FROM [User_Role] WHERE [UserId] = @p0;@p0 = 1 [Type: Int32 (0)]
INSERT INTO [User_Role]  ([UserId], [RoleId]) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 2 [Type: Int32 (0)]
INSERT INTO [User_Role]  ([UserId], [RoleId]) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 7 [Type: Int32 (0)]
INSERT INTO [User_Role]  ([UserId], [RoleId]) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 6 [Type: Int32 (0)]
INSERT INTO [User_Role]  ([UserId], [RoleId]) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 10 [Type: Int32 (0)]

居然是先將屬於該使用者的全部角色刪除， 再新增一份新的進來， 完全無法接受， 反過來思考覺得肯定是自己的問題， 經過一番搜尋 (Google)， 發現 StackOverflow 上也有人問類似的問題， 並且最終在 NHibernate Tip: Use set for many-to-many associations 發現瞭解決方案， 將多對多的對映的 bag 改為用 set ， 問題終於得到了解決， 改過後的對映如下：

Set(
    m => m.Roles,
    map => {
        map.Table("[User_Role]");
        map.Key(k => { k.Column("[UserId]"); });
    },
    rel => {
        rel.ManyToMany(map => {
            map.Class(typeof(Role));
            map.Column("[RoleId]");
        });
    }
);

將 UserMapping 和 RoleMapping 中多對多對映全部改為 Set 之後， 上面的測試程式碼生成的 SQL 如下：

DELETE FROM [User_Role] WHERE [UserId] = @p0 AND [RoleId] = @p1;@p0 = 1 [Type: Int32 (0)], @p1 = 8 [Type: Int32 (0)]
INSERT INTO [User_Role]  ([UserId], [RoleId]) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 9 [Type: Int32 (0)]

在 NHibernate 參考文件的 19.5. Understanding Collection performance 中這樣描述：

Bags are the worst case. Since a bag permits duplicate element values and has no index column, no primary key may be defined. NHibernate has no way of distinguishing between duplicate rows. NHibernate resolves this problem by completely removing (in a single DELETE) and recreating the collection whenever it changes. This might be very inefficient.

不只是多對多， 如果你的集合需要更新， NHibernate 推薦的是

19.5.2. Lists, maps, idbags and sets are the most efficient collections to update

然而 bags 也不是一無是處：

19.5.3. Bags and lists are the most efficient inverse collections

Just before you ditch bags forever, there is a particular case in which bags (and also lists) are much more performant than sets. For a collection with inverse=”true” (the standard bidirectional one-to-many relationship idiom, for example) we can add elements to a bag or list without needing to initialize (fetch) the bag elements! This is because IList.Add() must always succeed for a bag or IList (unlike an ISet). This can make the following common code much faster.

Parent p = sess.Load(id);
Child c = new Child();
c.Parent = p;
p.Children.Add(c);  //no need to fetch the collection!
sess.Flush();

由此可見， bag 在多對多對映更新時效能較差， 如果不需要更新，則可以放心使用， 在需要更新時則 set 是更好的選擇。