Dapper map multiple joins Sql Query

Question

I want to map complex object to dapper result from query which has two inner joins. I know we've solution to map one inner join but I want to map two inner joins result.

Here is the Scenario:

My Classes are:

public class Order 
{
    public int id { get; set; }
    public string order_reference { get; set; }
    public string order_status { get; set; }
    public List<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
    public int id { get; set; }
    public int order_id { get; set; }
    public string product_number { get; set; }
    public List<OrderLineSize> OrderLineSizes { get; set; }
}

public class OrderLineSize
{
    public int id { get; set; }
    public int order_line_id { get; set; }
    public string size_name { get; set; }
}

Order has OrderLines as List and OrderLine as OrderLineSizes as List.

Now, Here is my query base on that I want to populate List<Order> with correct data:

SELECT *
FROM orders_mstr o
INNER JOIN order_lines ol ON o.id = ol.order_id
INNER JOIN order_line_size_relations ols ON ol.id = ols.order_line_id

Here is what I tried so far:

var lookup = new Dictionary<int, Order>();
            connection.Query<Order, OrderLine, Order>(@"
                    SELECT o.*, ol.*
                    FROM orders_mstr o
                    INNER JOIN order_lines ol ON o.id = ol.order_id                    
                    ", (o, ol) => {
                    Order orderDetail;
                    if (!lookup.TryGetValue(o.id, out orderDetail))
                    {
                        lookup.Add(o.id, orderDetail = o);
                    }
                    if (orderDetail.OrderLines == null)
                        orderDetail.OrderLines = new List<OrderLine>();
                    orderDetail.OrderLines.Add(ol);
                    return orderDetail;
                }).AsQueryable();

            var resultList = lookup.Values;

Using this, I can successfully map order object with OrderLine but I want to populate OrderLineSizes as well with correct data.

Answer 1

I tried my best and solve it.

Here is the more easy and accurate solution as per me.:

var lookup = new Dictionary<int, OrderDetail>();
            var lookup2 = new Dictionary<int, OrderLine>();
            connection.Query<OrderDetail, OrderLine, OrderLineSize, OrderDetail>(@"
                    SELECT o.*, ol.*, ols.*
                    FROM orders_mstr o
                    INNER JOIN order_lines ol ON o.id = ol.order_id
                    INNER JOIN order_line_size_relations ols ON ol.id = ols.order_line_id           
                    ", (o, ol, ols) =>
            {
                OrderDetail orderDetail;
                if (!lookup.TryGetValue(o.id, out orderDetail))
                {
                    lookup.Add(o.id, orderDetail = o);
                }
                OrderLine orderLine;
                if (!lookup2.TryGetValue(ol.id, out orderLine))
                {
                    lookup2.Add(ol.id, orderLine = ol);
                    orderDetail.OrderLines.Add(orderLine);
                }
                orderLine.OrderLineSizes.Add(ols);
                return orderDetail;
            }).AsQueryable();

            var resultList = lookup.Values.ToList();