Buffered and Unbuffered Reads in Dapper: Streaming Large Result Sets

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

Guess First

SQLite has a nice feature: you can define your own SQL function in C# on a connection. Below we define a function called touch that bumps a counter every time it's called, and put it in the where clause. That lets us count how many rows the database touches. The same query runs first buffered, then with buffered: false, and for both results we take Count() first and then First(). What are the touched values on the two lines?

await using var conn = new SqliteConnection(cs);
await conn.OpenAsync();

var touched = 0;
conn.CreateFunction("touch", (long id) => { touched++; return 1; });

const string sql = "select id, title, price from books where touch(id) = 1 order by id";

var buffered = conn.Query<Book>(sql);
Console.WriteLine($"buffered:   count={buffered.Count()}, first={buffered.First().Title}, touched={touched}");

touched = 0;
var unbuffered = conn.Query<Book>(sql, buffered: false);
Console.WriteLine($"unbuffered: count={unbuffered.Count()}, first={unbuffered.First().Title}, touched={touched}");

Console.WriteLine($"types: {buffered.GetType().Name} / {unbuffered.GetType().Name}");

There are four books in the table. Hint: the number on the second line is not 4.

Setup

The packages are the same: Dapper 2.1.89 and Microsoft.Data.Sqlite 10.0.14. In this post we use the small schema from post 6; instead of writing large data into a table we will generate it on the fly with a recursive CTE. Every example starts with const string cs = "Data Source=bookstore.db"; and await Setup.CreateAsync(cs);:

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,
                stock     integer not null check (stock >= 0)
            );
            insert into authors (id, name, country) values
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Ted Chiang', 'USA');
            insert into books (id, author_id, title, price, stock) values
                (1, 1, 'A Wizard of Earthsea', 9.99, 12),
                (2, 1, 'The Left Hand of Darkness', 12.50, 4),
                (3, 2, 'Dune', 14.99, 20),
                (4, 3, 'Exhalation', 16.00, 7);
            """);
    }
}

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

The Answer: An Unbuffered Result Is Not a List, It's a Recipe

buffered:   count=4, first=A Wizard of Earthsea, touched=4
unbuffered: count=4, first=A Wizard of Earthsea, touched=5
types: List`1 / <QueryImpl>d__149`1

Buffered Query<T> (its default form) runs the query right away, reads all the rows into a List<T>, closes the reader and hands you the list. Count() and First() then look only at the list in memory; the database touched four rows in total.

What comes back with buffered: false, on the other hand, is not a list but a compiler-generated iterator: <QueryImpl>d__149`1. This iterator works like a recipe; every time you enumerate it, it runs the query from scratch. Count() ran the query once and read all four rows. First() ran the query a second time but stopped as soon as it got the first row; SQLite touched just one more row. Five in total. Both faces of unbuffered show up on a single line here: you read only as much as you need, but every enumeration means a new round trip.

By the way, we opened the connection ourselves so that touch would work. As we said in post 6, Dapper opens and closes a closed connection for every call, and session-bound things like CreateFunction are lost in between.

Memory: A Million Rows

Where the difference really shows is large result sets. Let's read a million-row report in both ways and see how much memory it holds:

await using var conn = new SqliteConnection(cs);
await conn.OpenAsync();

const string sql = """
    with recursive n(i) as (select 1 union all select i + 1 from n where i < 1000000)
    select i as id, 'Book #' || i as title, i % 50 + 0.99 as price from n
    """;

var baseline = GC.GetTotalMemory(forceFullCollection: true);

var all = conn.Query<Book>(sql).AsList();
var bufferedMb = (GC.GetTotalMemory(true) - baseline) / 1024 / 1024;
Console.WriteLine($"buffered:   {all.Count:N0} rows, sum={all.Sum(b => b.Price):N2}, held={bufferedMb} MB");
all = null;

baseline = GC.GetTotalMemory(forceFullCollection: true);
var count = 0;
var sum = 0m;
long peakMb = 0;
foreach (var book in conn.Query<Book>(sql, buffered: false))
{
    count++;
    sum += book.Price;
    if (count % 250_000 == 0)
        peakMb = Math.Max(peakMb, (GC.GetTotalMemory(true) - baseline) / 1024 / 1024);
}
Console.WriteLine($"unbuffered: {count:N0} rows, sum={sum:N2}, held={peakMb} MB");

buffered:   1,000,000 rows, sum=25,490,000.00, held=191 MB
unbuffered: 1,000,000 rows, sum=25,490,000.00, held=0 MB

GC.GetTotalMemory(true) runs a full garbage collection and returns the size of the objects that are still alive; so these numbers are the memory "held at that moment." In the buffered read a million Books and a million strings are alive at the same time and hold 191 MB. In the unbuffered read each row becomes garbage once it's processed; none of the measurements taken every 250 thousand rows went over a megabyte. The totals are the same, the memory profile is completely different.

This doesn't mean "always use unbuffered." A buffered read releases the reader and the connection as early as possible; Dapper's README says that's why the default is buffered: it minimizes shared locks in the database and cuts down on network time. With unbuffered, the reader stays open for as long as you enumerate. If you do slow work inside the loop, the connection is busy and resources on the server are held for that whole time. The rule: if you need the whole result at once and it fits comfortably in memory, buffered; if it's a large stream you can process row by row and throw away, unbuffered.

QueryUnbufferedAsync and IAsyncEnumerable

The async counterpart of the sync Query with buffered: false has been in Dapper since 2.0.138: QueryUnbufferedAsync<T>. It returns an IAsyncEnumerable<T> and is consumed with await foreach. In this section and the next two we use the same million-row sql constant:

await using var conn = new SqliteConnection(cs);

var expensive = 0;
await foreach (var book in conn.QueryUnbufferedAsync<Book>(sql))
{
    if (book.Price > 49) expensive++;
}
Console.WriteLine($"expensive: {expensive:N0}, state: {conn.State}");

expensive: 20,000, state: Closed

An important signature detail: unlike the other Dapper methods, QueryUnbufferedAsync is an extension method defined on DbConnection, not on IDbConnection, and its transaction parameter is a DbTransaction. If you carry your connection around as an IDbConnection, this method isn't visible. That was one of the reasons the factory in post 6 returned a DbConnection. Because the connection came in closed, Dapper opened it when the stream started and closed it when the stream ended; state: Closed shows that.

Cancellation and Exiting Early

QueryUnbufferedAsync has no public overload that takes a CommandDefinition, so you can't pass the token through CommandDefinition. A comment in Dapper's source states the way plainly: on iterators, cancellation is expressed with WithCancellation. The inner implementation receives the token with [EnumeratorCancellation] and uses it when opening the connection, when executing the reader, and on every ReadAsync call:

using var cts = new CancellationTokenSource();
var seen = 0;
try
{
    await foreach (var book in conn.QueryUnbufferedAsync<Book>(sql).WithCancellation(cts.Token))
    {
        if (++seen == 1000) cts.Cancel();
    }
}
catch (OperationCanceledException ex)
{
    Console.WriteLine($"{ex.GetType().Name} after {seen:N0} rows, state: {conn.State}");
}

await foreach (var book in conn.QueryUnbufferedAsync<Book>(sql))
{
    if (book.Id == 3)
    {
        Console.WriteLine($"found {book.Title}, stopping early");
        break;
    }
}
Console.WriteLine($"state: {conn.State}");

TaskCanceledException after 1,000 rows, state: Closed
found Book #3, stopping early
state: Closed

The cancellation was caught in the ReadAsync right after the thousandth row, and the connection was again closed cleanly. In the second loop we exited early with break; await foreach disposed the iterator, and Dapper closed the reader in its finally block and closed the connection it had opened. There is one more detail in the source: if the reader hasn't reached its end yet, Dapper first calls the command's Cancel() method so the server stops sending the remaining rows. As we saw in post 6, Cancel() is an empty method in Microsoft.Data.Sqlite 10.0; on SQLite this has no effect, while on server databases it makes exiting early cheaper.

QueryAsync and CommandFlags.None

QueryAsync has no buffered parameter, but you can turn buffering off by passing CommandFlags.None through CommandDefinition. Let's look at what comes back:

var flagsNone = await conn.QueryAsync<Book>(new CommandDefinition(sql, flags: CommandFlags.None));
Console.WriteLine($"QueryAsync + CommandFlags.None: {flagsNone.GetType().Name}");

QueryAsync + CommandFlags.None: <ExecuteReaderSync>d__55`1

The name says it all. Dapper opens the reader asynchronously but hands out the rows with a synchronous iterator, by calling Read(); the comment in the source also notes that ReadAsync and cancellation can't be used here. So this combination is a hybrid that does synchronous reads in the middle of async code. If you want to stream rows in an async chain, the right tool is QueryUnbufferedAsync.

Common Pitfalls

Turning an unbuffered result into a list with .ToList(). You pull every row into memory anyway, and on top of that you lose buffered's advantage of closing the reader early.

Enumerating an unbuffered result more than once. As you saw in the guessing example, every enumeration runs the query again; if the data changed in between, two enumerations can even give different results.

Returning an unbuffered result from a method that disposes the connection:

foreach (var book in GetBooks(cs))
    Console.WriteLine(book.Title);

static IEnumerable<Book> GetBooks(string cs)
{
    using var conn = new SqliteConnection(cs);
    return conn.Query<Book>("select id, title, price from books", buffered: false);
}

A Wizard of Earthsea
The Left Hand of Darkness
Dune
Exhalation

On SQLite this code works, but by luck. The query runs not when the method returns but when the foreach starts; by then the connection has long been disposed. Dapper sees the connection as closed and opens it, and since SqliteConnection keeps its connection string after being disposed, it can be opened. When I tried the same code with SqlClient I got InvalidOperationException: The ConnectionString property has not been initialized. before the query even reached a server; a disposed SqlConnection forgets its connection string. If you hand an unbuffered result out of a method, carry the connection's lifetime along with it, or write an async iterator that returns IAsyncEnumerable<T> and put the await using inside it.

Running another command on the same connection while enumerating. SQLite allows it; when I ran an update for each book inside the loop, all four rows were updated. On SQL Server, however, running a second command on the same connection while a reader is open throws an exception unless MARS is enabled. If you need to write inside an unbuffered loop, use a second connection, or read buffered first.

For those coming from EF Core: you can stream results in EF Core too with AsAsyncEnumerable(); the choice between buffered ToListAsync() and streaming follows the same logic in both worlds.

A Small Experiment

★☆☆ In the guessing example, turn the unbuffered variable into a list once with .AsList() and take Count() and First() from that list. What does touched become, and why?

★★☆ Read the million-row query with QueryUnbufferedAsync and print the GC.GetTotalMemory(true) value every 100 thousand rows. Compare the values you measure with the sync buffered: false version.

★★★ Rewrite the GetBooks method as async IAsyncEnumerable<Book> GetBooksAsync(string cs, [EnumeratorCancellation] CancellationToken ct = default): open the connection inside the method with await using, and yield return the rows from QueryUnbufferedAsync. When the caller cancels with WithCancellation, verify that the connection closes by listening to the StateChange event.

So far we haven't complained about SQLite's own types, but types such as Guid, DateTimeOffset and TimeSpan come back from SQLite as a string or a long. In the next post we will teach Dapper these conversions with type handlers, and look at the story of DateOnly/TimeOnly support in 2.1.86 and at custom column mapping. The series will keep being updated as new Dapper versions ship.