Dapper三表关联查询：如何将OrderDetails按Order分组（替代LINQ）

方案1：手动遍历结果集构建对象树 #

利用Dapper的ExecuteReader逐行读取数据，通过字典跟踪已创建的对象，避免重复实例化并完成关联：

var customerDict = new Dictionary<int, Customer>();
var orderDict = new Dictionary<int, Order>();

using (var connection = new SqlConnection(connectionString))
{
    var sql = @"
        SELECT c.*, o.*, od.*
        FROM Customer c
        JOIN [Order] o ON c.CustomerId = o.CustomerId
        JOIN OrderDetail od ON o.OrderId = od.OrderId";

    var reader = connection.ExecuteReader(sql);
    while (reader.Read())
    {
        // 处理Customer
        var customerId = reader.GetInt32(reader.GetOrdinal("CustomerId"));
        if (!customerDict.TryGetValue(customerId, out var customer))
        {
            customer = new Customer
            {
                CustomerId = customerId,
                Name = reader.GetString(reader.GetOrdinal("Name")),
                // 填充其他Customer属性
                Orders = new List<Order>()
            };
            customerDict.Add(customerId, customer);
        }

        // 处理Order
        var orderId = reader.GetInt32(reader.GetOrdinal("OrderId"));
        if (!orderDict.TryGetValue(orderId, out var order))
        {
            order = new Order
            {
                OrderId = orderId,
                OrderDate = reader.GetDateTime(reader.GetOrdinal("OrderDate")),
                // 填充其他Order属性
                OrderDetails = new List<OrderDetail>()
            };
            customer.Orders.Add(order);
            orderDict.Add(orderId, order);
        }

        // 处理OrderDetail并关联到对应Order
        var orderDetail = new OrderDetail
        {
            OrderDetailId = reader.GetInt32(reader.GetOrdinal("OrderDetailId")),
            ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
            Quantity = reader.GetInt32(reader.GetOrdinal("Quantity")),
            // 填充其他OrderDetail属性
        };
        order.OrderDetails.Add(orderDetail);
    }
}

var finalResult = customerDict.Values.ToList();

方案2：拆分多次查询并手动映射 #

避免关联查询产生的重复数据，分三次查询后通过字典建立对象间的关联：

List<Customer> customers;
Dictionary<int, List<Order>> customerOrderMap = new Dictionary<int, List<Order>>();
Dictionary<int, List<OrderDetail>> orderDetailMap = new Dictionary<int, List<OrderDetail>>();

using (var connection = new SqlConnection(connectionString))
{
    // 查询所有Customer
    customers = connection.Query<Customer>("SELECT * FROM Customer").ToList();

    // 查询所有Order并按CustomerId分组
    var allOrders = connection.Query<Order>("SELECT * FROM [Order]").ToList();
    foreach (var order in allOrders)
    {
        if (!customerOrderMap.ContainsKey(order.CustomerId))
        {
            customerOrderMap[order.CustomerId] = new List<Order>();
        }
        customerOrderMap[order.CustomerId].Add(order);
    }

    // 查询所有OrderDetail并按OrderId分组
    var allDetails = connection.Query<OrderDetail>("SELECT * FROM OrderDetail").ToList();
    foreach (var detail in allDetails)
    {
        if (!orderDetailMap.ContainsKey(detail.OrderId))
        {
            orderDetailMap[detail.OrderId] = new List<OrderDetail>();
        }
        orderDetailMap[detail.OrderId].Add(detail);
    }
}

// 完成对象关联
foreach (var customer in customers)
{
    if (customerOrderMap.TryGetValue(customer.CustomerId, out var orders))
    {
        customer.Orders = orders;
        foreach (var order in orders)
        {
            if (orderDetailMap.TryGetValue(order.OrderId, out var details))
            {
                order.OrderDetails = details;
            }
        }
    }
}

方案3：使用Dapper多结果集查询 #

通过QueryMultiple一次执行多个SQL语句，分别获取三个实体的结果集后手动关联：

using (var connection = new SqlConnection(connectionString))
{
    var sql = @"
        SELECT * FROM Customer;
        SELECT * FROM [Order];
        SELECT * FROM OrderDetail;";

    using (var multiResult = connection.QueryMultiple(sql))
    {
        var customers = multiResult.Read<Customer>().ToList();
        var orders = multiResult.Read<Order>().ToList();
        var details = multiResult.Read<OrderDetail>().ToList();

        // 建立Order与Customer的关联
        var orderLookup = new Dictionary<int, Order>();
        foreach (var order in orders)
        {
            orderLookup[order.OrderId] = order;
            var targetCustomer = customers.FirstOrDefault(c => c.CustomerId == order.CustomerId);
            if (targetCustomer != null)
            {
                targetCustomer.Orders ??= new List<Order>();
                targetCustomer.Orders.Add(order);
            }
        }

        // 建立OrderDetail与Order的关联
        foreach (var detail in details)
        {
            if (orderLookup.TryGetValue(detail.OrderId, out var targetOrder))
            {
                targetOrder.OrderDetails ??= new List<OrderDetail>();
                targetOrder.OrderDetails.Add(detail);
            }
        }
    }
}

内容的提问来源于stack exchange，提问作者Zakari