-
Notifications
You must be signed in to change notification settings - Fork 1
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;
}
}
}