Skip to content

Map parent child using multiple result sets

Cleve Littlefield edited this page Feb 1, 2016 · 1 revision

Assuming we have two Poco models:

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public List<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class OrderItem
{
    public int Id { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

And a two map definitions like this:

var orderMapDefiniton = new MapDefiniton<Order>();
orderMapDefinition.MapType();

var orderItemMapDefiniton = new MapDefiniton<OrderItem>();
orderItemMapDefinition.MapType();

Execute and map reader (in this example we will call a stored procedure that returns two result sets).

using (var connection = new SqlConnection("connectionstring"))
{
    using (var command = new SqlCommand("spGetOrders", connection))
    {
        await connection.OpenAsync();
        using (var reader = command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
        {
            var orderMap = orderMapDefinition.CreateMap();
            var orderItemMap = orderItemMapDefinition.CreateMap();
            orderMap .LoadOrdinals(reader);
                            
            var orders = new Dictionary<int, Order>();
            while (await reader.ReadAsync())
            {
                var order = new Order();
                orderMap.Load(order, reader);
                orders[order.Id] = order;
            }
   
            await reader.NextResultAsync();
            int orderIdOrdinal = reader.GetOrdinal("OrderId");
            orderItemMap.LoadOrdinals(reader);
            while (await reader.ReadAsync())
            {
                var orderItem = new OrderItem();
                orderItemMap.Load(orderItem, reader);
                int orderId = reader.GetInt32(orderIdOrdinal);
                orders[orderId].Items.Add(orderItem);
            }

            return orders.Values;
        }
    } 
}

Clone this wiki locally