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.

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

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

Solution 3

I decided to use Dapper to get a big data for calculating somethings. This is my dapper extension method to join 3 table in _RepositoryBase.cs file.

    public List<Tuple<T, T2, T3, T4>> QueryMultiple<T2, T3, T4>(string sql, object param)
        where T2 : class
        where T3 : class
    {
        using (var con = new SqlConnection(GetConnStr()))
        {
            if (con.State == ConnectionState.Closed)
                con.Open();

            var query = con.Query<T, T2, T3, T4, Tuple<T, T2, T3, T4>>(sql, (t, t2, t3, t4) => Tuple.Create(t, t2, t3, t4), param);

            //if (query.Count() == 0)
            //    return new List<T>();

            var data = query.ToList();

            con.Close();
            con.Dispose();

            return data;
        }
    }

Then, this function will help you to get sql joined data via dapper.

    public List<Table1> GetSqlJoinedDataViaDaper(int customerId)
    {
        var repo = new GenericRepository<T_LOOKUP>();
        var sql = @"select table1.ID Table1ID, table1.*,
                    table2.ID Table2ID, table2.*,
                    table3.ID Table3ID, table3.*
                    from dbo.Table1 table1 (nolock)
                    left outer join dbo.Table2 table2 (nolock) on table2.ID=table1.FKTable2ID
                    left outer join dbo.Table3 table3 (nolock) on table3.ID=table1.FKTable3ID
                    where table1.CustomerID=@p1 ";

        var resultWithJoin = repo.QueryMultiple<Table1, Table2, Table3>(sql,
            new { p1 = 1, splitOn = "Table1ID,Table2ID,Table3ID" }).ToList();

        var retval = new List<Table1>();
        foreach (var item in resultWithJoin)
        {
            Table1 t1 = item.Item2; //After first split
            t1.Table2 = item.Item3; //After Table2ID split
            t1.Table3 = item.Item4; //After Table3ID split

            retval.Add(t1);
        }
        return retval;
    }

Summary: Write your select and insert split between tables. Say splits to Dapper and get your data. I used this and worked better than Entity Framework.

Comments

[Saadi]

OrderDetail is basically the Order Class

[Daniel Tshuva]

Ok, change it in question for other will understand

[johnny]

@DanielTshuva Can you tell me why Order is in the connection.Query command twice? Thanks.

[Daniel Tshuva]

@johnny - In the select query the first statement is to get Order properties (id, order_reference, order_status) -> This for first Order, the second Order is the OUT type. Note, that we query for 3 Select statements for 3 types, but in the connection.Query we provider 4 type -> the last Type is the out Type. Hope you understand.

[Wouter Vanherck]

I'm having issues with the QueryMultiple() function: The type or namespace name T could not be found. When I change it to List<Tuple<T, T2, T3>> QueryMultiple<T, T2, T3>(string sql, object param) it compiles, but the calling function does not execute as expected.

[hastrb]

This code will not work actually if we use the domain classes of OP. It is due to OrderLines and OrderLineSizes are null, hence, you will get NullReferenceException in the runtime on lines: orderDetail.OrderLines.Add(orderLine); and orderLine.OrderLineSizes.Add(ols);. Initialize each of them next line to lookup.Add and lookup2.Add, or simply use C#6 auto-property initializer on the class level.