使用Dapper进行多重映射查询 [英] Multi mapping query with Dapper

问题描述

我的数据库中有这些类及其等效表：

I have these classes and their equivalent tables in my database:

public class Entity
{
    public int Id { get; set; }
    public List<EntityIdentifier> Identifiers { get; set; }

    public BaseEntity()
    {
        Identifiers = new List<EntityIdentifier>();
    }
}

public class EntityIdentifier
{
    public int Id { get; set; }
    public int EntityId { get; set; }

    public string Code { get; set; }
    public string Value { get; set; }
}

我想用Dapper查询数据库并自动映射数据。

I want to query the database with Dapper and automap the data.

我有这个多重映射的例子，来自精简版git页面：

I have this example of multi mapping, from the Dapper git page:

var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);

但是，在此示例中，每个子项（帖子）都有指向其父项（用户）的链接。
就我而言，是父母（实体）指向孩子（标识符）的列表。

However, in this example, each child (post) has a link to its parent (user). In my case, it is the parent (entity) that points to a list of children (identifiers).

怎么办我需要根据情况调整代码吗？

How do I need to adapt the code to my case?

这是我正在使用的SQL查询：

Here is the SQL query that I am using:

SELECT e.*, i.*
FROM Entity e INNER JOIN EntityIdentifier i ON i.EntityId = e.Id

推荐答案

这是我在Dapper相关网站上找到的一个示例。这里的重点是要有一个字典，您可以在其中保留Entity.ID作为键，而Entity作为字典值。然后在lambda表达式中检查字典是否已经包含Entity，如果是，则将EntityIdentifier添加到Entity列表中，否则将Dapper返回的Entity添加到字典中

This is an example I have found somewhere on the Dapper related sites. The point here is to have a dictionary where you keep the Entity.ID as key and Entity as dictionary value. Then in the lambda expression you check if the dictionary already contains the Entity, if yes just add the EntityIdentifier to the Entity list otherwise add the Entity returned by Dapper to the Dictionary

string cmdText = @"SELECT e.*, i.*
                   FROM Entity e INNER JOIN Identifier i ON i.EntityId = e.Id";
var lookup = new Dictionary<int, Entity>();
using (IDbConnection connection = OpenConnection())
{
    var multi = connection.Query<Entity, EntityIdentifier, Entity>(cmdText, 
                                (entity, identifier) =>
    {
        Entity current;
        if (!lookup.TryGetValue(entity.ID, out current))
        {
            lookup.Add(entity.ID, current = entity);
            current.Identifiers = new List<EntityIdentifier>();
        }
        current.Identifiers.Add(identifier);
        return current;
    }, splitOn: "i.ID").Distinct();
    return multi;
}

有时，这会因参数splitOn而变得复杂。不知道是否需要重复将其显式添加到Select语句中（我通常使用IDTable模式）

Sometime this becomes complicated by the parameter splitOn. Not sure if you need to repeat it adding explicitly to the Select statement (I usually use the IDTable pattern)

这篇关于使用Dapper进行多重映射查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！