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:

• One to One
• One to Many

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.

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
        sql,
        (invoice, invoiceDetail) =>
        {
            invoice.InvoiceDetail = invoiceDetail;
            return invoice;
        },
        splitOn: "InvoiceID")
    .Distinct()
    .ToList();

}

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.

string sql = "SELECT TOP 10 * FROM Orders AS A INNER JOIN OrderDetails AS B ON A.OrderID = B.OrderID;";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{			
var orderDictionary = new Dictionary<int, Order>();
var list = connection.Query&lt;Order, OrderDetail, Order&gt;(
    sql,
    (order, orderDetail) =&gt;
    {
        Order orderEntry;

        if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
        {
            orderEntry = order;
            orderEntry.OrderDetails = new List&lt;OrderDetail&gt;();
            orderDictionary.Add(orderEntry.OrderID, orderEntry);
        }

        orderEntry.OrderDetails.Add(orderDetail);
        return orderEntry;
    },
    splitOn: &quot;OrderDetailID&quot;)
.Distinct()
.ToList();

Console.WriteLine(&quot;Orders Count:&quot; + list.Count);

FiddleHelper.WriteTable(list);
FiddleHelper.WriteTable(list.First().OrderDetails);

}

Try it online