Multi mapping query with Dapper

Question

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; }
}

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

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?

Here is the SQL query that I am using:

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

What is the query that you execute to extract the data in join between the Entity and EntityIdentifier tables? — Steve, Feb 27 '16 at 14:59

Steve · Accepted Answer · 2016-02-27T15:17:11.760

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;
}

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)

Thanks I am doing tests and will try your proposition shortly — Bruno, Feb 27 '16 at 15:15
I ended up using a heavily modified version of [this post](http://stackoverflow.com/questions/10202584/using-dapper-to-map-more-than-5-types), using the query with an array of types, since in my real use case (it was much simplified for this question) I have more than 7 classes to map... — Bruno, Feb 27 '16 at 16:55

Multi mapping query with Dapper

1 Answers1

Linked