Dapper One-To-Many Multiple Tables

Today I was working on using Dapper to use a SELECT statement from an Order table but to JOIN to two other tables which were the Suppliers table and the OrderItems table.

The reason for this is to produce for a report list of all orders, the supplier and the total cost.

Link to article this is taken from

Table structure:

Code Full Snippet:

The code below is in a Repository for Orders and is returning an Order model.

public async Task<Order> GetOrderAsync(int orderId)
        {
            var query = @"SELECT * FROM Orders 
                INNER JOIN Suppliers ON Orders.SupplierId = Suppliers.Id 
                INNER JOIN OrderItems ON OrderItems.OrderId = @Id
                WHERE Orders.Id = @Id";

            var orderMap = new Dictionary<int, Order>();

            using (var connection = _context.CreateConnection())
            {
                await connection.QueryAsync<Order, Supplier, OrderItem, Order>(query,
                    (order, supplier, orderitem) =>
                    {
                        order.Supplier = supplier;

                        orderitem.OrderId = order.Id;

                        if (orderMap.TryGetValue(order.Id, out Order existingOrder))
                        {
                            order = existingOrder;
                        }
                        else
                        {
                            order.OrderItems = new List<OrderItem>();
                            orderMap.Add(order.Id, order);
                        }

                        order.OrderItems.Add(orderitem);
                        return order;
                    },
                    param: new { Id = orderId });

                //get the order object from the dictionary
                Order orderToReturn = new Order();
                orderToReturn = orderMap[orderId];
                return orderToReturn;
            }
        }

Order Model:

public class Order
    {
        public int Id { get; set; }
        public int SupplierId { get; set; }
        public Supplier Supplier { get; set; }
        public DateTime OrderDate { get; set; }  
        public string? Comments { get; set; }
        public List<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
    }

I will try break down the code mainly to help me ensure I understand it but feel free to correct me below in the comments or if there is a better way of doign what I am then again feel free to comment below.

var query = @"SELECT * FROM Orders 
                INNER JOIN Suppliers ON Orders.SupplierId = Suppliers.Id 
                INNER JOIN OrderItems ON OrderItems.OrderId = @Id
                WHERE Orders.Id = @Id";

This code is just a standard SELECT statement with INNER JOIN on a Suppliers and OrderItems table. The reason for the Suppliers join is in order to get the supplier and the OrderItems in order to get all items in the order. It then selects the correct order based on the Id passed.

 var orderMap = new Dictionary<int, Order>();

Here we are creating a new dictionary where int will be the order Id passed and then an Order object.

 await connection.QueryAsync<Order, Supplier, OrderItem, Order>(query,
                    (order, supplier, orderitem) =>

This code take from the link above just with object names modified:

Query means first map the columns to an Order object, then a Customer object, then a OrderItem object, and finally return IEnumerable.

order.Supplier = supplier;
orderitem.OrderId = order.Id;

Here I am simplying settign the Supplier for the order and the orderId for the OrderItem.

if (orderMap.TryGetValue(order.Id, out Order existingOrder))
{
    order = existingOrder;
}
else
{
    order.OrderItems = new List<OrderItem>();
    orderMap.Add(order.Id, order);
}

So here I am checking in the dictionary if the Order exists as dapper will map the OrderItems columns to a new Order object for each row it finds therefore we need to de-dupe to keep track of the unique order otherwise if you have three OrderItems it will create three entries in the dictionary one for each OrderItem rather than have one Order with a list of OrderItems.

So if the order exists then it simply saves as the existingOrder.

If it does not exist then it creates a new list of OrderItem and maps to the order in the dictionary.

I am not sure I have this part 100% correct so feel free to correct me. I am going to go through it in more detail to try to ensure fully understand it.

order.OrderItems.Add(orderitem);
 return order;

Here thecode is adding the orderitem to the list of OrderItems in the order and then returns the order from it.

param: new { Id = orderId });

Here we are adding a parameter for the order Id in order to get the correct order. This bit when I was doing the code I missed out and if you do then it will error stating you must declare the scalar variable. This took me a few hours to figure out so it seems what looks easy is often what gives you the most headaches.

Order orderToReturn = new Order();
orderToReturn = orderMap[orderId];
return orderToReturn;

This section of code takes the entry in the dictionary as there should only be one but you could error check for this. As the dictionary wants to return a list I cannot return from the method just a single Order without putting the dciotnary value into an Order object then returning this.

Result

{
  "id": 166,
  "supplierId": 2,
  "supplierName": "NCS",
  "orderDate": "2021-09-26T00:00:00",
  "comments": null,
  "orderItems": [
    {
      "id": 655,
      "description": "quam. Pellentesque habitant morbi tristique",
      "qty": 50,
      "price": 29.99,
      "totalPrice": 1499.5
    },
    {
      "id": 1151,
      "description": "dis parturient montes, nascetur ridiculus",
      "qty": 2,
      "price": 38,
      "totalPrice": 76
    },
    {
      "id": 1824,
      "description": "Does this add and update the order page.",
      "qty": 5000,
      "price": 9.99,
      "totalPrice": 49950
    },
    {
      "id": 1825,
      "description": "Does this add and update the order page.  Does this work.",
      "qty": 5000,
      "price": 9.99,
      "totalPrice": 49950
    },
    {
      "id": 1826,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1827,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1828,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1829,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1830,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1831,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1832,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1833,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1834,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1835,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1836,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1837,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1838,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1839,
      "description": "Does this now work?",
      "qty": 55,
      "price": 1.99,
      "totalPrice": 109.45
    },
    {
      "id": 1854,
      "description": "Testing toast",
      "qty": 5,
      "price": 1.99,
      "totalPrice": 9.95
    },
    {
      "id": 1859,
      "description": "Item god knows",
      "qty": 5,
      "price": 3.99,
      "totalPrice": 19.95
    },
    {
      "id": 1860,
      "description": "Another",
      "qty": 5,
      "price": 2.99,
      "totalPrice": 14.95
    },
    {
      "id": 1861,
      "description": "hh",
      "qty": 5,
      "price": 3.99,
      "totalPrice": 19.95
    }
  ],
  "orderCode": "IT-CRAIG-0166"
}

The output is a little different to the models above as I have a mapping to a different model as below:

public class OrderAndItemsDto
{
    public int Id { get; set; }
    public int SupplierId { get; set; }
    public string SupplierName { get; set; }
    public DateTime OrderDate { get; set; }
    public string? Comments { get; set; }
    public List<OrderItemDto> OrderItems { get; set; } = new List<OrderItemDto>();

    public string OrderCode
    {
        get
        {
            return $"IT-CRAIG-{Id.ToString("0000")}";
        }
    }
}