Streaming Database Data with IAsyncEnumerable in C#

Applications increasingly need to process database results as they arrive rather than waiting for an entire query to finish. A reporting dashboard, an inventory screen, or an analytics workflow can start working with the first records while the database is still producing the rest. In modern C#, IAsyncEnumerable<T> provides a practical way to model this flow with asynchronous iteration.

For Australian software teams, streaming can make a noticeable difference when users connect over variable NBN, mobile, or regional networks. It can reduce memory pressure, improve perceived response times, and support cloud-native services hosted in Azure Australia East or Australia Southeast. The design still requires care: database connections, cancellation, transactions, and privacy obligations must all be handled deliberately.

Why asynchronous streaming matters

A conventional method often returns Task<List<Order>>. The database driver executes the query, transfers every matching row, materialises the complete list, and only then allows the caller to use the result. This is straightforward, but a large export can consume substantial memory and delay the first visible result.

IAsyncEnumerable<Order> changes the consumption model. The caller requests the next item asynchronously, allowing the application to process records incrementally. In an ASP.NET Core endpoint, this can support progressive response writing. In a background worker, each item can be validated, transformed, or sent to another service without keeping the complete dataset in memory.

The pattern is especially useful for large reports, event-like database reads, batch integrations, and administrative search results. It is less valuable for a small lookup where returning a compact object or list is simpler. Streaming adds lifecycle concerns, so it should be selected because the workload benefits from incremental processing, not because asynchronous APIs are fashionable.

A typical consumer uses await foreach:

await foreach (var order in repository.ReadOrdersAsync(cancellationToken))
{
    await ProcessOrderAsync(order, cancellationToken);
}

The compiler translates this into repeated asynchronous calls to MoveNextAsync. That means the consumer can pause between records, honour cancellation, and avoid blocking a thread while waiting for database or network I/O.

Building a database iterator

With Entity Framework Core, an asynchronous query can be exposed through AsAsyncEnumerable. A repository might apply filters and projections before returning the sequence:

public async IAsyncEnumerable<OrderSummary> ReadOrdersAsync(
    DateTime from,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)
{
    await foreach (var item in _db.Orders
        .AsNoTracking()
        .Where(o => o.CreatedUtc >= from)
        .OrderBy(o => o.Id)
        .Select(o => new OrderSummary(o.Id, o.Total))
        .AsAsyncEnumerable()
        .WithCancellation(cancellationToken))
    {
        yield return item;
    }
}

The yield return statement makes each projected record available as it is read. AsNoTracking is generally appropriate for read-only streams because EF Core does not need to maintain change-tracking entries for every row. Projection also keeps the result small by selecting only the columns the consumer needs.

Cancellation should be part of the public method contract. The [EnumeratorCancellation] attribute tells the compiler how to connect the caller’s cancellation token to the generated async iterator. The consumer can then stop a request when a browser disconnects, a job is cancelled, or an operational timeout expires.

Database provider behaviour matters. SQL Server, PostgreSQL, and other providers may use a data reader underneath, but buffering and network behaviour can differ. An async iterator does not magically make every provider fully non-buffering. Verify the provider’s implementation and measure memory use with realistic row counts before promising constant-memory behaviour.

The same approach works with lower-level ADO.NET when precise control is required:

public async IAsyncEnumerable<string> ReadCodesAsync(
    [EnumeratorCancellation] CancellationToken cancellationToken = default)
{
    await using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync(cancellationToken);

    await using var command = new SqlCommand(
        "SELECT Code FROM Products ORDER BY ProductId", connection);

    await using var reader = await command.ExecuteReaderAsync(cancellationToken);

    while (await reader.ReadAsync(cancellationToken))
    {
        yield return reader.GetString(0);
    }
}

The await using statements are important. The connection, command, and reader remain alive for the duration of enumeration and are disposed when iteration completes or is cancelled.

Choosing a streaming approach

The best API depends on how data is consumed, how much control is needed, and where the results go. A list remains a good fit for bounded collections, while a channel can decouple a producer from one or more consumers.

A web API should distinguish between streaming a response and merely returning an async enumerable internally. Depending on the formatter and response type, ASP.NET Core may serialise items progressively, or it may buffer them. Test the actual endpoint with a client that reports first-byte time and total transfer time.

For browser-facing JSON, newline-delimited JSON or another explicitly streaming format can be more practical than a single JSON array. A JSON array cannot be considered complete until its closing bracket arrives, while newline-delimited records can be processed as independent messages. For public APIs, document the format, error behaviour, cancellation semantics, and whether partial results are valid.

Testing deserves equal attention. A fake repository can expose a small async iterator, while integration tests should verify disposal, cancellation, ordering, and provider behaviour against a real database. These C# testing practices are useful when validating that a consumer stops reading after cancellation and does not leave connections behind.

Handling performance and reliability

Streaming reduces application memory use, but it does not remove database costs. A query still needs suitable indexes, a stable execution plan, and an efficient projection. For a large Australian retail catalogue, filtering by tenant, warehouse, and updated timestamp before ordering can be far more important than changing the return type.

Stable ordering is essential when a stream is used for exports or incremental processing. Ordering by a unique key, or by a timestamp plus a unique key, prevents ambiguous results. If a process can restart, record a checkpoint such as the last successfully processed identifier. This is safer than assuming that a long-lived database cursor will survive a network interruption.

Keep the unit of work short where possible. A consumer that performs slow external calls while holding a database connection open can exhaust the connection pool. One option is to read a controlled batch into a bounded channel, then process it with a separate stage. Another is to keep processing lightweight and persist progress frequently. The correct choice depends on the required consistency and throughput.

Timeouts need separate consideration. Configure command timeouts for database execution, cancellation for request lifetime, and operational limits for the entire job. A Sydney customer using a mobile hotspot may abandon a large download, while a scheduled Melbourne data integration may need to continue independently of any HTTP request.

Avoid unbounded parallelism. await foreach is sequential by default, which protects the database and makes ordering predictable. If records can be processed concurrently, use a bounded worker count and measure database load, downstream rate limits, and memory usage. Parallelising every item can turn a well-behaved stream into a burst of requests.

Applying the pattern in Australian systems

Australian organisations must consider the Privacy Act 1988 and the Australian Privacy Principles when streaming personal information. A stream can expose data for longer than expected if a connection remains open, and partial downloads may be difficult to audit. Minimise selected fields, enforce authorisation before opening the reader, and log access without placing sensitive values in application logs.

Data residency may also influence architecture. Azure Australia East in New South Wales and Azure Australia Southeast in Victoria can help teams keep workloads close to users or within an approved region, but residency is a contractual and governance question rather than an automatic guarantee. Check service configuration, backups, replicas, support access, and third-party processors.

The local market creates varied operating conditions. A SaaS product serving customers in Sydney, Brisbane, and Perth may experience different network paths and peak usage patterns. A stream that feels immediate in a city office can behave differently on regional connectivity. Response limits, resumable exports, and a visible progress indicator are useful operational features for these conditions.

Everyday work habits matter as well. Australian users commonly move between office Wi-Fi, home broadband, and mobile connections, so a long-running report should tolerate a dropped client. For internal systems, a background export with an email or portal notification can be more reliable than holding an HTTP request open during a commute or an intermittent connection.

Security controls should be built into the iterator’s boundary. Apply tenant filters in the database query, use parameterised commands, and avoid returning entities that contain unrelated personal or financial fields. For organisations aligned with the Australian Signals Directorate’s Essential Eight, streaming endpoints should also fit existing identity, patching, logging, and application-control practices.

A practical implementation checklist is concise:

• Project only the columns required by the consumer.
• Pass cancellation from the request or job to the database command.
• Use a unique, deterministic ordering for repeatable exports.
• Dispose connections and readers through await using.
• Test real provider behaviour with realistic result sizes.
• Apply authorisation and privacy filtering before enumeration starts.
• Add bounded concurrency only after measuring the sequential version.

IAsyncEnumerable<T> is most effective when treated as a resource-management feature as much as a syntax feature. It lets an application begin useful work early and avoid buffering entire query results, but the database reader remains a live resource until enumeration ends. Clear ownership, cancellation, bounded processing, and observable failure handling turn asynchronous database iteration into a dependable part of a production C# system.