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();

Answer 2

I don't know what is class 'OrderDetail' you don't provide it so I used Order class.
This can also be done by QueryMultiple but because your question includes INNER JOIN I don't use it.

public Dictionary<int, Order> GetOrderLookup()
{
    var lookup = new Dictionary<int, Order>();

    const string sql = @"   SELECT  o.id,
                                    o.order_reference,
                                    o.order_status,

                                    ol.id,
                                    ol.order_id,
                                    ol.product_number,

                                    ols.id,
                                    ols.order_line_id,
                                    ols.size_name
                            FROM    orders_mstr o
                            JOIN    order_lines ol ON o.id = ol.order_id
                            JOIN    order_line_size_relations ols ON ol.id = ols.order_line_id";

    List<Order> orders = null;
    using (var connection = OpenConnection(_connectionString))
    {
        orders = connection.Query<Order, OrderLine, OrderLineSize, Order>(sql, (order, orderLine, orderLizeSize) =>
        {
            orderLine.OrderLineSizes = new List<OrderLineSize> { orderLizeSize };
            order.OrderLines = new List<OrderLine>() { orderLine };
            return order;
        },
        null, commandType: CommandType.Text).ToList();
    }

    if (orders == null || orders.Count == 0)
    {
        return lookup;
    }

    foreach (var order in orders)
    {
        var contians = lookup.ContainsKey(order.id);
        if (contians)
        {
            var newLinesToAdd = new List<OrderLine>();
            var existsLines = lookup[order.id].OrderLines;
            foreach (var existsLine in existsLines)
            {
                foreach (var newLine in order.OrderLines)
                {
                    if (existsLine.id == newLine.id)
                    {
                        existsLine.OrderLineSizes.AddRange(newLine.OrderLineSizes);
                    }
                    else
                    {
                        newLinesToAdd.Add(newLine);
                    }
                }
            }
            existsLines.AddRange(newLinesToAdd);
        }
        else
        {
            lookup.Add(order.id, order);
        }
    }

    return lookup;
}