Multi-Mapping and splitOn in Dapper: Reading Related Data in a Single Query

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

We want to read books together with their authors in a single JOIN and fill each Book's Author property. We give Dapper two types and a small function that joins the two objects. What does this code print?

await using var conn = new SqliteConnection(cs);

try
{
    var books = await conn.QueryAsync<Book, Author, Book>(
        """
        select b.id, b.title, b.price, a.name, a.country
        from books b
        join authors a on a.id = b.author_id
        """,
        (book, author) =>
        {
            book.Author = author;
            return book;
        });
    Console.WriteLine(books.First().Author?.Name);
}
catch (Exception ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

If you said "Ursula K. Le Guin", look at the SQL again. The answer is the shortest route to understanding how multi-mapping works.

Setup

The packages are the same: Dapper 2.1.89 and Microsoft.Data.Sqlite 10.0.14. All four tables are present in this post. Stanislaw Lem deliberately has no books; that will come in handy with the LEFT JOIN.

using Dapper;
using Microsoft.Data.Sqlite;

public static class Setup
{
    public static async Task CreateAsync(string cs)
    {
        if (File.Exists("bookstore.db")) File.Delete("bookstore.db");
        await using var conn = new SqliteConnection(cs);
        await conn.ExecuteAsync("""
            create table authors (
                id      integer primary key,
                name    text not null,
                country text not null
            );
            create table books (
                id        integer primary key,
                author_id integer not null references authors(id),
                title     text not null,
                price     real not null
            );
            create table orders (
                id         integer primary key,
                customer   text not null,
                created_at text not null
            );
            create table order_items (
                id         integer primary key,
                order_id   integer not null references orders(id),
                book_id    integer not null references books(id),
                quantity   integer not null,
                unit_price real not null
            );
            insert into authors (id, name, country) values
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Ted Chiang', 'USA'),
                (4, 'Stanislaw Lem', 'Poland');
            insert into books (id, author_id, title, price) values
                (1, 1, 'A Wizard of Earthsea', 9.99),
                (2, 1, 'The Left Hand of Darkness', 12.50),
                (3, 2, 'Dune', 14.99),
                (4, 2, 'Dune Messiah', 11.99),
                (5, 3, 'Exhalation', 16.00);
            insert into orders (id, customer, created_at) values
                (1, 'Ada', '2026-11-04T10:00:00Z');
            insert into order_items (id, order_id, book_id, quantity, unit_price) values
                (1, 1, 3, 2, 14.99),
                (2, 1, 5, 1, 16.00);
            """);
    }
}

public sealed class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Country { get; set; } = "";
    public List<Book> Books { get; } = [];
}

public sealed class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public Author? Author { get; set; }
}

Every example starts with const string cs = "Data Source=bookstore.db"; and await Setup.CreateAsync(cs);.

The Answer: splitOn Looks for a Column

ArgumentException: Multi-map error: splitOn column 'Id' was not found - please ensure your splitOn parameter is set and in the correct order (Parameter 'splitOn')

The exception arrived before a single row was read. The logic of multi-mapping is this: Dapper cuts every row of the result into column slices from left to right and maps each slice, in order, to a type. The splitOn parameter says at which column a new slice starts, and its default value is "Id". Looking at Dapper's source, you can see that it searches for split points from right to left, compares case-insensitively, and never treats the first column as a split point. In our query b.id is the first column, and the author part has no id at all. Dapper can't find where Author starts and gives up.

Once you understand this error, the rest of multi-mapping falls into place: the column order in the SELECT list must match the order of the generic parameters exactly, and the first column of each new type must be the column named in splitOn.

1-1: A Book and Its Author

The most natural fix is to start the author slice with a.id:

var books = await conn.QueryAsync<Book, Author, Book>(
    """
    select b.id, b.title, b.price, a.id, a.name, a.country
    from books b
    join authors a on a.id = b.author_id
    order by b.id
    """,
    (book, author) =>
    {
        book.Author = author;
        return book;
    });

foreach (var book in books)
{
    Console.WriteLine($"{book.Id} {book.Title} by {book.Author!.Name} (author {book.Author.Id})");
}

1 A Wizard of Earthsea by Ursula K. Le Guin (author 1)
2 The Left Hand of Darkness by Ursula K. Le Guin (author 1)
3 Dune by Frank Herbert (author 2)
4 Dune Messiah by Frank Herbert (author 2)
5 Exhalation by Ted Chiang (author 3)

The last generic parameter, the third Book, is the result type. The first two are the objects produced from each row. The map function is called once per row and hands you a fresh Book and a fresh Author built from that row; you connect them however you like and return the result. Both tables having a column called id is not a problem, because each slice is mapped separately. b.id goes to Book.Id, a.id goes to Author.Id.

Choosing splitOn Yourself

splitOn doesn't have to be a primary key; it just means "the next object starts at this column." The way to fix the guessing example without changing its query is to make name the split point:

var cheap = await conn.QueryAsync<Book, Author, Book>(
    """
    select b.id, b.title, b.price, a.name, a.country
    from books b
    join authors a on a.id = b.author_id
    where b.price < @maxPrice
    order by b.price
    """,
    (book, author) =>
    {
        book.Author = author;
        return book;
    },
    new { maxPrice = 12m },
    splitOn: "name");

foreach (var book in cheap)
{
    Console.WriteLine($"{book.Title}: {book.Price} ({book.Author!.Name}, id={book.Author.Id})");
}

A Wizard of Earthsea: 9.99 (Ursula K. Le Guin, id=0)
Dune Messiah: 11.99 (Frank Herbert, id=0)

It works, but Author.Id stayed at 0, because the author's id isn't in the SELECT. Sometimes that's exactly what you want: you don't need to carry an id for an author name you only show on screen. Note also that the parameters go in their usual place, before splitOn.

With more than two types you give splitOn a comma-separated list; each item is the column where the next type starts. Let's read order items with their book and author:

DefaultTypeMap.MatchNamesWithUnderscores = true;

var lines = await conn.QueryAsync<OrderLine, Book, Author, OrderLine>(
    """
    select oi.quantity, oi.unit_price,
           b.title, b.price,
           a.name, a.country
    from order_items oi
    join books b on b.id = oi.book_id
    join authors a on a.id = b.author_id
    where oi.order_id = @orderId
    """,
    (line, book, author) =>
    {
        book.Author = author;
        line.Book = book;
        return line;
    },
    new { orderId = 1 },
    splitOn: "title,name");

foreach (var line in lines)
{
    Console.WriteLine($"{line.Quantity} x {line.Book!.Title} ({line.Book.Author!.Name}) = {line.Quantity * line.UnitPrice}");
}

public sealed class OrderLine
{
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public Book? Book { get; set; }
}

2 x Dune (Frank Herbert) = 29.98
1 x Exhalation (Ted Chiang) = 16

If every slice started with id, a single "id" (or no splitOn at all) would be enough; a single value is used for every split point. MatchNamesWithUnderscores is there to map unit_price to UnitPrice; the global setting we discussed in post 2 applies to multi-mapping just the same.

1-N: An Author and Their Books

Now from the other direction: we want the authors and each author's list of books. A JOIN repeats an author in as many rows as they have books. Dapper, however, produces a new Author for every row; it doesn't know that two rows belong to the same author. Let's see that first:

const string sql = """
    select a.id, a.name, a.country, b.id, b.title, b.price
    from authors a
    left join books b on b.author_id = a.id
    order by a.id, b.id
    """;

var naive = (await conn.QueryAsync<Author, Book?, Author>(sql, (author, book) =>
{
    if (book is not null) author.Books.Add(book);
    return author;
})).AsList();

Console.WriteLine($"Rows: {naive.Count}, same Le Guin instance: {ReferenceEquals(naive[0], naive[1])}");

Rows: 6, same Le Guin instance: False

Six Author objects for four authors, each with at most one book in its Books list. The solution is to keep a Dictionary outside the map function and reuse the existing object when you see the same id:

var authors = new Dictionary<int, Author>();

await conn.QueryAsync<Author, Book?, Author>(sql, (author, book) =>
{
    if (!authors.TryGetValue(author.Id, out var existing))
    {
        existing = author;
        authors.Add(existing.Id, existing);
    }

    if (book is not null)
    {
        book.Author = existing;
        existing.Books.Add(book);
    }

    return existing;
});

foreach (var author in authors.Values)
{
    var titles = author.Books.Count == 0 ? "-" : string.Join(", ", author.Books.Select(b => b.Title));
    Console.WriteLine($"{author.Name}: {titles}");
}

Ursula K. Le Guin: A Wizard of Earthsea, The Left Hand of Darkness
Frank Herbert: Dune, Dune Messiah
Ted Chiang: Exhalation
Stanislaw Lem: -

We take the result from authors.Values, not from the return value of QueryAsync; the return value still has one element per row. Look at Lem's line too: the LEFT JOIN brought the book columns back as null, and Dapper passed null to the book parameter. The rule is clear in the source: if the first column of a slice other than the first type, that is the split column, is null, Dapper doesn't create that object at all. That's why we wrote Book? in the generic parameter; with NRT on, the compiler also reminds you to check for null.

The N+1 Problem

In a world without multi-mapping, most people read the same data like this: fetch the authors first, then ask for each author's books.

var queries = 0;
var authors = (await conn.QueryAsync<Author>("select id, name, country from authors")).AsList();
queries++;
foreach (var author in authors)
{
    var books = await conn.QueryAsync<Book>(
        "select id, title, price from books where author_id = @authorId",
        new { authorId = author.Id });
    queries++;
    author.Books.AddRange(books);
}

One query for the authors, N queries for the books: N+1. The code is readable and the problem goes unnoticed on small data, which makes it one of the sneakiest performance bugs. The alternatives are a single JOIN or two queries. In the second approach we fetch all the books at once with IN @ids list expansion and group them in memory:

var authors2 = (await conn.QueryAsync<Author>("select id, name, country from authors")).AsList();
var ids = authors2.Select(a => a.Id).ToArray();
var rows = await conn.QueryAsync<(int AuthorId, int Id, string Title, decimal Price)>(
    "select author_id, id, title, price from books where author_id in @ids", new { ids });
var byAuthor = rows.ToLookup(r => r.AuthorId);
foreach (var author in authors2)
{
    author.Books.AddRange(byAuthor[author.Id].Select(r => new Book { Id = r.Id, Title = r.Title, Price = r.Price, Author = author }));
}

Here we used a value tuple; Dapper maps tuple elements by position, not by name. I added 496 authors with four books each to the database and ran the three approaches in the same program for three rounds. The output of the second round:

N+1:      501 queries, 1989 books, 16 ms
IN @ids:  2 queries, 1989 books, 3 ms
JOIN:     1 query, 1989 books, 2 ms

SQLite runs in-process, so there is no network latency here; the difference comes entirely from the fixed per-command cost. On a server database every round trip also adds network latency: with 1 ms of latency, 501 queries mean half a second before a single row is read.

The tradeoff when choosing between a JOIN and two queries is this. A JOIN is a single round trip, but it carries the parent columns again in every child row; if you JOIN two different collections onto one parent at the same time, the row count grows multiplicatively. The two-query approach has no repetition and reads each table once; the cost is a second round trip and the size of the IN list. SQL Server allows at most 2100 parameters per command, so very long lists need a different path; we will come back to that with table-valued parameters in post 13. There is also a middle ground between the two: send both queries in a single command and read two separate result sets. That is exactly the topic of the next post.

Common Pitfalls

Writing select * in a JOIN query. The column order stays tied to the table definition; when someone adds a column, the split points shift and you get mappings that are wrong but raise no error. Write the columns explicitly, in generic parameter order.

Leaving the SELECT order and the generic parameter order out of sync. Dapper looks at slices, not names; if you said Query<Book, Author, Book>, the first slice must be the book columns.

The splitOn column also appearing in the first type. Because Dapper searches right to left it finds the right point in most cases, but when you split on a generic name like name and the first type also has a name, be ready for surprises. Pick a genuinely distinctive split column, or give it an alias.

Forgetting the dictionary in 1-N and using the return value. You get repeated parents and single-item lists.

Forgetting the difference between INNER JOIN and LEFT JOIN. Authors without books don't appear in an INNER JOIN result at all; with a LEFT JOIN you need to declare the child type as nullable and check for null.

For those coming from EF Core: Include(a => a.Books) does this stitching for you and collects the same author into a single object. In Dapper you write the JOIN, the split points and the deduplication of repeated parents yourself. In return you know exactly which SQL goes over the wire.

A Small Experiment

★☆☆ In the 1-1 example's SELECT, move a.id to the end of the list. Which error, or which wrong mapping, do you see? Read from the output why column order matters so much.

★★☆ Rewrite the OrderLine example so that every slice starts with id (oi.id, b.id, a.id) and remove the splitOn parameter entirely. Verify that the output stays the same.

★★★ Build a two-level structure where authors come with their books and every book comes with its order items: Query<Author, Book?, OrderLine?, Author>. Collect both authors and books with separate dictionaries so nothing repeats, and observe how the row count grows.

In the next post we will return several result sets from a single command: with QueryMultiple and GridReader we will read an order header, its items and its total in one round trip, and talk about when to pick which compared to multi-mapping. The series will keep being updated as new Dapper versions ship.