【AppBox】5年后，我们为什么要从 Entity Framework 转到 Dapper 工具？

前言

时间退回到 2009-09-26，为了演示开源项目 FineUI 的使用方法，我们发布了 AppBox（通用权限管理框架，包括用户管理、职称管理、部门管理、角色管理、角色权限管理等模块），最初的 AppBox 采用 Subsonic 作为 ORM 工具。

遗憾的是，Subsonic后来逐渐不再维护，我们于 2013-08-28 正式从 Subsonic 转到 Entity Framework，最初对 Entity Framework 接触只能用两个字来形容：惊艳！整个 AppBox 项目没有写一行 SQL 代码，甚至没有打开 SQLServer 数据库，全部代码用 C# 来完成，EF CodeFirst小心翼翼的帮组我们完成了从数据库创建，访问，查询，更新，删除等一系列操作。

AppBox的详细介绍：https://www.cnblogs.com/sanshi/p/4030265.html

5 年来，我们一直在津津乐道 Entity Framework 带来的好处，也许是情人眼里出西施，对于它的缺点文过饰非，大可用一句话搪塞：你要完整学习 Entity Framework 知识体系，方能事半功倍，俗话说：磨刀不误砍柴工。

一般来说，新手的问题无外乎如下几点：

1. 数据库在哪？怎么没有数据库初始脚本？

2. 怎么又出错了？到底执行的SQL语句是啥？

3. 怎么支持 MySQL 数据库？为什么SQLServer正常的查询，到MySQL就出错了？

4. 为啥突然数据库都清空了？好恐怖，幸好不是在服务器

5. 性能怎么样？大家都说EF的性能不好

6. 能不能先建数据库，然后生成模型类？

.....

这些问题，有些是可以解决的，有些是对EF不了解遇到的，有些的确是EF自身的问题。

比如对 MYSQL 的支持不好，这个问题在简单的查询时正常，一遇到复杂的查询，总会遇到各种问题。而数据库被清空那个则是不了解EF的 Data Migration机制。性能倒不是大问题，只要合理的查询，加上EF的持续优化，性能应该还是可预期的。

即使一切的问题都可以归纳到没有好好学学，那 Entity Framework 总归还是有一个大问题：入门容易，而知识体系有点复杂，学习曲线会比较陡峭！

为什么要转到Dapper？

如果你认为上面就是我们转到 Dapper 的原因，那你算错了。5年的时间，我们已经对 Entity Framework 有了足够的了解和掌握，因此上面的问题都已不是问题。真正出现问题的不是 Entity Framework，而是我们，好吧，就明说了吧：我们太想念 SQL 语句了！

Entity Framework是一个有益的尝试，尝试向开发人员隐藏 SQL 语句，所有的数据库查询操作都通过面向对象的 C# 语言来完成，可以想象，从关系型数据库抽象为面向对象的语言，这个扭曲力场不可谓不强大，而这个扭曲力会带来两个极端：

1. 简单的操作会更加简单

2. 复杂的操作会更加复杂

哪些是简单的操作呢？

比如创建数据库：

Entity Framework CodeFirst开发模式允许我们只写模型类，程序会在第一次运行时创建数据库，比如一个简单的用户角色关系，通过模型类可以这么定义：

public class Role : IKeyID
{
    [Key]
    public int ID { get; set; }

    [Required, StringLength(50)]
    public string Name { get; set; }

    [StringLength(500)]
    public string Remark { get; set; }


    public virtual ICollection<User> Users { get; set; }
    
}

public class User : IKeyID
{
    [Key]
    public int ID { get; set; }

    [Required, StringLength(50)]
    public string Name { get; set; }

    [Required, StringLength(100)]
    public string Email { get; set; }

    [Required, StringLength(50)]
    public string Password { get; set; }

    public virtual ICollection<Role> Roles { get; set; }
    
}

然后通过C#代码定义模型关联：

modelBuilder.Entity<Role>()
    .HasMany(r => r.Users)
    .WithMany(u => u.Roles)
    .Map(x => x.ToTable("RoleUsers")
        .MapLeftKey("RoleID")
        .MapRightKey("UserID"));

这里是意思是：

1. 一个角色可以有多个用户（HasMany）

2. 一个用户可以有多个角色（WithMany）

3. 将这种关联关系保存到数据库表 RoleUsers，对于两个外键：RoleID和UserID

上面的代码如果在MySQL数据库中直接创建，熟悉SQL语句的会感觉更加亲切：

CREATE TABLE IF NOT EXISTS `roles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) CHARACTER  NOT NULL,
  `Remark` varchar(500) CHARACTER  DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
);

CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) CHARACTER  NOT NULL,
  `Email` varchar(100) CHARACTER  NOT NULL,
  `Password` varchar(50) CHARACTER  NOT NULL,
  `Enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
);

CREATE TABLE IF NOT EXISTS `roleusers` (
  `RoleID` int(11) NOT NULL,
  `UserID` int(11) NOT NULL,
  PRIMARY KEY (`RoleID`,`UserID`),
  KEY `Role_Users_Target` (`UserID`),
  CONSTRAINT `Role_Users_Source` FOREIGN KEY (`RoleID`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `Role_Users_Target` FOREIGN KEY (`UserID`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

在表 roleusers 中，创建了两个约束，分别是：

1. Role_Users_Source：定义外键 RoleID，关联 roles 表的 ID 列，并使用 ON DELETE CASCADE 定义级联删除，如果roles 表删除了一行数据，那么roleusers 中一行或多行关联数据会被删除

2. Role_Users_Target：定义外键 UserID，关联 users 表的 ID 列，同样定义级联删除规则

再比如简单的CRUD操作：

获取指定ID的角色：

DB.Roles.Find(id)

更新某个角色：

Role item = DB.Roles.Find(id);
item.Name = tbxName.Text.Trim();
item.Remark = tbxRemark.Text.Trim();
DB.SaveChanges();

删除某个角色：

DB.Roles.Where(r => r.ID == roleID).Delete();

获取某个角色下的用户数：

DB.Users.Where(u => u.Roles.Any(r => r.ID == roleID)).Count();

这个C#代码虽然看着简单，不是 Entity Framework 生成的SQL语句看起来却不是很友好：

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Users] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[RoleUsers] AS [Extent2]
        WHERE ([Extent1].[ID] = [Extent2].[UserID]) AND ([Extent2].[RoleID] = @p__linq__0)
    )
)  AS [GroupBy1]

可能是考虑到 C# 代码可能会比较复杂，从通用性的角度出发，EF为一个简单的查询生成了包含 3 个 SELECT 的 SQL 查询语句。

如果仔细观察上面的SQL代码，有效的只是如下部分：

SELECT 
COUNT(1)
FROM [dbo].[Users]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[RoleUsers]
    WHERE ([Users].[ID] = [RoleUsers].[UserID]) AND ([RoleUsers].[RoleID] = @p__linq__0)
)

而这个SQL的外层SELECT其实是多余的，简化后的SQL代码是这样的：

SELECT 
    COUNT(*)
    FROM [dbo].[RoleUsers]
    WHERE ([Users].[ID] = [RoleUsers].[UserID]) AND ([RoleUsers].[RoleID] = @p__linq__0)

可见，为了完成需要的操作，Entity Framework为我们封装了多余的SQL代码，这让我们有点担心，且不说多余的两个SELECT会不会对性能有印象（这里可能没有，复杂的情况就不一定了），EF总给人一种雾里看花的感觉，因为最终还是要落实到SQL语句上来。

完成同样的操作，用 Dapper 可能要稍微多写点代码，但是 SQL 语句让人看着心里更有谱：

获取指定ID的角色：

conn.QuerySingleOrDefault<Role>("select * from roles where ID = @RoleID", new { RoleID = roleID });

更新某个角色：

Role item = GetCurrentRole(id);
item.Name = tbxName.Text.Trim();
item.Remark = tbxRemark.Text.Trim();

conn.Execute("update roles set Name = @Name, Remark = @Remark where ID = @ID", item);

删除某个角色：

conn.Execute("delete from roles where ID = @RoleID", new { RoleID = roleID });

获取某个角色下的用户数：

conn.QuerySingle<int>("select count(*) from roleusers where RoleID = @RoleID", new { RoleID = roleID });

哪些是复杂的操作呢？

因为数据库是关系型，Entity Framework偏偏要用面向对象的 C# 来操作，遇到级联关系的更新时，EF就会变得有点复杂。

比如从某个角色中删除多个用户：

在 Entity Framework中，我们需要先获取这个角色以及属于这个角色的用户，然后才能执行删除操作。

int roleID = GetSelectedDataKeyID(Grid1);
List<int> userIDs = GetSelectedDataKeyIDs(Grid2);

Role role = DB.Roles.Include(r => r.Users)
    .Where(r => r.ID == roleID)
    .FirstOrDefault();

foreach (int userID in userIDs)
{
    User user = role.Users.Where(u => u.ID == userID).FirstOrDefault();
    if (user != null)
    {
        role.Users.Remove(user);
    }
}

DB.SaveChanges();

从代码逻辑上讲，这个代码片段是很直观的：

1. 首先获取当前角色，由于后面要操作角色的用户列表，所以使用 Include 语句，这将导致生成SQL查询语句有点复杂：

SELECT 
    [Project2].[ID] AS [ID], 
    [Project2].[Name] AS [Name], 
    [Project2].[Remark] AS [Remark], 
    [Project2].[C1] AS [C1], 
    [Project2].[ID1] AS [ID1], 
    [Project2].[Name1] AS [Name1], 
    FROM ( SELECT 
        [Limit1].[ID] AS [ID], 
        [Limit1].[Name] AS [Name], 
        [Limit1].[Remark] AS [Remark], 
        [Join1].[ID] AS [ID1], 
        [Join1].[Name] AS [Name1], 
        CASE WHEN ([Join1].[RoleID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1) 
            [Extent1].[ID] AS [ID], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[Remark] AS [Remark]
            FROM [dbo].[Roles] AS [Extent1]
            WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN  (SELECT [Extent2].[RoleID] AS [RoleID], [Extent3].[ID] AS [ID], [Extent3].[Name] AS [Name]
            FROM  [dbo].[RoleUsers] AS [Extent2]
            INNER JOIN [dbo].[Users] AS [Extent3] ON [Extent3].[ID] = [Extent2].[UserID] ) AS [Join1] ON [Limit1].[ID] = [Join1].[RoleID]
    )  AS [Project2]
    ORDER BY [Project2].[ID] ASC, [Project2].[C1] ASC

2. 遍历需要删除的用户列表，并从当前角色的用户列表中删除，这将执行多个SQL语句：

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=3,@1=45
go

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=3,@1=46
go

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=3,@1=47
go

。。。。。

上面的C#代码以及生成的SQL语句之所以这么复杂，归根到底是因为 Entity Framework 企图使用面向对象的方式操作关系型数据库，换句话说：模型类对数据库的 RoleUsers 表是一无所知的。

而使用 Dapper 代码，代码非常简单，因为我们可以直接操作 roleusers 表：

int roleID = GetSelectedDataKeyID(Grid1);
List<int> userIDs = GetSelectedDataKeyIDs(Grid2);

conn.Execute("delete from roleusers where RoleID = @RoleID and UserID in @UserIDs", new { RoleID = roleID, UserIDs = userIDs });

再比如更新某个用户的角色列表：

在 Entity Framework中，我们需要先获取这个用户以及属于这个用户的角色，然后才能执行替换操作。

User item = DB.Users
    .Include(u => u.Roles)
    .Where(u => u.ID == id).FirstOrDefault();

int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text);
ReplaceEntities<Role>(item.Roles, roleIDs);

DB.SaveChanges();

而 ReplaceEntities 是我们自定义的一个帮助函数：

protected void ReplaceEntities<T>(ICollection<T> existEntities, int[] newEntityIDs) where T : class,  IKeyID, new()
{
    if (newEntityIDs.Length == 0)
    {
        existEntities.Clear();
    }
    else
    {
        int[] tobeAdded = newEntityIDs.Except(existEntities.Select(x => x.ID)).ToArray();
        int[] tobeRemoved = existEntities.Select(x => x.ID).Except(newEntityIDs).ToArray();

        AddEntities<T>(existEntities, tobeAdded);

        existEntities.Where(x => tobeRemoved.Contains(x.ID)).ToList().ForEach(e => existEntities.Remove(e));
    }
}

由于 Entity Framework 明确知道了删除哪些角色，以及添加哪些角色，所以会生成多条插入删除SQL语句，类似：

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=3,@1=50
go

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=23,@1=50
go

exec sp_executesql N'DELETE [dbo].[RoleUsers]
WHERE (([RoleID] = @0) AND ([UserID] = @1))',N'@0 int,@1 int',@0=33,@1=50
go

exec sp_executesql N'INSERT [dbo].[RoleUsers]([RoleID], [UserID])
VALUES (@0, @1)
',N'@0 int,@1 int',@0=4,@1=50
go

exec sp_executesql N'INSERT [dbo].[RoleUsers]([RoleID], [UserID])
VALUES (@0, @1)
',N'@0 int,@1 int',@0=6,@1=50
go

exec sp_executesql N'INSERT [dbo].[RoleUsers]([RoleID], [UserID])
VALUES (@0, @1)
',N'@0 int,@1 int',@0=7,@1=50
go

。。。。。。

而使用Dapper更加简单，我们无需知道此用户有哪些角色，可以直接操作 roleusers 数据库：

User item = DB.Users
    .Include(u => u.Roles)
    .Where(u => u.ID == id).FirstOrDefault();

int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text);

conn.Execute("delete from roleusers where UserID = @UserID", new { UserID = userID });
conn.Execute("insert roleusers (UserID, RoleID) values (@UserID, @RoleID)", roleIDs.Select(u => new { UserID = userID, RoleID = u }).ToList());