QueryMultiple and GridReader in Dapper: Several Result Sets in One Round Trip

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

Guess First

We send two selects with a single command: first the author, then that author's books. After reading both result sets, we call Read once more as if there were a third. Does the third Read return an empty list, null, or throw an exception?

await using var conn = new SqliteConnection(cs);

using (var grid = conn.QueryMultiple("""
    select id, name, country from authors where id = @id;
    select id, title, price from books where author_id = @id;
    """, new { id = 2 }))
{
    var author = grid.ReadSingle<Author>();
    var books = grid.Read<Book>().AsList();
    Console.WriteLine($"{author.Name}: {books.Count} books, IsConsumed={grid.IsConsumed}");
    try
    {
        var extra = grid.Read<Book>();
    }
    catch (Exception ex)
    {
        Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
    }
}

Write your guess down; setup first.

Setup

The packages are the same: Dapper 2.1.89 and Microsoft.Data.Sqlite 10.0.14. The tables and data are the same as in the previous post: four authors (Stanislaw Lem has no books), five books, one order and two order items.

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);
            """);
    }
}

There is a single difference in the models: we add an AuthorId property to Book, because we will need it when joining two separate result sets in memory:

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 int AuthorId { 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: Grids Are Read in Order, and Once

Frank Herbert: 2 books, IsConsumed=True
ObjectDisposedException: The reader has been disposed; this can happen after all data has been consumed
Object name: 'Dapper.SqlMapper+GridReader'.

An exception, and an ObjectDisposedException at that. QueryMultiple runs the command and hands you a SqlMapper.GridReader. A GridReader is a thin wrapper around a single data reader that stays open underneath. Each Read call reads the next result set, a "grid", and advances the reader to the following one. When the last grid is consumed, Dapper closes the reader right away; IsConsumed=True shows that. From then on every Read tries to touch a closed reader and is met with this exception.

The other half of the rule is order. Grids arrive in the order of the statements in the SQL, and Dapper doesn't know which grid belongs to which type. Let's see what happens if you read the first grid with the wrong type:

using (var grid = conn.QueryMultiple("""
    select id, name, country from authors where id = @id;
    select id, title, price from books where author_id = @id;
    """, new { id = 2 }))
{
    var wrong = grid.Read<Book>().AsList();
    Console.WriteLine($"first grid as Book: {wrong.Count} row, Id={wrong[0].Id}, Title='{wrong[0].Title}'");
}

first grid as Book: 1 row, Id=2, Title=''

No error. The author's id landed in Book.Id, name and country were ignored because Book has nothing to match them, and Title stayed empty. The "unmatched columns are silently skipped" behavior we saw in post 2 applies here too; the wrong order produces wrong data, not an exception. Write your Read calls in the order of the SQL, matching them up line by line.

Master-Detail: Order, Items and Total

The most typical use of QueryMultiple is master-detail screens: an order header, its items and some summary information. Let's write a method that gets all three in one round trip. While reading the items we will also use the previous post's multi-mapping inside a grid:

DefaultTypeMap.MatchNamesWithUnderscores = true;

await using var conn = new SqliteConnection(cs);

var details = await GetOrderDetailsAsync(conn, orderId: 1);
if (details is not null)
{
    Console.WriteLine($"Order {details.Order.Id} for {details.Order.Customer}, {details.Lines.Count} lines, total {details.Total}");
    foreach (var line in details.Lines)
    {
        Console.WriteLine($"  {line.Quantity} x {line.Book!.Title} by {line.Book.Author!.Name}");
    }
}

var missing = await GetOrderDetailsAsync(conn, orderId: 42);
Console.WriteLine($"Order 42: {(missing is null ? "not found" : "found")}");

static async Task<OrderDetails?> GetOrderDetailsAsync(DbConnection conn, int orderId)
{
    await using var grid = await conn.QueryMultipleAsync("""
        select id, customer, created_at from orders where id = @orderId;

        select oi.quantity, oi.unit_price, b.id, b.title, b.price, a.id, 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
        order by oi.id;

        select coalesce(sum(quantity * unit_price), 0) from order_items where order_id = @orderId;
        """, new { orderId });

    var order = await grid.ReadSingleOrDefaultAsync<Order>();
    if (order is null) return null;

    var lines = grid.Read<OrderLine, Book, Author, OrderLine>((line, book, author) =>
    {
        book.Author = author;
        line.Book = book;
        return line;
    }).AsList();

    var total = await grid.ReadSingleAsync<decimal>();
    return new OrderDetails(order, lines, total);
}

public sealed record OrderDetails(Order Order, IReadOnlyList<OrderLine> Lines, decimal Total);

public sealed class Order
{
    public int Id { get; set; }
    public string Customer { get; set; } = "";
    public string CreatedAt { get; set; } = "";
}

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

Order 1 for Ada, 2 lines, total 45.98
  2 x Dune by Frank Herbert
  1 x Exhalation by Ted Chiang
Order 42: not found

Let's go through a few things one by one. The three statements share the same parameter object; @orderId is defined once and used in every statement. Methods such as ReadSingleOrDefaultAsync, ReadFirstAsync and ReadAsync<T> are the grid counterparts of the single-row family from post 3, and they throw exceptions by the same rules. We didn't pass splitOn when reading the items, because every slice starts with id, and the default for multi-mapping on a GridReader is "id" as well.

One more detail: the Read<TFirst, TSecond, ..., TReturn> overloads that do multi-mapping have no async counterpart; in the GridReader source the async methods are defined only for single-type reads. On SQLite this has no practical significance, but on server databases it means that grid's rows are read synchronously.

When order 42 wasn't found, the method returned early and two grids were never read. That's not a problem: await using closes the remaining result sets and the reader while disposing the GridReader. We didn't open the connection either; Dapper opened the closed connection for QueryMultipleAsync and closed it when the GridReader was disposed.

Joining Two Result Sets in Memory

In the previous post we built the 1-N relationship with a JOIN and a dictionary. We can get the same result with two grids: authors in one, books in the other.

await using var grid = await conn.QueryMultipleAsync("""
    select id, name, country from authors where country = @country order by id;

    select b.id, b.author_id, b.title, b.price
    from books b
    join authors a on a.id = b.author_id
    where a.country = @country
    order by b.id;
    """, new { country = "USA" });

var authors = (await grid.ReadAsync<Author>()).AsList();
var booksByAuthor = (await grid.ReadAsync<Book>()).ToLookup(b => b.AuthorId);

foreach (var author in authors)
{
    author.Books.AddRange(booksByAuthor[author.Id]);
    Console.WriteLine($"{author.Name}: {string.Join(", ", author.Books.Select(b => b.Title))}");
}

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

No dictionary, no repeated author rows, no map function. Every author came once and every book came once; ToLookup grouped the books by AuthorId. Keeping the second query's filter in line with the first is your responsibility: here both statements filter with country = @country. If you don't want that repetition, writing the first statement's result to a temp table and joining against it in the second is also an option.

Unbuffered Grids

Read<T> is buffered by default: it reads all of a grid's rows into a List<T>, then advances the reader to the next grid. When you pass buffered: false, rows are read as you enumerate them. That means you have to consume a grid all the way to the end before moving on to the next one:

using (var grid = conn.QueryMultiple("""
    select id, name, country from authors;
    select id, title, price from books;
    """))
{
    try
    {
        var authors = grid.Read<Author>(buffered: false);
        var books = grid.Read<Book>().AsList();
        Console.WriteLine($"books {books.Count}");
        Console.WriteLine($"authors {authors.Count()}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
    }
}
Console.WriteLine($"state: {conn.State}");

InvalidOperationException: Query results must be consumed in the correct order, and each result can only be consumed once
state: Closed

The second Read call came while the first grid was still waiting to be consumed, and Dapper refused it. On the async side the counterpart of the same idea is ReadUnbufferedAsync<T>(), available since 2.0.138; it returns an IAsyncEnumerable<T> and is consumed with await foreach. In the next post we will measure and discuss the difference between buffered and unbuffered on large result sets.

QueryMultiple or Multi-Mapping?

Both save you from N+1, and both are a single round trip. The choice depends on the shape of the data.

Multi-mapping carries several objects inside a single row. It is unbeatable for 1-1 relationships (a book and its author, an item and its book); the JOIN produces no repetition and the code stays short. In 1-N it carries the parent columns again in every child row and needs deduplication with a dictionary. When you JOIN two collections onto one parent, the row count multiplies.

QueryMultiple brings each table back as a separate result set. There is no repetition, and you can put unrelated data into the same round trip too, like the header, items and summary of a master-detail screen. The cost is repeating the filter in every statement and joining the results in memory. I use multi-mapping for 1-1 and small 1-N, QueryMultiple for several collections or independent queries, and on most master-detail screens both together, as in the order example above.

A note on the provider side too: sending several statements in one command is supported on SQLite, SQL Server and PostgreSQL, but not on every provider. And if you are planning on Dapper.AOT, QueryMultiple is not among the calls it intercepts; the call falls back to classic Dapper. We will open that up in the last post of the series.

Common Pitfalls

Writing the Read calls in a different order from the statements in the SQL. You get wrong data, not an exception; it's especially hard to notice when the columns of two grids look alike.

Calling Read more times than there are grids, or trying to read a grid twice. The first produces an ObjectDisposedException, the second an InvalidOperationException with the "consumed in the correct order" message.

Not disposing the GridReader. The reader underneath stays open, and if Dapper opened the connection, the connection stays open too. Always using or await using.

Reading a grid with buffered: false and moving on to the next one without consuming it.

Not keeping filters in sync across statements. If you write the first grid with country = 'USA' and the second without a filter, the lookup fills up with books that belong to no author.

For those coming from EF Core: EF Core's AsSplitQuery() option solves a similar problem by splitting collection Includes into separate queries. In Dapper you do that splitting yourself with QueryMultiple, and you also decide whether the queries go in the same round trip.

A Small Experiment

★☆☆ Swap the two statements in the guessing example but leave the Read calls as they are. What does ReadSingle<Author>() do, and why?

★★☆ Add a fourth grid to GetOrderDetailsAsync: the total book count of the authors of the books in the order (select a.name, count(*) ...). Carry the result as a new property on the OrderDetails record.

★★★ Rewrite the two-grid author-book example with streaming: read the authors buffered and the books with ReadUnbufferedAsync<Book>() inside an await foreach, adding each book directly to its author from a dictionary instead of using a lookup. Explain which reading order is mandatory, and why.

Every result set we have read so far was small enough to fit in memory. In a million-row report, though, a buffered read means hundreds of megabytes. In the next post we will stream rows with buffered: false and QueryUnbufferedAsync, and look at memory and behavioral differences with real measurements. The series will keep being updated as new Dapper versions ship.