[C#][ASP.NET] Web API 开发心得 (6) - 轻量级的 ORM 工具 Dapper + Slappe

写了这么多篇终于要进入资料库的部分，今天要和大家分享的是轻量级 ORM 框架 Dapper，自己从最开始的 ADO.NET 配合 DataTable 到后来流行的 Entity Framework 都使用过，而第一次接触 Entity Framework 就可以很明显感受到它的优点，不必写 SQL 用 LINQ 就可以产生语法，并且会将结果映射到 C# 物件上，这对当时只写过 ADO.NET 的我来说是非常不可思议的。

不过程式框架不可能样样好，慢慢就会发现一些问题，例如我们碰上複杂查询时，用 LINQ 写出来的程式可读性会远比 SQL 来的差。

再来就像第一篇提到的，Entity Framework 对资料库的侵入性太强，不适合导入旧系统，例如导航属性必须建立资料表关联，而旧系统资料库通常不会做太大的变动，怕引响到现有功能，因此导入 Entity Framework 会有其限制在。

不过对我来说最大的困难点是在团队上，相信很多人都遇过，有些工程师很排斥写 SQL，有些很排斥写 LINQ，而我自己的团队中刚好都是喜欢写 SQL 的工程师 XD
所以...我还是只能乖乖的写 SQL...。

不过对 Web 工程师而言 SQL 是很重要的工具，还是要逼着自己学习，接着我就开始搜寻一些辅助型的 ORM 工具，看到很多前辈推荐 Dapper，它可以将查询的结果 Mapping 到物件上，比起 DataTable 多了强型别的优点，并且后续还可以接着 LINQ 做其他的处理，使用后觉得很不错。

而我自己是不排斥 Entity Framework 的，未来有机会也会写一些 EF 的心得分享给大家，接下来就进入今天的主题，Dapper + Slapper.AutoMapper。

安装套件

使用 Nuget 安装 Dapper。

建立资料表

开启 SQL Server Management 新增资料库和四张资料表。

Student 学生

Junior 毕业国中

Exam 考试

ExamType 考试类型

建立 Model

新增 Model 资料夹，并新增 Student、Junior、Exam、ExamType 四个 DTO 类别，接着在 Student 类别建立两个导航属性 Junior 和 Exams，分别代表学生和毕业国中是属于多对一关係，学生和考试是一对多关係，然后在 Exam 类别建立 ExamType 导航属性，代表考试和考试类型是多对一关係，最后加上栏位说明，我通常会把栏位说明写在 Model 上，因为这样写程式时 Visual Sudio 会自动带出，非常方便。

程式码:

namespace Model{    /// <summary>    /// 学生    /// </summary>    public class Student    {        public int Id { get; set; }        /// <summary>        /// 学号        /// </summary>        public string Sid { get; set; }        /// <summary>        /// 姓名        /// </summary>        public string Name { get; set; }        /// <summary>        /// 毕业国中 Id        /// </summary>        public int JuniorId { get; set; }        public Junior Junior { get; set; }        public List<Exam> Exams { get; set; }    }}

namespace Model{    /// <summary>    /// 国中    /// </summary>    public class Junior    {        public int Id { get; set; }        /// <summary>        /// 校代码        /// </summary>        public string Code { get; set; }        /// <summary>        /// 学校名称        /// </summary>        public string Name { get; set; }    }}

namespace Model{    /// <summary>    /// 考试    /// </summary>    public class Exam    {        public int Id { get; set; }        /// <summary>        /// 考试名称        /// </summary>        public string Name { get; set; }        /// <summary>        /// 分数        /// </summary>        public decimal Score { get; set; }        /// <summary>        /// 学生 Id        /// </summary>        public int StudentId { get; set; }        /// <summary>        /// 考试类型 Id        /// </summary>        public int ExamTypeId { get; set; }        public ExamType ExamType { get; set; }    }}

namespace Model{    /// <summary>    /// 考试类型    /// </summary>    public class ExamType    {        public int Id { get; set; }        /// <summary>        /// 考试类型名称        /// </summary>        public string Name { get; set; }    }}

建立连线字串

到 Web.config 内新增资料库的连线字串 ConnectionString。

<configuration>  <connectionStrings>    <add name="SQLConnectionString" connectionString="         Data Source=.;         Initial Catalog=WebApiTest;         Connection Timeout=300000;         Persist Security Info=True;         User ID=帐号;         Password=密码" providerName="System.Data.SqlClient" />  </connectionStrings></configuration>

新增 Connection 资料夹，并新增 ConnectionFactory 类别，我们会使用这个类别来统一管理 SqlConnection 的产生，而不直接在 Controller 用 new 去产生物件，Controller 只相依于 IDbConnection 介面，降低程式耦合度，CreateConnection 方法也保留了连线多个资料库的弹性，可透过参数 name 去产生不同资料库的 SqlConnection。

程式码:

namespace Connection{    public class ConnectionFactory    {        public IDbConnection CreateConnection(string name = "default")        {            switch (name)            {                case "default":                    {                        var ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;                        return new SqlConnection(ConnectionString);                    }                default:                    {                        throw new Exception("name 不存在。");                    }            }        }    }}

资料夹结构

Dapper 用法

执行没有回传的 SQL 命令 Execute

Execute 用于没有回传结果的 SQL 命令，例如 INSERT、UPDATE、DELETE，Dapper 会将传入的物件 Mapping 到具名参数上。

api/student/insert

var cn = _connectionFactory.CreateConnection();var sql = "INSERT INTO Student ([Sid], Name, JuniorId) VALUES (@Sid, @Name, @JuniorId)";var newStudent = new Student{    Sid = "100001",    Name = "小明",    JuniorId = 1,};cn.Execute(sql, newStudent);

批次执行 Execute

在 Execute 方法，传入实作 IEnumerable 介面的物件，Dapper 会批次执行这段 SQL 语法。

api/student/batch/insert

var cn = _connectionFactory.CreateConnection();var sql = "INSERT INTO Student ([Sid], Name, JuniorId) VALUE(@Sid, @Name, @JuniorId)";var newStudents = new List<Student>{    new Student    {        Sid = "100001",        Name = "小明",        JuniorId = 1,    },    new Student    {        Sid = "100002",        Name = "小华",        JuniorId = 2,    }};cn.Execute(sql, newStudents);

参数化查询 Query

可传入匿名物件，方便参数命名，可指定查询结果要 Mapping 的物件型别，如果未指定预设传回 IEnumerable<dynamic>。

api/student/query

var cn = _connectionFactory.CreateConnection();var sql = @"SELECT * FROM Student AS A WHERE A.Name=@Name";var studentList = cn.Query<Student>(sql, new { Name = "小明" }).ToList();

{    "success": true,    "msg": null,    "data": [        {            "Id": 1,            "Sid": "100001",            "Name": "小明",            "JuniorId": 1,            "Junior": null,            "Exams": null        }    ]}

参数阵列展开 Query

在 Query 方法，可直接传入阵列 (IEnumerable) 型态的具名参数，Dapper 会自动将其展开，例如 WHERE Id IN @Ids 自动展开成 WHERE Id IN (@Ids1, @Ids2, @Ids3)，这是我非常喜欢的功能，以往要用迴圈去绕阵列，再串出 SQL 语法，现在 Dapper 都帮我们做完了，程式变的很简洁。

api/student/query/in

var cn = _connectionFactory.CreateConnection();var sql = @"SELECT * FROM Student AS A WHERE A.Name IN @Names";var studentList = cn.Query<Student>(sql, new { Names = new string[] { "小明", "小华" } }).ToList();

{    "success": true,    "msg": null,    "data": [        {            "Id": 1,            "Sid": "100001",            "Name": "小明",            "JuniorId": 1,            "Junior": null,            "Exams": null        },        {            "Id": 2,            "Sid": "100002",            "Name": "小华",            "JuniorId": 3,            "Junior": null,            "Exams": null        }    ]}

一对一或多对一映射 Mapping

Dapper 能像 Entity Framework 一样将查询映射到导航属性上，预设会使用 Id 去做切割，如果想用其他名称切割可以使用 splitOn 参数。

api/student/mapping/manyToOne

var cn = _connectionFactory.CreateConnection();var sql = @"SELECT *             FROM Student AS A            LEFT JOIN Junior AS B ON B.Id=A.JuniorId";var studentList = cn.Query<Student, Junior, Student>(sql, (s, j) =>{    s.Junior = j;    return s;}).ToList();

{    "success": true,    "msg": null,    "data": [        {            "Id": 1,            "Sid": "100001",            "Name": "小明",            "JuniorId": 1,            "Junior": {                "Id": 1,                "Code": "000001",                "Name": "学校1"            },            "Exams": null        },        {            "Id": 2,            "Sid": "100002",            "Name": "小华",            "JuniorId": 3,            "Junior": {                "Id": 3,                "Code": "000002",                "Name": "学校2"            },            "Exams": null        }    ]}

一对多映射 Mapping

Dapper 在处理一对多映射比较麻烦，需要用 Dictionary 去过滤主表因 Join 而产生重複的部分。

api/student/mapping/oneToMany

var cn = _connectionFactory.CreateConnection();var sql = @"SELECT *             FROM Student AS A            LEFT JOIN Exam AS B ON B.StudentId=A.Id";var studentDictionary = new Dictionary<int, Student>();var studentList = cn.Query<Student, Exam, Student>(sql, (s, e) =>{    var entity = null as Student;    if (!studentDictionary.TryGetValue(s.Id, out entity))    {        entity = s;        entity.Exams = new List<Exam>();        studentDictionary.Add(entity.Id, entity);    }    s.Exams.Add(e);    return s;}).Distinct().ToList();

{    "success": true,    "msg": null,    "data": [        {            "Id": 1,            "Sid": "100001",            "Name": "小明",            "JuniorId": 1,            "Junior": null,            "Exams": [                {                    "Id": 1,                    "Name": "国文",                    "Score": 60,                    "StudentId": 1,                    "ExamTypeId": 2,                    "ExamType": null                },                {                    "Id": 4,                    "Name": "英文",                    "Score": 70,                    "StudentId": 1,                    "ExamTypeId": 3,                    "ExamType": null                }            ]        },        {            "Id": 2,            "Sid": "100002",            "Name": "小华",            "JuniorId": 3,            "Junior": null,            "Exams": [                {                    "Id": 6,                    "Name": "国文",                    "Score": 50,                    "StudentId": 2,                    "ExamTypeId": 2,                    "ExamType": null                },                {                    "Id": 8,                    "Name": "英文",                    "Score": 60,                    "StudentId": 2,                    "ExamTypeId": 3,                    "ExamType": null                }            ]        }    ]}

看到这里，前面几个还好，最后一个例子，很多人一定会觉得怎么这么难用，没错我自己也有发现，一对多映射是 Dapper 的弱点，由其在实务上不太可能都是单层关係，常常会碰到 Join 一大堆资料表的情况，Entity Framework 可以很容易的用 Include 完成好几层的複杂映射，而用 Dapper 就会发现，自己怎么写出了一大堆複杂难懂的程式码，大半的程式都在做映射，这个问题也困扰了我好久，后来也是爬了很多文章后，发现 Slapper.AutoMapper 这个套件，它可以利用 SQL 语法来辅助 Dapper 完成多层複杂的映射，虽然还是有缺点，不过比起原来的作法已经好很多，这个套件好像国内比较少人用，所以想分享给大家。

安装套件

使用 Nuget 安装 Slapper.AutoMapper。

Slapper.AutoMapper 用法

Slapper.AutoMapper 是一个映射库，可以将动态类型 dynamic，转换为静态类型并填充其複杂的关联子物件，Slapper 主要会先将 dynamic 转换为 IDictionary<string, object>，接着透过下划线表示法，将下划线的部分填充到子物件中，下面来看实际用法。

多层複杂映射

api/student/mapping/slapper

下面我 Join 了四张资料表并将结果映射到 Student 类别，类别内 一对多、多对一、多层嵌套的关係都有，然后可以看到 SQL 语法内，使用了下划线来表示物件彼此的层级关係，Slapper 依据此关係就可以自动完成複杂的映射动作，逻辑比上面用 Dapper 简单多了。

var cn = _connectionFactory.CreateConnection();var sql = @"SELECT A.*,                   B.Id AS Junior_Id,               B.Code AS Junior_Code,                B.Name AS Junior_Name,               C.Id AS Exams_Id,               C.Name AS Exams_Name,               C.Score AS Exams_Score,               C.StudentId AS Exams_StudentId,               C.ExamTypeId AS Exams_ExamTypeId,               D.Id AS Exams_ExamType_Id,               D.Name AS Exams_ExamType_Name            FROM Student AS A            LEFT JOIN Junior AS B ON B.Id=A.JuniorId            LEFT JOIN Exam AS C ON C.StudentId=A.Id            LEFT JOIN ExamType AS D ON D.Id=C.ExamTypeId";var dy = cn.Query<dynamic>(sql);var studentList = Slapper.AutoMapper.MapDynamic<Student>(dy, false).ToList();

{    "success": true,    "msg": null,    "data": [        {            "Id": 1,            "Sid": "100001",            "Name": "小明",            "JuniorId": 1,            "Junior": {                "Id": 1,                "Code": "000001",                "Name": "学校1"            },            "Exams": [                {                    "Id": 1,                    "Name": "国文",                    "Score": 60,                    "StudentId": 1,                    "ExamTypeId": 2,                    "ExamType": {                        "Id": 2,                        "Name": "期中考"                    }                },                {                    "Id": 4,                    "Name": "英文",                    "Score": 70,                    "StudentId": 1,                    "ExamTypeId": 3,                    "ExamType": {                        "Id": 3,                        "Name": "期末考"                    }                }            ]        },        {            "Id": 2,            "Sid": "100002",            "Name": "小华",            "JuniorId": 3,            "Junior": {                "Id": 3,                "Code": "000002",                "Name": "学校2"            },            "Exams": [                {                    "Id": 6,                    "Name": "国文",                    "Score": 50,                    "StudentId": 2,                    "ExamTypeId": 2,                    "ExamType": {                        "Id": 2,                        "Name": "期中考"                    }                },                {                    "Id": 8,                    "Name": "英文",                    "Score": 60,                    "StudentId": 2,                    "ExamTypeId": 3,                    "ExamType": {                        "Id": 3,                        "Name": "期末考"                    }                }            ]        }    ]}

Slapper.AutoMapper 在做映射时会需要指定每个类别的键值，
预设的键值有下面三种:

範例:

如果想要指定其他键值可以透过四种方法:

Slapper.AutoMapper.Configuration.IdentifierConventions.Add(type => type.Name + "_Id");

Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Student), new List <string> { "StudentId"，"StudentType" });

public class Student{    [Slapper.AutoMapper.Id]    public int Id { get; set; }}

Slapper.AutoMapper.Configuration.IdentifierAttributeType = typeof(CustomAttribute);

结语

今天终于进入到资料库的部分，不过写的东西不是主流的 Entity Framework，虽然 Dapper 蛮多人在用的，不过 Slapper 目前还没有看到国内有相关的文章，所以不知道大家的接受度如何，我自己是觉得蛮好用的，到这里 Dapper 在查询方面已经算完成，配合 Slapper 多层複杂的物件映射也都可以简单完成，不过在新增、修改、删除方面，每次操作还是需要为每个 Table 写 SQL 语法，使用上不是很方便，所以下一篇我会介绍如何将 Insert、Update、Delete 抽离成共用的方法，操作起来就像 Entity Framework 一样方便，今天就介绍到这里，感谢大家观看。

最后附上完整的测试程式

namespace Api{    [Result]    [Exception]    [RoutePrefix("api/student")]    public class StudentController : BaseController    {        private ConnectionFactory _connectionFactory;        public StudentController()        {            _connectionFactory = new ConnectionFactory();        }        //Insert        [HttpGet]        [Route("insert")]        public void Insert()        {            var cn = _connectionFactory.CreateConnection();            var sql = "INSERT INTO Student ([Sid], Name, JuniorId) VALUES (@Sid, @Name, @JuniorId)";            var newStudent = new Student            {                Sid = "100001",                Name = "小明",                JuniorId = 1,            };            cn.Execute(sql, newStudent);        }        //BatchInsert        [HttpGet]        [Route("batch/insert")]        public void BatchInsert()        {            var cn = _connectionFactory.CreateConnection();            var sql = "INSERT INTO Student ([Sid], Name, JuniorId) VALUES (@Sid, @Name, @JuniorId)";            var newStudents = new List<Student>            {                new Student                {                    Sid = "100001",                    Name = "小明",                    JuniorId = 1,                },                new Student                {                    Sid = "100002",                    Name = "小华",                    JuniorId = 2,                }            };            cn.Execute(sql, newStudents);        }        //Query        [HttpGet]        [Route("query")]        public List<Student> Query()        {            var cn = _connectionFactory.CreateConnection();            var sql = @"SELECT * FROM Student AS A WHERE A.Name=@Name";            var studentList = cn.Query<Student>(sql, new { Name = "小明" }).ToList();            return studentList;        }        //QueryIn        [HttpGet]        [Route("query/in")]        public List<Student> QueryIn()        {            var cn = _connectionFactory.CreateConnection();            var sql = @"SELECT * FROM Student AS A WHERE A.Name IN @Names";            var studentList = cn.Query<Student>(sql, new { Names = new string[] { "小明", "小华" } }).ToList();            return studentList;        }        //MappingManyToOne        [HttpGet]        [Route("mapping/manyToOne")]        public List<Student> MappingManyToOne()        {            var cn = _connectionFactory.CreateConnection();            var sql = @"SELECT *                         FROM Student AS A                        LEFT JOIN Junior AS B ON B.Id=A.JuniorId";            var studentList = cn.Query<Student, Junior, Student>(sql, (s, j) =>            {                s.Junior = j;                return s;            })            .ToList();            return studentList;        }        //MappingOneToMany        [HttpGet]        [Route("mapping/oneToMany")]        public List<Student> MappingOneToMany()        {            var cn = _connectionFactory.CreateConnection();            var sql = @"SELECT *                         FROM Student AS A                        LEFT JOIN Exam AS B ON B.StudentId=A.Id";            var studentDictionary = new Dictionary<int, Student>();            var studentList = cn.Query<Student, Exam, Student>(sql, (s, e) =>            {                var entity = null as Student;                if (!studentDictionary.TryGetValue(s.Id, out entity))                {                    entity = s;                    entity.Exams = new List<Exam>();                    studentDictionary.Add(entity.Id, entity);                }                entity.Exams.Add(e);                return entity;            })            .Distinct().ToList();            return studentList;        }        //MappingSlapper        [HttpGet]        [Route("mapping/slapper")]        public List<Student> MappingSlapper()        {            var cn = _connectionFactory.CreateConnection();            var sql = @"SELECT A.*,                               B.Id AS Junior_Id,                           B.Code AS Junior_Code,                            B.Name AS Junior_Name,                           C.Id AS Exams_Id,                           C.Name AS Exams_Name,                           C.Score AS Exams_Score,                           C.StudentId AS Exams_StudentId,                           C.ExamTypeId AS Exams_ExamTypeId,                           D.Id AS Exams_ExamType_Id,                           D.Name AS Exams_ExamType_Name                        FROM Student AS A                        LEFT JOIN Junior AS B ON B.Id=A.JuniorId                        LEFT JOIN Exam AS C ON C.StudentId=A.Id                        LEFT JOIN ExamType AS D ON D.Id=C.ExamTypeId";            var dy = cn.Query<dynamic>(sql);            var studentList = Slapper.AutoMapper.MapDynamic<Student>(dy, false).ToList();                        return studentList;        }    }}

2019/03/03 补充:

最近发现 Slapper.AutoMapper 如果 JOIN 的表太多会有效能上的问题，尤其在 JOIN 的副表资料很多的情况，例如: 学生 JOIN 考试成绩
如果考试成绩的资料很多，接着又 JOIN 五、六张表，这种情况下 Slapper 的处理效率就会很差，而我自己优化的方法是将笔数多的表拉出去另外查询，最后在组合起来，这样就能避免少部分表造成 JOIN 后资料笔数过多的问题。
而 Entity Framework 好像没有类似的问题，真好奇 EF 底层是如何处理映射的。

参考资料

Dapper - 使用 LINQPad 快速产生相对映 SQL Command 查询结果的类别
短小精悍的.NET ORM神器 -- Dapper
Dapper - Result Multi-Mapping
StackExchange/Dapper
SlapperAutoMapper/Slapper.AutoMapper