C# – Map query results to multiple objects with Dapper

by

When you’re querying joined tables, you can map each row to multiple objects by using the multi mapping feature in Dapper.

To multi map, you have to provide Dapper with the following:

  • Which types to map to.
  • Which column(s) to split on. This tells Dapper which columns it should try to map to which type.
  • A mapping function where Dapper passes in the mapped objects and you can link them together.

In this article, I’ll show examples of multi mapping.

Note: If you don’t specify the split column, it’ll use the default of “Id”. I recommend always explicitly specifying the split column.

One-to-one relationship multi mapping

The Orders table has a one-to-one relationship with the Customers table, and they are linked by the CustomerId column:

Table relationship diagram: One-to-one relation between Orders and Customers

The following SQL query selects an order and the associated customer:

SELECT o.OrderId, o.[Status], c.CustomerId, c.[Name]                         
FROM Orders o
INNER JOIN Customers c
ON o.CustomerId = c.CustomerId
WHERE o.OrderId = @Id
Code language: SQL (Structured Query Language) (sql)

Here’s the results of the query:

OrderId	Status	CustomerId	Name
43672	New	30067		Corey LuoCode language: plaintext (plaintext)

To map these results to an Order and Customer object, use multi mapping and split on the CustomerId column:

using (var con = new SqlConnection(ConnectionString))
{
	return con.Query<Order, Customer, Order>(GET_SQL, 
		map: (order, customer) =>
		{
			order.Customer = customer;
			return order;
		},
		param: new { id },
		splitOn: "CustomerId").FirstOrDefault();
}
Code language: C# (cs)

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

For each row, it creates an Order object and a Customer object. It maps the columns to the objects based on the split column (CustomerId) like this:

  • Order columns = all columns to the left of CustomerId (OrderId, Status).
  • Customer columns = remaining columns (CustomerId, Name).

The end result is an Order object with a Customer object:

{
  "OrderId": 43659,
  "Customer": {
    "CustomerId": 29825,
    "Name": "Corey Luo"
  },
  "Status": "New"
}Code language: JSON / JSON with Comments (json)

Note: In all examples, I serialized the objects to JSON with indenting.

One-to-many relationship multi mapping

The Orders table has a one-to-many relationship with the OrderLines table, and they are linked by the OrderId column:

Table relationship diagram: One-to-many relation between Orders and OrderLines

The following SQL query selects orders and associated order lines:

SELECT o.OrderId, o.Status, ol.OrderLineId, ol.Product, ol.Quantity
FROM Orders o
INNER JOIN OrderLines ol
ON o.OrderId = ol.OrderId
WHERE o.OrderId IN @Ids
Code language: SQL (Structured Query Language) (sql)

Here are the query results (for a single order id):

OrderId	Status	OrderLineId	Product				Quantity
43672	New	126		Mountain Bike Socks, M		6
43672	New	127		Mountain-100 Black, 42		2
43672	New	128		Mountain-100 Silver, 48		1Code language: plaintext (plaintext)

To map these results to Order/OrderLine objects, multi map and split on the OrderLineId column. The map function is more complex in the one-to-many scenario.

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

using (var con = new SqlConnection(ConnectionString))
{
	con.Query<Order, OrderLine, Order>(GET_LINES_SQL,
		map: (order, orderLine) =>
		{
			orderLine.OrderId = order.OrderId; //non-reference back link

			//check if this order has been seen already
			if (orderMap.TryGetValue(order.OrderId, out Order existingOrder))
			{
				order = existingOrder;
			}
			else
			{
				order.Lines = new List<OrderLine>();
				orderMap.Add(order.OrderId, order);

			}

			order.Lines.Add(orderLine);
			return order;
		},
		splitOn: "OrderLineId",
		param: new { ids }
	);
}

return orderMap.Values;
Code language: C# (cs)

Query<Order, OrderLine, Order> means first map the columns to an Order object, then to an OrderLine object, and return IEnumerable<Order>.

For each row, it creates an Order object and an OrderLine object and maps the columns based on the split column (OrderLineId) like this:

  • Order columns = all columns to the left of OrderLineId (OrderId, Status).
  • OrderLine columns = remaining columns (OrderLineId, Product, Quantity).

It passes the mapped objects to the map function. Dapper maps the order columns to a new Order object for every row – which is why you need to de-dupe and keep track of unique Order objects by adding them to a dictionary.

This results in the following Order object with an array of OrderLine objects:

{
  "OrderId": 43672,
  "Lines": [
    {
      "OrderLineId": 126,
      "OrderId": 43672,
      "Product": "Mountain Bike Socks, M",
      "Quantity": 6
    },
    {
      "OrderLineId": 127,
      "OrderId": 43672,
      "Product": "Mountain-100 Black, 42",
      "Quantity": 2
    },
    {
      "OrderLineId": 128,
      "OrderId": 43672,
      "Product": "Mountain-100 Silver, 48",
      "Quantity": 1
    }
  ],
  "Status": "New"
}Code language: JSON / JSON with Comments (json)

Note: It seems inefficient that Dapper is mapping the order columns to new Order objects for each row. The alternative is to execute multiple queries – one for Orders and one for OrderLines – and then loop over the results and link them. Based on my testing, that has approximately the same performance as multi mapping.

Multi mapping to more than two objects

The Orders table has a one-to-one relationship with the Customers table and Stores table:

Table relationship diagram: One-to-many relationship between Orders/Customers and Orders/Stores

The following SQL query selects an order and associated customer and store:

SELECT o.OrderId, o.[Status], c.CustomerId, c.[Name], s.StoreId, s.[Location]
FROM Orders o
INNER JOIN Customers c
ON o.CustomerId = c.CustomerId
INNER JOIN Stores s
ON o.StoreId = s.StoreId
WHERE o.OrderId = @Id
Code language: SQL (Structured Query Language) (sql)

Here’s the results:

OrderId	Status	CustomerId	Name		StoreId	Location
43672	New	30067		Corey Luo	1	Main StCode language: plaintext (plaintext)

Here’s how to multi map these results to an Order/Customer/Store object:

using (var con = new SqlConnection(ConnectionString))
{
	return con.Query<Order, Customer, Store, Order>(GET_SQL,
		map: (order, customer, store) =>
		{
			order.Customer = customer;
			order.Store = store;
			return order;
		},
	param: new { id },
	splitOn: "CustomerId,StoreId").FirstOrDefault();
}
Code language: C# (cs)

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

When you’re mapping to more than two objects, you’ll need to specify multiple split columns with a comma-delimited string (“CustomerId,StoreId”). It maps the columns to the three objects based on these split columns (CustomerId and StoreId) like this:

  • Order columns = all columns to left of CustomerId (OrderId, Status).
  • Customer columns = remaining columns to the left of StoreId (CustomerId, Name).
  • Store columns = remaining columns (StoreId, Location).

Here is the resulting Order object with linked Customer/Store objects:

{
  "OrderId": 43659,
  "Customer": {
    "CustomerId": 29825,
    "Name": "Corey Luo"
  },
  "Status": "New",
  "Store": {
    "StoreId": 1,
    "Location": "Main St"
  }
}Code language: JSON / JSON with Comments (json)

Related Articles

Comments are closed.