Why streaming matters

When a service needs to return thousands of rows from a database or parse a massive CSV file, loading everything into a List<T> first can blow the memory budget and add latency. IAsyncEnumerable<T> lets you pull items one‑by‑one (or in small batches) while the caller processes them, keeping the footprint tiny and the pipeline responsive.

Real‑world scenario: exporting audit logs

Imagine an admin UI that lets users download the last six months of audit events — potentially millions of rows. The UI streams the CSV to the browser so the user sees progress immediately. With IAsyncEnumerable the server never materialises the whole result set; it reads a batch from the DB, yields each record, and the HTTP response flushes chunk by chunk.

The producer: an async iterator over a database

public async IAsyncEnumerable<AuditEvent> GetAuditEventsAsync(
    DateTime from,
    DateTime to,
    [EnumeratorCancellation] CancellationToken ct = default)
{
    // Use a lightweight Dapper query that streams rows.
    const string sql = """
        SELECT Id, Timestamp, UserId, Action, Details
        FROM AuditLog
        WHERE Timestamp BETWEEN @from AND @to
        ORDER BY Timestamp;
    """;

    await using var conn = _dbFactory.CreateConnection();
    await conn.OpenAsync(ct);

    // Dapper's QueryUnbuffered returns an IEnumerable that reads lazily.
    await foreach (var row in conn.QueryUnbufferedAsync<AuditEventDto>(sql, new { from, to }).WithCancellation(ct))
    {
        // Map DTO → domain model (could be a record).
        yield return new AuditEvent(
            row.Id,
            row.Timestamp,
            row.UserId,
            row.Action,
            row.Details);
    }
}

The method signature returns IAsyncEnumerable<AuditEvent>. The [EnumeratorCancellation] attribute forwards the caller’s cancellation token into the iterator, so a client abort stops the DB read immediately.

Consuming the stream in an ASP.NET Core endpoint

[HttpGet("export")]
public async Task ExportAuditLog(
    DateTime from,
    DateTime to,
    CancellationToken ct)
{
    Response.ContentType = "text/csv";
    Response.Headers["Content-Disposition"] = "attachment; filename=audit.csv";

    // Write header line first.
    await Response.WriteAsync("Id,Timestamp,UserId,Action,Details\n", ct);

    await foreach (var ev in _auditService.GetAuditEventsAsync(from, to, ct))
    {
        var line = $"{ev.Id},{ev.Timestamp:O},{ev.UserId},{ev.Action},{EscapeCsv(ev.Details)}\n";
        await Response.WriteAsync(line, ct);
        await Response.Body.FlushAsync(ct); // push to client immediately
    }
}

Notice the FlushAsync call after each line — this forces the server to send the buffered data to the network, giving the browser a true streaming experience.

Why not just return Task<IEnumerable<T>>?

Returning a fully materialised collection forces the server to allocate memory for every row before the first byte leaves the process. With IAsyncEnumerable the allocation is limited to the current batch (often a single row), and back‑pressure is natural: if the client pauses, the iterator simply awaits on the next MoveNextAsync.

Error handling and resource cleanup

  • Try/finally inside the iterator – the await using on the connection guarantees disposal even if the consumer cancels.
  • CancellationToken propagation – the [EnumeratorCancellation] attribute is the idiomatic way to let the caller abort the enumeration.
  • Exception surfaces at the point of consumption – any DB error bubbles out when the caller awaits the next element, making it easy to log and return a 5xx.

Performance tip: batch the DB reads

If the underlying provider supports server‑side cursors (e.g., PostgreSQL’s FETCH or SQL Server’s FAST_FORWARD), you can ask Dapper to fetch rows in chunks of 500‑1000. This reduces round‑trips while still keeping the memory profile low.

// Dapper 2.1+ supports buffered size via CommandDefinition
var cmd = new CommandDefinition(sql, new { from, to }, cancellationToken: ct);
await foreach (var row in conn.QueryUnbufferedAsync<AuditEventDto>(cmd, buffered: false))
{
    yield return Map(row);
}

When to reach for this pattern

  1. Large result sets that don’t fit comfortably in RAM.
  2. Long‑running exports, reports, or data‑pipeline stages.
  3. Any API where the consumer can start processing before the full payload arrives (Server‑Sent Events, gRPC streaming, file downloads).

In day‑to‑day work I’ve replaced dozens of “load‑all‑then‑return” endpoints with this approach and seen memory drop from hundreds of megabytes to a few megabytes, while the first byte reaches the client in milliseconds. It’s a small language feature that pays off big in production.