Mastering Dapper Relationship Mappings: A Friendly Guide

One thali, many small bowls

Picture a steel thali at a South Indian meal. It arrives as one plate, but on that one plate sit several small bowls (katoris): one for sambar, one for rasam, one for curd, one for sweet.

The waiter brings it all in a single trip. But when you eat, your brain still keeps the bowls separate. You do not pour curd into the sambar. You see one plate, yet you understand it as many small parts.

Dapper relationship mapping works the same way. The database sends back one wide row. That row holds columns from many tables joined together. Your job is to take that one plate and gently sort it into separate bowls: a Post object here, an Author object there, then place the author into the post.

This sorting is called multi-mapping. By the end of this guide you will know how to do it for one-to-one, one-to-many, and many-to-many relationships, and you will understand the small word that controls it all: splitOn.

A quick word on what Dapper is

Dapper is a tiny library that sits on top of plain ADO.NET. You write the SQL yourself. Dapper runs it and turns the rows into your C# objects. That is mostly it.

Because it does so little extra, it is very fast. It does not track changes for you, and it does not build a model of your tables. So when two tables are joined, Dapper does not magically know that a Post belongs to an Author. You have to tell it. That telling is what this guide is about.

Here are the small classes we will use all the way through.

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}
 
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public int AuthorId { get; set; }
    public Author Author { get; set; } = new();
    public List<Comment> Comments { get; set; } = new();
}
 
public class Comment
{
    public int Id { get; set; }
    public string Text { get; set; } = "";
    public int PostId { get; set; }
}

How a JOIN becomes one wide row

When you JOIN two tables, the database glues their columns side by side. One result row carries columns from both tables.

Say you run this:

SELECT p.Id, p.Title, p.AuthorId, a.Id, a.Name
FROM Posts p
JOIN Authors a ON a.Id = p.AuthorId;

One row might look like the table below. Notice there are two Id columns, one for the post and one for the author.

Dapper sees this flat list of columns. It does not know on its own that the last two belong to a different object. We have to draw a line in the middle. That line is splitOn.

Mapping one-to-one (a Post and its Author)

Let us map each Post to its single Author. We use the QueryAsync overload that takes two types plus a small mapping function.

using Dapper;
 
const string sql = @"
    SELECT p.Id, p.Title, p.AuthorId, a.Id, a.Name
    FROM Posts p
    JOIN Authors a ON a.Id = p.AuthorId";
 
var posts = await connection.QueryAsync<Post, Author, Post>(
    sql,
    (post, author) =>
    {
        post.Author = author;   // attach the author to the post
        return post;            // return the finished post
    },
    splitOn: "Id");
 
var list = posts.ToList();

Read the generic types as a recipe: <Post, Author, Post> means "build a Post, build an Author, and give me back a Post". The function in the middle receives the two built objects. You connect them and return the one you care about.

Why splitOn matters here

splitOn: "Id" says: "start a new object every time you see a column named Id." In our SELECT, the columns are Id, Title, AuthorId, Id, Name. The second Id (the author's) is where Dapper splits. Everything before it becomes the Post. Everything from that Id onward becomes the Author.

The default value of splitOn is already "Id", so in this case you could even leave it out. But being explicit is a good habit. The moment your key columns have other names, you must set it yourself.

A common, painful mistake: you forget splitOn, Dapper splits on the first Id, and your Author ends up holding the post's columns. The build fails or the data looks wrong. When mapping feels broken, check splitOn first.

Mapping one-to-many (a Post and its Comments)

This is where people get stuck, so go slowly.

A JOIN from Posts to Comments repeats the post once for every comment. A post with three comments shows up in three rows. If we are not careful, we will create the same post three times.

We want one Post with three Comment objects inside it. The trick is a Dictionary. We key it by the post's Id. For each row, we check: have we seen this post before?

const string sql = @"
    SELECT p.Id, p.Title, p.AuthorId, c.Id, c.Text, c.PostId
    FROM Posts p
    JOIN Comments c ON c.PostId = p.Id
    ORDER BY p.Id";
 
var postLookup = new Dictionary<int, Post>();
 
await connection.QueryAsync<Post, Comment, Post>(
    sql,
    (post, comment) =>
    {
        // Have we already built this post?
        if (!postLookup.TryGetValue(post.Id, out var existing))
        {
            existing = post;
            existing.Comments = new List<Comment>();
            postLookup.Add(existing.Id, existing);
        }
 
        existing.Comments.Add(comment); // add the comment to the right post
        return existing;
    },
    splitOn: "Id");
 
var posts = postLookup.Values.ToList();

Walk through it with our three rows:

• Row 1: post 1 is new. Add it to the dictionary. Add comment 100.
• Row 2: post 1 already exists. Reuse it. Add comment 101.
• Row 3: post 1 still exists. Reuse it. Add comment 102.

At the end the dictionary holds one post, and that post holds three comments. We return postLookup.Values because the function returns duplicates (the same post many times), but the dictionary keeps only one true copy.

Why ORDER BY helps

Notice the ORDER BY p.Id in the SQL. It is not strictly required, because the dictionary handles posts in any order. But ordering by the parent keeps all rows for one post together. That keeps memory small if you process and release groups, and it makes debugging easier because the rows read in a sensible order.

Mapping three tables at once

You can map more than two objects from a single row. Suppose we want a Post, its Author, and its Comments, all in one query. We use the three-type-plus-return overload and give two split points.

const string sql = @"
    SELECT p.Id, p.Title, p.AuthorId,
           a.Id, a.Name,
           c.Id, c.Text, c.PostId
    FROM Posts p
    JOIN Authors a ON a.Id = p.AuthorId
    JOIN Comments c ON c.PostId = p.Id
    ORDER BY p.Id";
 
var postLookup = new Dictionary<int, Post>();
 
await connection.QueryAsync<Post, Author, Comment, Post>(
    sql,
    (post, author, comment) =>
    {
        if (!postLookup.TryGetValue(post.Id, out var existing))
        {
            existing = post;
            existing.Author = author;
            existing.Comments = new List<Comment>();
            postLookup.Add(existing.Id, existing);
        }
 
        existing.Comments.Add(comment);
        return existing;
    },
    splitOn: "Id,Id");
 
var posts = postLookup.Values.ToList();

Here splitOn: "Id,Id" lists the first column of each new object after the first one. The first object (Post) needs no marker, because it always starts at column one. The second Id starts the Author. The third Id starts the Comment. So we name two split columns for three objects: one fewer marker than the number of objects.

If your author key were AuthorId and your comment key were CommentId, you would write splitOn: "AuthorId,CommentId" instead. The rule never changes: each name is the first column of the next object in your SELECT order.

Choosing between Query and Dapper's QueryMultiple

There is a second, very different approach for one-to-many: run two separate SELECTs in one command and read them as two result sets with QueryMultiple. This avoids the row explosion entirely.

const string sql = @"
    SELECT * FROM Posts WHERE AuthorId = @AuthorId;
    SELECT * FROM Comments WHERE PostId IN
        (SELECT Id FROM Posts WHERE AuthorId = @AuthorId);";
 
using var multi = await connection.QueryMultipleAsync(sql, new { AuthorId = 7 });
 
var posts = (await multi.ReadAsync<Post>()).ToList();
var comments = (await multi.ReadAsync<Comment>()).ToList();
 
// stitch them together in memory
var byPost = comments.ToLookup(c => c.PostId);
foreach (var post in posts)
    post.Comments = byPost[post.Id].ToList();

Both styles are valid. Here is how to pick.

For many-to-many (say Posts and Tags joined through a PostTags table), the same two ideas apply. You can JOIN all three tables and use a dictionary keyed by post id to gather tags, or you can pull posts and tags as two result sets with QueryMultiple and stitch them with a lookup. The QueryMultiple path is usually cleaner because many-to-many JOINs multiply rows the most.

Common mistakes and how to avoid them

A few traps catch almost everyone the first time.

• Forgetting splitOn when keys are not Id. Dapper splits on the first Id it sees. Name your split columns clearly.
• Wrong column order in the SELECT. splitOn matches positions, not table names. List columns in the same order as your generic types.
• Returning duplicates in one-to-many. Always collect into a dictionary and return its values, or you will get the same parent many times.
• Null children. With a LEFT JOIN, a parent with no children gives a row where the child columns are all null. Check for a null child before adding it to the list.

(post, comment) =>
{
    if (!postLookup.TryGetValue(post.Id, out var existing))
    {
        existing = post;
        existing.Comments = new List<Comment>();
        postLookup.Add(existing.Id, existing);
    }
 
    if (comment is not null)        // guard against LEFT JOIN nulls
        existing.Comments.Add(comment);
 
    return existing;
}

A note on Dapper today

Dapper is open source and free under the MIT license, maintained as the DapperLib/Dapper project. This is different from some other .NET libraries: tools like MediatR and MassTransit have moved to a commercial license for newer versions. Dapper has not. You can use it in your projects without a paid license, on modern .NET (it works happily on .NET 10, the current LTS).

That said, if your queries grow into deep, many-table loads, take a moment to ask whether a full ORM like EF Core would serve you better for that part of the app. Many teams happily mix both: EF Core for writes and complex models, Dapper for fast, hand-tuned reads.

Quick recap

• A JOIN returns one wide row with columns from many tables. Multi-mapping cuts that row into separate objects.
• The Query/QueryAsync overloads take multiple type arguments plus a small function where you connect the objects.
• splitOn marks the first column of each new object. Its default is "Id". Name it yourself when keys differ, and list one fewer marker than the number of objects.
• One-to-one is simple: attach the child and return the parent.
• One-to-many needs a dictionary keyed by the parent id, so each parent is built once while children pile into its list.
• For heavy one-to-many or many-to-many, prefer QueryMultiple to avoid row explosion, then stitch with a ToLookup.
• Watch out for wrong column order, missing splitOn, duplicate parents, and null children from LEFT JOIN.
• Dapper is free and MIT-licensed, and works great on .NET 10.

References and further reading

• Dapper Relationships (Learn Dapper)
• Dapper on GitHub (DapperLib)
• Dapper Multi-Mapping (Dapper Tutorial)
• Mastering Dapper Relationship Mappings (Milan Jovanović)