Map to nested object using Dapper

Question

I have an object that has 2 lists of classes.

public class Order
{
    int order_id;
    double amount;
    List<order_item> order_items;
    List<order_shipment> order_shipments;
}

And here's how the objects should be mapped:

SELECT * FROM Orders o
INNER JOIN Order_Items i ON i.order_id = o.order_id
INNER JOIN Order_Shipments s ON s.order_id = o.order_id

What's a way to map the above query to the nested class?

Have you looked at dapper multi-mapper? – iikkoo Oct 11 '20 at 15:07 — iikkoo, Oct 11 '20 at 15:07
Is that a separate library? I'm not familiar with it – Joe Defill Oct 11 '20 at 15:16 — Joe Defill, Oct 11 '20 at 15:16

Selim Yildiz · Answer 1 · 2020-10-12T09:50:10.280

Give it a go:

var lookup = new Dictionary<int, Order>();
conn.Query<Order, order_item, order_shipment, Order>(@"
        SELECT o.*,i.*,s.* FROM Orders o
        INNER JOIN Order_Items i ON i.order_id = o.order_id
        INNER JOIN Order_Shipments s ON s.order_id = o.order_id 
    ", (o, i, s) => {
        Order order;
        
        if (!lookup.TryGetValue(o.order_id, out order))
              lookup.Add(o.order_id, order = o);
        
        order.order_items = order.order_items ?? new List<order_item>();
        order.order_items.Add(i);
         
        order.order_shipments = order.order_shipments ?? new List<order_shipment>();
        order.order_shipments.Add(s);
        
        return order;
    }).AsQueryable();
    
var result = lookup.Values;

See: How do I map lists of nested objects with Dapper

Update:

Thanks, @Palle Due for pointing out. You should also add splitOn parameter based on first column of tables as follows:(Assuming first column of Order_Items is order_item_id and Order_Shipment is order_shipment_id)

 splitOn: "order_id, order_item_id, order_shipment_id"

See: Correct use of Multimapping in Dapper

Aren't you missing `splitOn: "order_id"`? The default is to split on columns named `id`, so `Query` wouldn't know how to separate. — Palle Due, Oct 12 '20 at 08:11
Sorry, but I could not understand exactly what you have said. Could you elaborate it a bit, please? — Selim Yildiz, Oct 12 '20 at 08:18
The `Query` needs some help splitting the result data into Orders, order_items and order_shipments. You do this by adding the `splitOn` parameter stating which columns make up the split. You haven't added that parameter, which is fine, but only if all split columns are named `id`. See this link: https://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper — Palle Due, Oct 12 '20 at 09:07

Map to nested object using Dapper

1 Answers1