Dapper - Result Multi-Mapping

Description

Extension methods can be used to execute a query and map the result to a strongly typed list with relations.

The relation can be either:

These extension methods can be called from any object of type IDbConnection.

Example - Query Multi-Mapping (One to One)

Query method can execute a query and map the result to a strongly typed list with a one to one relation.

  1. string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";
  3. using (var connection = My.ConnectionFactory())
  4. {
  5.     connection.Open();
  7.     var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
  8.             sql,
  9.             (invoice, invoiceDetail) =>
  10.             {
  11.                 invoice.InvoiceDetail = invoiceDetail;
  12.                 return invoice;
  13.             },
  14.             splitOn: "InvoiceID")
  15.         .Distinct()
  16.         .ToList();
  17. }

Example - Query Multi-Mapping (One to Many)

Query method can execute a query and map the result to a strongly typed list with a one to many relations.

  1. string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";
  3. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  4. {            
  5.     var orderDictionary = new Dictionary<int, Order>();
  8.     var list = connection.Query<Order, OrderDetail, Order>(
  9.         sql,
  10.         (order, orderDetail) =>
  11.         {
  12.             Order orderEntry;
  14.             if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
  15.             {
  16.                 orderEntry = order;
  17.                 orderEntry.OrderDetails = new List<OrderDetail>();
  18.                 orderDictionary.Add(orderEntry.OrderID, orderEntry);
  19.             }
  21.             orderEntry.OrderDetails.Add(orderDetail);
  22.             return orderEntry;
  23.         },
  24.         splitOn: "OrderDetailID")
  25.     .Distinct()
  26.     .ToList();
  28.     Console.WriteLine("Orders Count:" + list.Count);
  30.     FiddleHelper.WriteTable(list);
  31.     FiddleHelper.WriteTable(list.First().OrderDetails);
  32. }

Try it: .NET Core | .NET Framework