Dapper 的关系 一对一，一对多，多对多

Dapper 提供了一种称为Multi mapping的功能，使您能够将单行中的数据显式映射到多个对象。

一对多关系

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    ...
    public Category Category { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    ...
    public ICollection<Product> Products { get; set; }
}

 

 

select productid, productname, p.categoryid, categoryname 
from products p 
inner join categories c on p.categoryid = c.categoryid

　　 执行以上Sql得到以下结果

　　

 

[Route("QueryOneToOne")]
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> QueryOneToOne()
        {//一对一关系查询，一个Product属于一个Category
            var sql = @"select productid,productname,p.categoryid,categoryname from products p
join categories c on p.categoryid=c.categoryid";
            using (var con = new SqlConnection(MyConfig.ConnectionString))
            {//Query和方法有重载，QueryAsync它们采用多个泛型参数和一个Func<TFirst, ..., TReturn> map参数。
                //QueryAsync<Product, Category, Product>,
                //Product, Category，为映射参数，最后一个Product为返回类型
                var products = await con.QueryAsync<Product, Category, Product>
                    (sql, (product, category) =>
                    {//将结果数据映射到返回类型。
                        product.Category = category;
                        product.CategoryId = category.CategoryId;
                        return product;
                    }, splitOn: "CategoryId");
                //splitOn这告诉 Dapper 拆分CategoryId列上的数据。该列之前的任何内容都映射到第一个参数 (Product)，
                //该列之后的任何其他内容都应该映射到第二个输入参数 (the Category)。
                return Ok(products);
            }
        }
        [Route("QueryOneToMany")]
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Category>>> QueryOneToMany()
        {//一对多关系查询，一个Category包含多个Product
            var sql = @"select productid,productname,p.categoryid,categoryname from products p
join categories c on p.categoryid=c.categoryid";
            using (var con = new SqlConnection(MyConfig.ConnectionString))
            {//Query和方法有重载，QueryAsync它们采用多个泛型参数和一个Func<TFirst, ..., TReturn> map参数。
                //QueryAsync<Product, Category, Product>,
                //Product, Category，为映射参数，最后一个Product为返回类型
                var categories = await con.QueryAsync<Product, Category, Category>
                    (sql, (product, category) =>
                    {
                        category.Products.Add(product);
                        category.Products.ForEach(m => m.CategoryId = category.CategoryId);
                        return category;
                    }, splitOn: "CategoryId");
                //splitOn这告诉 Dapper 拆分CategoryId列上的数据。该列之前的任何内容都映射到第一个参数 (Product)，
                //该列之后的任何其他内容都应该映射到第二个输入参数 (the Category)。
                //用分组去除重复的类别
                var result = categories.GroupBy(c => c.CategoryId).Select(g =>
                {
                    var groupcategory = g.First();
                    groupcategory.Products = g.Select(c => c.Products.Single()).ToList();
                    return groupcategory;
                });
                return Ok(result);
            }
        }

多对多关系

 多重映射也适用于多对多关系。

 

 

public class Tag
{
    public int TagId { get; set; }
    public string TagName { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Headline { get; set; }
    public string Content { get; set; }
    public Author Author { get; set; }
    public List<Tag> Tags { get; set; } 
}

　　

 

 以下 SQL 检索与每个帖子相关的标签信息：

select p.postid, headline, t.tagid, tagname
from posts p 
inner join posttags pt on pt.postid = p.postid
inner join tags t on t.tagid = pt.tagid

每个标签返回一行，导致帖子信息重复：

 

using (var connection = new SQLiteConnection(connString))
{
    var sql = @"select p.postid, headline, t.tagid, tagname
                from posts p 
                inner join posttags pt on pt.postid = p.postid
                inner join tags t on t.tagid = pt.tagid";
    var posts = await connection.QueryAsync<Post, Tag, Post>(sql, (post, tag) => {
        post.Tags.Add(tag);
        return post;
    }, splitOn: "tagid");
//对结果去除重复的记录
    var result = posts.GroupBy(p => p.PostId).Select(g =>
    {
        var groupedPost = g.First();
        groupedPost.Tags = g.Select(p => p.Tags.Single()).ToList();
        return groupedPost;
    });
    foreach(var post in result)
    {
        Console.Write($"{post.Headline}: ");
        foreach(var tag in post.Tags)
        {
            Console.Write($" {tag.TagName} ");
        }
        Console.Write(Environment.NewLine);
    }
}

　　

多重关系 

多重映射适用于多重关系。

select p.postid, headline, firstname, lastname, t.tagid, tagname
from posts p 
inner join authors a on p.authorid = a.authorid
inner join posttags pt on pt.postid = p.postid
inner join tags t on t.tagid = pt.tagid

　　多重映射函数接受一个额外的输入参数，表示要检索的额外实体：

using (var connection = new SQLiteConnection(connString))
{
    var sql = @"select p.postid, headline, firstname, lastname, t.tagid, tagname
                from posts p 
                inner join authors a on p.authorid = a.authorid
                inner join posttags pt on pt.postid = p.postid
                inner join tags t on t.tagid = pt.tagid";
    var posts = await connection.QueryAsync<Post, Author, Tag, Post>(sql, (post, author, tag) => {
        post.Author = author;
        post.Tags.Add(tag);
        return post;
    }, splitOn: "firstname, tagid");
    var result = posts.GroupBy(p => p.PostId).Select(g =>
    {
        var groupedPost = g.First();
        groupedPost.Tags = g.Select(p => p.Tags.Single()).ToList();
        return groupedPost;
    });
    foreach(var post in result)
    {
        Console.Write($"{post.Headline}: ");
        foreach(var tag in post.Tags)
        {
            Console.Write($" {tag.TagName} ");
        }
        Console.Write($" by {post.Author.FirstName} {post.Author.LastName} {Environment.NewLine}");
    }
}