multiple joins mapping c#
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;
}