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

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:

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:

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.