Mise Streaming

Streaming rows

StreamAsync<T> yields mapped rows while the database reader remains open. Use it when processing a large result incrementally is preferable to collecting it into a list.

Enumerate within the reader's scope

await using var connection = new NpgsqlConnection(connectionString);
await using var reader = new DbReader(connection: connection);

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .OrderBy($"{CourseTbl.CourseIdCol:raw}");

await foreach (var course in reader.StreamAsync<CourseRow>(query, cancellationToken))
{
    Console.WriteLine($"{course.CourseId}: {course.Title}");
}

The query begins when enumeration begins. The stream owns its command and database reader until enumeration ends or the enumerator is disposed. A normal await foreach, including an early break, disposes its enumerator.

Keep resources alive

Do not create a reader in an await using scope and return its stream for enumeration after that scope ends. Consume it inside the scope, or design an iterator that owns and keeps its resources for the entire enumeration.

A Partie Provider disposes its provided reader when downstream work finishes. Enumerate a provided reader's stream before returning from that work. A deferred stream returned as a handler value can outlive the provider's resource scope.

One active operation

While a stream is active, another terminal on the same reader throws InvalidOperationException. Do not issue a lookup through that reader inside its streaming loop. Complete the stream, use another reader, or include the required data in the query.

The active stream also prevents disposing the reader until the enumerator has finished or been disposed. Cancellation and exceptions unwind its cleanup, but provider or cleanup exceptions can still propagate.

Choose list or stream

A list buffers all rows and releases the underlying database reader before returning. A stream keeps that resource open while the consumer processes each row. Slow consumers therefore hold the connection for longer.

Streaming returns IAsyncEnumerable<T> directly rather than a Result<IReadOnlyList<T>>. Empty results yield nothing; mapping and database failures occur during enumeration. See Readers and Resource lifetimes.