How to Map query results to multiple objects with Dapper in C#

By Tan Lee Published on Mar 12, 2025 373

When dealing with SQL queries that join multiple tables, Dapper's multi-mapping feature allows you to map the results to multiple objects. This can be really handy when you're pulling related data in a single query.

Multi Mapping in Dapper

To set up multi-mapping, you need to define the following:

The types to map to - Specify the classes to map the results to.
The split column(s) - Tell Dapper how to split the columns across the different objects.
A mapping function - This is where you link the mapped objects together after they are created.

In this article, I’ll show how multi-mapping works using an example with a Products and Categories table.

One-to-One Relationship Multi Mapping

Consider a Products table and a Categories table, where each product is linked to one category via the CategoryId column:

Table relationship: One-to-one between Products and Categories.

SQL query:

        
              SELECT p.ProductId, p.Name, c.CategoryId, c.CategoryName
FROM Products p
INNER JOIN Categories c
ON p.CategoryId = c.CategoryId
WHERE p.ProductId = @Id

Sample query result:

ProductId	Name	CategoryId	CategoryName
12345	"Smartphone"	1	"Electronics"

Mapping the Result to Objects

We can map this result to a Product and Category object using multi-mapping.

        
              using (var con = new SqlConnection(ConnectionString))
{
    return con.Query<Product, Category, Product>(GET_SQL, 
        map: (product, category) =>
        {
            product.Category = category;
            return product;
        },
        param: new { id },
        splitOn: "CategoryId").FirstOrDefault();
}

In this example:

Query<Product, Category, Product> tells Dapper to map the columns to the Product object first, then to the Category object, and return an IEnumerable<Product>.
The split column is CategoryId, so Dapper knows to map the columns left of CategoryId to Product and the remaining columns to Category.

Resulting object:

        
              {
  "ProductId": 12345,
  "Name": "Smartphone",
  "Category": {
    "CategoryId": 1,
    "CategoryName": "Electronics"
  }
}

One-to-Many Relationship Multi Mapping

Now, let’s say we have a Products table and an OrderItems table, where each product can be part of multiple order items. This forms a one-to-many relationship.

Table relationship: One-to-many between Products and OrderItems.

SQL query:

        
              SELECT p.ProductId, p.Name, oi.OrderItemId, oi.Quantity
FROM Products p
INNER JOIN OrderItems oi
ON p.ProductId = oi.ProductId
WHERE p.ProductId IN @Ids

Sample query result for a product:

ProductId	Name	OrderItemId	Quantity
12345	"Smartphone"	789	2
12345	"Smartphone"	790	1

Mapping the Result to Objects

In the one-to-many scenario, we need to ensure that multiple OrderItem objects are added to a single Product.

        
          
          
              
              var productMap = new Dictionary<int, Product>();
 
using (var con = new SqlConnection(ConnectionString))
{
    con.Query<Product, OrderItem, Product>(GET_ITEMS_SQL,
        map: (product, orderItem) =>
        {
            orderItem.ProductId = product.ProductId; // Linking OrderItem to Product
 
            if (productMap.TryGetValue(product.ProductId, out Product existingProduct))
            {
                product = existingProduct; // Use existing product
            }
            else
            {
                product.OrderItems = new List<OrderItem>();
                productMap.Add(product.ProductId, product); // Store the product
            }
 
            product.OrderItems.Add(orderItem);
            return product;
        },
        splitOn: "OrderItemId",
        param: new { ids }
    );
}
 
return productMap.Values;

            

        
      

In this example:

Query<Product, OrderItem, Product> tells Dapper to first map the columns to a Product object, then to an OrderItem object.
The split column is OrderItemId, so columns to the left (like ProductId and Name) map to Product, and the remaining columns (like OrderItemId and Quantity) map to OrderItem.
We use a dictionary (productMap) to ensure that each product is only mapped once, and its associated order items are added to the OrderItems list.

Resulting object:

        
          
          
              
              {
  "ProductId": 12345,
  "Name": "Smartphone",
  "OrderItems": [
    {
      "OrderItemId": 789,
      "ProductId": 12345,
      "Quantity": 2
    },
    {
      "OrderItemId": 790,
      "ProductId": 12345,
      "Quantity": 1
    }
  ]
}

            

        
      

Multi Mapping to More Than Two Objects

Finally, let's say we want to map the results of a query that pulls data from Products, Categories, and Suppliers tables. The relationships are as follows:

One-to-one between Products and Categories.
One-to-many between Products and Suppliers.

Table relationship: One-to-one between Products and Categories, and one-to-many between Products and Suppliers.

SQL query:

        
              SELECT p.ProductId, p.Name, c.CategoryId, c.CategoryName, s.SupplierId, s.SupplierName
FROM Products p
INNER JOIN Categories c
ON p.CategoryId = c.CategoryId
INNER JOIN Suppliers s
ON p.SupplierId = s.SupplierId
WHERE p.ProductId = @Id

Sample query result:

ProductId	Name	CategoryId	CategoryName	SupplierId	SupplierName
12345	"Smartphone"	1	"Electronics"	10	"TechCorp"

Mapping the Result to Objects

Here’s how to map the results to Product, Category, and Supplier objects:

        
              using (var con = new SqlConnection(ConnectionString))
{
    return con.Query<Product, Category, Supplier, Product>(GET_SQL,
        map: (product, category, supplier) =>
        {
            product.Category = category;
            product.Supplier = supplier;
            return product;
        },
        param: new { id },
        splitOn: "CategoryId,SupplierId").FirstOrDefault();
}

In this example:

Query<Product, Category, Supplier, Product> tells Dapper to map the columns first to Product, then to Category, and finally to Supplier.
The split columns are CategoryId and SupplierId, so Dapper knows how to map the columns to the correct objects.

Resulting object:

        
              {
  "ProductId": 12345,
  "Name": "Smartphone",
  "Category": {
    "CategoryId": 1,
    "CategoryName": "Electronics"
  },
  "Supplier": {
    "SupplierId": 10,
    "SupplierName": "TechCorp"
  }
}

This example covers how to perform multi-mapping with Dapper for various types of relationships, ranging from one-to-one to one-to-many and even multi-mapping with more than two objects.

Tan Lee