Dapper one to many relationship mapping

Question

I'm trying to map a one-to-many relationship with dapper. But I don't get it to working.

I have a table order and another one for the products order_product

Now I have this code:

var sql = @"SELECT 
* 
FROM `order` o
INNER JOIN `order_product` op ON op.order_id = o.order_id
WHERE o.order_id = 5153";
var products = await connection.QueryAsync<Order, OrderProduct, Order>(sql, (order, product) =>
{
    if (order.Products == null)
    {
        order.Products = new List<OrderProduct>();
    }

    if (product != null)
    {
        order.Products.Add(product);
    }

    return order;
}, splitOn: "order_id");

For testing purposes I'm loading the order with id 5153 which consists of 4 products.

The class OrderProduct is defined as:

public class OrderProduct
    {
        public int order_product_id { get; set; }
        public int order_id { get; set; }
        public int product_id { get; set; }
        public string name { get; set; }
        public string model { get; set; }
        public int quantity { get; set; }
        public decimal price { get; set; }
        public decimal total { get; set; }
        public decimal tax { get; set; }
        public int reward { get; set; }
    }

The order class holds all properties from the order table +

public ICollection<OrderProduct> Products { get; set; }

However instead of 1 Order object with 4 OrderProduct objects I get 4 Order objects where each contains one product of the 4.

Does anybody know what I did wrong here?

Problem is you have two `order_id` columns. Ideally you would specify each column rather than use `SELECT *`. Any case you are splitting at the wrong column, you want to split at `order_product_id`. — Charlieface, Mar 23 '22 at 12:00
Does this answer your question? [Correct use of multimapping in Dapper](https://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper) — Charlieface, Mar 23 '22 at 12:00

J.Salas · Answer 1 · 2022-03-23T12:16:41.753

Try this way:

        var sql = @"SELECT * FROM `order` o 
              INNER JOIN `order_product` op ON op.order_id = o.order_id
            WHERE o.order_id = 5153";
        var orderDictionary = new Dictionary<int, Order>();
        var products = await connection.QueryAsync<Order, OrderProduct, Order>(sql, (order, product) =>
        {
            if (!orderDictionary.TryGetValue(order.order_id, out Order docEntry))
            {
                docEntry = order;
                docEntry.Products = new List<OrderProduct>();
                orderDictionary.Add(docEntry.order_id, docEntry);
            }

            if (product != null) docEntry.Products.Add(product);
            docEntry.Products = docEntry.Products.Distinct().ToList();

            return docEntry;
        }
        , splitOn: "order_id");

you got the order List on the orderDictionary, if you only want a list, get it this way

var orderList = orderDictionary.Values.ToList();

select * is a bad idea for productions queries, you need to be sure your order_id column is the splitOn one, and you have two of them, normally Dapper does his work and try to guess which one is the splitter, but bettter if you fix itby aliasing one of them

That's not a good reference - it's not Dapper, it's an unrelated site that ["borrowed" the name without permission to make money](https://twitter.com/marcgravell/status/1359439987050237952) and is full of errors. The *actual* multimapping docs are [in the Github landing page](https://github.com/DapperLib/Dapper#multi-mapping) — Panagiotis Kanavos, Mar 23 '22 at 11:50
I know isn't the actual official docs, but I didn't know is a 'roge' page, I'm removing the reference. Anyway the official docs didnt give a multimaping one to many, the answer still is valid — J.Salas, Mar 23 '22 at 12:07

score 0 · Answer 2 · answered Jun 21 '23 at 11:25

This is how the associations are represented:

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

You use multi-mapping to populate both entities in the same way as previously, but this time, you use a grouping function on the result to combine the duplicate post instances and the tags:

using(var connection = new SqlConnection(connectionString))
{
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);
    }
}

Dapper one to many relationship mapping

2 Answers2