I'm struggling with a query that returns a parent row and a variable number of child rows in a single flat result set. Dapper is great for simple 1:1 mappings, but here I need to group children under their parent dynamically.
For example, a Departments table joined to Employees where each department has zero or more employees. I don't want to run N+1 queries. My first thought was to use multiple result sets, but my SQL only returns one.
What patterns do people use? I've seen references to custom multi-mapping with Query<T, U, T>, but the syntax looks intimidating. Is there a cleaner way, perhaps by splitting the flat list in memory after a single query?
If someone could share a minimal working example of splitting a flat Dapper result into a parent-child hierarchy, that would save me hours of trial and error.
Dapper maps columns to properties very well, but it does not automatically turn joined rows into a complete nested object graph. For nested objects, use Dapper multi-mapping; for a one-to-many relationship, collect the repeated parent rows yourself.
Here’s a common example: each order has one customer and may have several order lines. The selected columns are grouped by type, and
splitOntells Dapper where each type’s columns begin.The
splitOn: "Id,Id"value matches the two boundaries in the selected columns: the customer’sId, then the line’sId. Keep the column order and split points in sync; if your schema uses different names, specify those names instead.This pattern works well for one collection. If you join multiple collections at once—say, lines and payments—the database can return a multiplied number of rows, and you may accidentally add duplicates. In that case, separate multiple result sets with Dapper’s
QueryMultiplecan be easier to reason about. Nested collection mapping is usually an aggregation task rather than something a single automatic mapping call can infer.Dapper maps rows very well, but a SQL join that returns repeated parent rows won’t automatically turn those rows into a nested object graph. For a one-to-many result, use multi-mapping and collect each parent and child in dictionaries as the rows come back.
Here’s a customer → orders → order lines example. The order of the selected columns matters: each
splitOnvalue marks the first column for the next mapped type.For more than one parent, key the customer dictionary by customer ID as above. If you join multiple collections at once—for example, orders and addresses—the SQL can produce a Cartesian product, repeating each combination of child rows. Deduplicate each collection by its own key, or consider loading the collections separately with
QueryMultiple. For small results, JSON columns can also be a reasonable option when the database can produce the nested shape you need.The key idea is that
Query<TFirst, TSecond, TReturn>maps row segments; your callback decides how those mapped pieces become a single nested result.