Streaming Methods

Overview

Streaming methods provide memory-efficient processing of large result sets by returning IEnumerable<T> or IAsyncEnumerable<T> instead of loading all results into memory at once. These methods are ideal for processing large datasets without consuming excessive memory.

How a streamed query differs from a buffered one

Three consequences follow from the shape, and each of them is a thing to plan around rather than a defect:

Interceptors do not run on a streamed query. This is intended. An interceptor wraps an execution and reports on its result, and a lazy IAsyncEnumerable has no result to report until every row has been read, so wrapping one would force the full materialization that streaming exists to avoid. If you need logging or metrics on a streamed read, put them in the loop body.

The connection stays open for as long as you enumerate. A streamed query that you abandon part way through releases its connection when the enumerator is disposed, which foreach and await foreach do for you at the end of the block. Storing the enumerator and forgetting it holds a connection open until the garbage collector reaches it.

Do not stream into another query on the same connection. Issuing a second command while a reader is open on the same connection either throws or opens a second connection behind your back, depending on the provider. Buffer the first result with Query<T> when the loop body needs the connection.

Synchronous Streaming Methods

QueryStream<T>(string sql)

Executes a query and returns an enumerable sequence of entities using strict mapping mode. Results are processed lazily as they are enumerated.

Signature:

public static IEnumerable<T> QueryStream<T>(this IDbConnection connection, string sql) where T : new()

Parameters:

• connection: The database connection
• sql: The SQL query to execute

Returns:

• IEnumerable<T>: A lazy enumerable of entities of type T

Example:

foreach (var product in connection.QueryStream<Product>("SELECT * FROM products"))
{
    // Process each product individually
    ProcessProduct(product);
}

QueryStream<T>(string sql, object parameters)

Executes a parameterized query and returns an enumerable sequence of entities using strict mapping mode.

Signature:

public static IEnumerable<T> QueryStream<T>(this IDbConnection connection, string sql, object parameters) where T : new()

QueryStream<T>(string sql, CommandOptions<T> options)

Executes a query with command options and returns an enumerable sequence of entities using strict mapping mode.

Signature:

public static IEnumerable<T> QueryStream<T>(this IDbConnection connection, string sql, CommandOptions<T> options) where T : new()

QueryStream<T>(string sql, object parameters, CommandOptions<T> options)

Executes a parameterized query with command options and returns an enumerable sequence of entities using strict mapping mode.

Signature:

public static IEnumerable<T> QueryStream<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options) where T : new()

Asynchronous Streaming Methods

QueryStreamAsync<T>(string sql, CancellationToken cancellationToken = default)

Asynchronously executes a query and returns an async enumerable sequence of entities using strict mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryStreamAsync<T>(this IDbConnection connection, string sql, CancellationToken cancellationToken = default) where T : new()

Parameters:

• connection: The database connection
• sql: The SQL query to execute
• cancellationToken: Cancellation token for the operation

Returns:

• IAsyncEnumerable<T>: An async enumerable of entities of type T

Example:

await foreach (var product in connection.QueryStreamAsync<Product>("SELECT * FROM products"))
{
    // Process each product individually
    await ProcessProductAsync(product);
}

QueryStreamAsync<T>(string sql, object parameters, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query and returns an async enumerable sequence of entities using strict mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryStreamAsync<T>(this IDbConnection connection, string sql, object parameters, CancellationToken cancellationToken = default) where T : new()

QueryStreamAsync<T>(string sql, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a query with command options and returns an async enumerable sequence of entities using strict mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryStreamAsync<T>(this IDbConnection connection, string sql, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

QueryStreamAsync<T>(string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query with command options and returns an async enumerable sequence of entities using strict mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryStreamAsync<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

Partial Mapping Streaming Methods

QueryPartialStream<T>(string sql)

Executes a query and returns an enumerable sequence of entities using partial mapping mode.

Signature:

public static IEnumerable<T> QueryPartialStream<T>(this IDbConnection connection, string sql) where T : new()

QueryPartialStream<T>(string sql, object parameters)

Executes a parameterized query and returns an enumerable sequence of entities using partial mapping mode.

Signature:

public static IEnumerable<T> QueryPartialStream<T>(this IDbConnection connection, string sql, object parameters) where T : new()

QueryPartialStream<T>(string sql, CommandOptions<T> options)

Executes a query with command options and returns an enumerable sequence of entities using partial mapping mode.

Signature:

public static IEnumerable<T> QueryPartialStream<T>(this IDbConnection connection, string sql, CommandOptions<T> options) where T : new()

QueryPartialStream<T>(string sql, object parameters, CommandOptions<T> options)

Executes a parameterized query with command options and returns an enumerable sequence of entities using partial mapping mode.

Signature:

public static IEnumerable<T> QueryPartialStream<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options) where T : new()

Example:

// Only product_id and product_name are selected; partial mapping tolerates the missing columns
foreach (var product in connection.QueryPartialStream<Product>("SELECT product_id, product_name FROM products"))
{
    ProcessProduct(product);
}

Async Partial Mapping Streaming Methods

QueryPartialStreamAsync<T>(string sql, CancellationToken cancellationToken = default)

Asynchronously executes a query and returns an async enumerable sequence of entities using partial mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryPartialStreamAsync<T>(this IDbConnection connection, string sql, CancellationToken cancellationToken = default) where T : new()

QueryPartialStreamAsync<T>(string sql, object parameters, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query and returns an async enumerable sequence of entities using partial mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryPartialStreamAsync<T>(this IDbConnection connection, string sql, object parameters, CancellationToken cancellationToken = default) where T : new()

QueryPartialStreamAsync<T>(string sql, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a query with command options and returns an async enumerable sequence of entities using partial mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryPartialStreamAsync<T>(this IDbConnection connection, string sql, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

QueryPartialStreamAsync<T>(string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query with command options and returns an async enumerable sequence of entities using partial mapping mode.

Signature:

public static IAsyncEnumerable<T> QueryPartialStreamAsync<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

Example:

// Only product_id and product_name are selected; partial mapping tolerates the missing columns
await foreach (var product in connection.QueryPartialStreamAsync<Product>("SELECT product_id, product_name FROM products"))
{
    await ProcessProductAsync(product);
}

Unbuffered Partial Mapping Streaming Methods

These overloads are functionally identical to the QueryPartialStream overloads above — results are streamed and not buffered in memory. They exist as an alias for callers who prefer the "unbuffered" naming to describe the streaming behavior.

QueryPartialUnbuffered<T>(string sql)

Executes a query and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IEnumerable<T> QueryPartialUnbuffered<T>(this IDbConnection connection, string sql) where T : new()

QueryPartialUnbuffered<T>(string sql, object parameters)

Executes a parameterized query and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IEnumerable<T> QueryPartialUnbuffered<T>(this IDbConnection connection, string sql, object parameters) where T : new()

QueryPartialUnbuffered<T>(string sql, CommandOptions<T> options)

Executes a query with command options and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IEnumerable<T> QueryPartialUnbuffered<T>(this IDbConnection connection, string sql, CommandOptions<T> options) where T : new()

QueryPartialUnbuffered<T>(string sql, object parameters, CommandOptions<T> options)

Executes a parameterized query with command options and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IEnumerable<T> QueryPartialUnbuffered<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options) where T : new()

Example:

// Unbuffered stream - most memory efficient for large result sets
foreach (var product in connection.QueryPartialUnbuffered<Product>("SELECT id, name FROM products"))
{
    Console.WriteLine($"{product.Id}: {product.Name}");
}

Unbuffered Async Partial Mapping Streaming Methods

QueryPartialUnbufferedAsync<T>(string sql, CancellationToken cancellationToken = default)

Asynchronously executes a query and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IAsyncEnumerable<T> QueryPartialUnbufferedAsync<T>(this IDbConnection connection, string sql, CancellationToken cancellationToken = default) where T : new()

QueryPartialUnbufferedAsync<T>(string sql, object parameters, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IAsyncEnumerable<T> QueryPartialUnbufferedAsync<T>(this IDbConnection connection, string sql, object parameters, CancellationToken cancellationToken = default) where T : new()

QueryPartialUnbufferedAsync<T>(string sql, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a query with command options and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IAsyncEnumerable<T> QueryPartialUnbufferedAsync<T>(this IDbConnection connection, string sql, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

QueryPartialUnbufferedAsync<T>(string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default)

Asynchronously executes a parameterized query with command options and streams the results as entities of type T using partial mapping mode (unbuffered).

Signature:

public static IAsyncEnumerable<T> QueryPartialUnbufferedAsync<T>(this IDbConnection connection, string sql, object parameters, CommandOptions<T> options, CancellationToken cancellationToken = default) where T : new()

Example:

// Unbuffered async stream - most memory efficient for large result sets
await foreach (var product in connection.QueryPartialUnbufferedAsync<Product>("SELECT id, name FROM products"))
{
    await ProcessProductAsync(product);
}

Notes