Introduction

In Dapper, a one-to-many query usually means running a join and then rebuilding the object graph yourself. Dapper can map rows quickly, but it will not automatically understand that repeated parent data from a joined result set should collapse into one parent object with a child collection.

Example Shape

Suppose you have orders and items:

1public class Order
2{
3    public int Id { get; set; }
4    public string CustomerName { get; set; } = "";
5    public List<OrderItem> Items { get; set; } = new();
6}
7
8public class OrderItem
9{
10    public int ItemId { get; set; }
11    public string ProductName { get; set; } = "";
12}

And a SQL query like:

1SELECT
2    o.Id,
3    o.CustomerName,
4    i.ItemId,
5    i.ProductName
6FROM Orders o
7LEFT JOIN OrderItems i ON i.OrderId = o.Id;

This returns one row per order-item combination, which means the order columns repeat across rows.

Use a Lookup Dictionary

The standard Dapper pattern is to keep a dictionary keyed by the parent ID, then add children as rows are read:

1using Dapper;
2using System.Collections.Generic;
3using System.Data;
4using System.Linq;
5
6var sql = @"
7SELECT
8    o.Id,
9    o.CustomerName,
10    i.ItemId,
11    i.ProductName
12FROM Orders o
13LEFT JOIN OrderItems i ON i.OrderId = o.Id;";
14
15var lookup = new Dictionary<int, Order>();
16
17var orders = connection.Query<Order, OrderItem, Order>(
18    sql,
19    (order, item) =>
20    {
21        if (!lookup.TryGetValue(order.Id, out var existing))
22        {
23            existing = order;
24            existing.Items = new List<OrderItem>();
25            lookup.Add(existing.Id, existing);
26        }
27
28        if (item != null && item.ItemId != 0)
29        {
30            existing.Items.Add(item);
31        }
32
33        return existing;
34    },
35    splitOn: "ItemId"
36).Distinct().ToList();

This is the idiomatic one-to-many Dapper approach because it handles repeated parent rows without duplicating the parent object in memory.

Why splitOn Matters

splitOn tells Dapper where the columns for the second mapped object begin. If it is wrong, Dapper will map columns into the wrong object or fail in confusing ways.

In the example above, splitOn: "ItemId" means:

• columns before ItemId belong to Order
• 'ItemId and the columns after it belong to OrderItem'

That makes column order in the SQL query important.

Handle Missing Children Correctly

With a LEFT JOIN, orders with no items still appear, but the child columns are null. Your mapping logic should avoid adding empty child objects for those rows.

That is why the null or sentinel check in the mapping delegate matters. Without it, you may end up with one fake OrderItem in every otherwise-empty order.

Sometimes Two Queries Are Simpler

A single join query is common, but it is not always the best design. For some workloads, querying parents and children separately and composing them in memory is clearer:

• one query for orders
• one query for items by order IDs

That can be easier to reason about when the object graph gets larger than one parent and one child type.

Common Pitfalls

• Expecting Dapper to build one-to-many object graphs automatically with no lookup dictionary.
• Using the wrong splitOn column.
• Forgetting that joined parent rows repeat and then getting duplicate parent objects.
• Adding empty child objects for LEFT JOIN rows with no child data.
• Writing overly wide joins when two simpler queries would be clearer.

Summary

• Dapper one-to-many mapping usually means join rows plus a parent lookup dictionary.
• Use Query<TParent, TChild, TParent> with a mapping delegate.
• Set splitOn to the first column of the child object in the result set.
• Guard against empty child rows when using LEFT JOIN.
• Choose between one join and multiple simpler queries based on clarity and data shape.