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?

Answer 1

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