Dapper映射多个联接SQL查询 [英] Dapper map multiple joins Sql Query

问题描述

我想将复杂的对象映射到具有两个内部联接的查询的精简结果.我知道我们已经找到了映射一个内部联接的解决方案，但是我想映射两个内部联接的结果.

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.

这是场景:

我的课程是:

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将OrderLines作为列表，并将OrderLine作为OrderLineSizes作为列表.

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

现在，这是我要使用正确的数据填充 List< Order> 的依据:

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;

使用此方法，我可以成功地使用OrderLine映射订单对象，但是我也想用正确的数据填充OrderLineSizes.

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

推荐答案

我已尽力解决了该问题.

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

这篇关于Dapper映射多个联接SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！