Dapper selecting nested objects

Question

My SQL looks like

Select TOP 3 o.OrderId,
o.Total,
od.Quantity,
od.ItemDescription,
os.OrderStatusDescription,
os.OrderStatusID
 From dbo.Orders o
 INNER JOIN dbo.OrderStatuses os ON os.OrderStatusID = o.OrderStatusID
 INNER JOIN dbo.OrderDetails od ON od.OrderID = o.OrderID
  Where o.CustomerId = 100 order by OrderDate desc;

I have my class on c# side as below, i have three tables on db side as well. i am trying to see if i can use dapper to map 3 tables into one object Order withouty having to do multiple select querys. i need top 3 rows from Order Table and its child tables loaded. OrderDetail can have n rows for 1 orderId.

How can i selected nested object with 1 look up using dapper?

 class Order{
      int OrderId {get;set;}      
      decimal Total {get;set;}
      List<OrderDetail> Details {get;set;}
      OrderStatus Status {get;set;}
 }

  class OrderDetail
  {
    int OrderLineId {get;set;}
    int OrderId {get;set;}  
    string ItemDescription {get;set;}
    int Quantity {get;set;}
  }

  class OrderStatus
  {
    int OrderStatusID {get;set;}
    string OrderStatusDescription {get;set;}
  }

this is what i have tried so far but not successful

  sqlConnection.Open();

IEnumerable orders = sqlConnection
    .Query<Order>(
        @"select Order.*, OrderDetail.* 
          from Order join OrderDetail 
               inner join on Order.OrderId = OrderDetail.OrderId
               inner join on Order.OrderStatusID = OrderStatus.OrderId
               and Order.CustomerId = 100",
        (o, od, os) =>
            {
                o.Details = od;  //this is a List<OrderDetail>
                o.Status = os;
                return o;
            }); //

@DavidL i am getting syntax error saying not convert lambda expression to type 'object' because it is not a delegate type. — Justin Homes, May 02 '16 at 15:10
This question is not a duplicate of yours but it contains a partial solution to your problem http://stackoverflow.com/questions/11042618/dapper-multi-mapping-issue, while the List point is explained here https://stackoverflow.com/questions/7508322/how-do-i-map-lists-of-nested-objects-with-dapper?rq=1 — Steve, May 02 '16 at 15:16
@Steve i did notice that but example only have with 2 tables join , i have 3 and did not see any example for it — Justin Homes, May 02 '16 at 15:41
The first link has three tables in join and explain how to prepare correctly your query, the second example shows how to fill your `List`. I am sorry but at the moment cannot try to build an answer putting together the pieces — Steve, May 02 '16 at 15:44
@Steve If i want to get TOP 3 items from Order Table and then get all related child tables of Order, is that possible in dapper in one query lookup? Currently doing Top3 will give me top 3 of the joined result which can be same OrderID 3 times with different nested objects. — Justin Homes, May 02 '16 at 20:52
I see, but I am not aware of anything that you could do with Dapper. It would be useful to stop the Query execution but I don't know if it is possible. Instead you could search if there is some way using DISTINCT — Steve, May 02 '16 at 21:55

Edward N · Answer 1 · 2016-05-12T06:01:09.870

There are some ways to achieve your case, but for me you can do this by the below code

var sql = @"Select TOP 3 o.OrderId,
    o.Total,
    os.OrderStatusID,
    os.OrderStatusDescription,
    od.OrderLineId,
    od.Quantity,
    od.ItemDescription       
    From dbo.Orders o
INNER JOIN dbo.OrderStatuses os ON os.OrderStatusID = o.OrderStatusID
INNER JOIN dbo.OrderDetails od ON od.OrderID = o.OrderID
Where o.CustomerId = 100 order by OrderDate desc;";


sqlConnection.Open();

var lookupOrders = new Dictionary<int,Order>();
var oders = sqlConnection.Query<Order, OrderStatus, OrderDetails, Order>(sql,
               (o, os, od) => 
               {
                    Order order;
                    if(!lookupOrders.ContainsKey(o.OrderId)){
                        lookupOrders.Add(o.OrderId, o);                            
                        order = o;
                        order.Details = new List<OrderDetail>();
                    }
                    else {
                        order = lookupOrders[o.OrderId];
                    }
                    order.Details.Add(od);
                    order.Status = os;
                    return order;
               }, splitOn: "OrderId, OrderStatusID, OrderLineId"
            ).Distinct();

Dapper selecting nested objects

1 Answers1

Linked