The Problem: Loading Everything Into Memory

Early in my career I wrote a service that exported a million‑row SQL query to CSV. The naïve implementation pulled the entire result set into a List<T>, then iterated to write the file. On a modest VM the process ate 2 GB of RAM and frequently threw OutOfMemoryException under load. The fix wasn’t a bigger server — it was a change in how we enumerated data.

Enter IAsyncEnumerable<T>

IAsyncEnumerable<T> (introduced in C# 8) lets you produce a sequence asynchronously, one element at a time, without materialising the whole collection. The consumer can await foreach over the stream, and the runtime only holds the current item in memory. This pattern is a perfect match for database readers, HTTP streams, or any source that can yield data incrementally.

Key insight: Back‑pressure is built‑in. The producer pauses when the consumer isn’t ready, preventing uncontrolled buffering.

Real‑World Scenario: Streaming a CSV Export

Imagine an admin dashboard where users can download a transaction log that may contain tens of millions of rows. We need a responsive UI, low memory footprint, and the ability to cancel the download mid‑stream.

Producer: Async Database Reader

public async IAsyncEnumerable<TransactionDto> GetTransactionsAsync(
    DateTime from,
    DateTime to,
    [EnumeratorCancellation] CancellationToken ct = default)
{
    // Use a lightweight Dapper query that streams rows via a data reader
    const string sql = "SELECT Id, Amount, CreatedAt, Description FROM Transactions " +
                       "WHERE CreatedAt BETWEEN @From AND @To ORDER BY CreatedAt";

    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);
    await using var cmd = new SqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@From", from);
    cmd.Parameters.AddWithValue("@To", to);

    await using var reader = await cmd.ExecuteReaderAsync(
        CommandBehavior.SequentialAccess | CommandBehavior.SingleResult, ct);

    while (await reader.ReadAsync(ct))
    {
        // Map only the columns we need — no object‑graph overhead
        yield return new TransactionDto
        {
            Id = reader.GetGuid(0),
            Amount = reader.GetDecimal(1),
            CreatedAt = reader.GetDateTime(2),
            Description = reader.IsDBNull(3) ? null : reader.GetString(3)
        };
    }
}

Consumer: Writing Directly to the Response Stream

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

    await using var writer = new StreamWriter(Response.BodyWriter.AsStream(),
        new UTF8Encoding(encoderShouldEmitUTF8Identifier: false),
        bufferSize: 4096,
        leaveOpen: true);

    // Write header
    await writer.WriteLineAsync("Id,Amount,CreatedAt,Description");

    await foreach (var tx in _repo.GetTransactionsAsync(from, to, ct))
    {
        // Escape fields that contain commas or quotes
        var line = $"{tx.Id},{tx.Amount:F2},{tx.CreatedAt:O},{CsvEscape(tx.Description)}";
        await writer.WriteLineAsync(line);
        await writer.FlushAsync(ct); // push data to client promptly
    }
}

private static string CsvEscape(string? value)
{
    if (string.IsNullOrEmpty(value)) return "";
    if (value.Contains(',') || value.Contains('"') || value.Contains('\n'))
    {
        return '"' + value.Replace("\"", "\"\"") + '"';
    }
    return value;
}

Why This Works

  • Constant memory: Only one TransactionDto lives on the heap at a time.
  • Cancellation flows naturally: The CancellationToken is threaded through both producer and consumer; aborting the request stops the reader immediately.
  • Back‑pressure: await writer.FlushAsync yields control until the client’s TCP window opens, so the server never buffers megabytes of CSV.
  • Composability: You can chain LINQ‑style operators (Where, Select) on the async stream without materialising.

Common Pitfalls & How to Avoid Them

  1. Forgetting EnumeratorCancellation – without it the token isn’t observed inside the iterator, so cancellation stalls.
  2. Using ToListAsync() upstream – defeats the purpose; keep the pipeline async all the way.
  3. Blocking calls inside the iterator (e.g., .Result) – they deadlock the thread pool. Always await.
  4. Large object allocation per row – reuse a struct or a pooled object if the DTO is heavy; the example uses a tiny class for clarity.

When Not to Use It

If the dataset is small (< 10 k rows) or you need random access (sorting, grouping) after retrieval, materialising into a list is simpler and faster. IAsyncEnumerable shines when the data is *produced* and *consumed* in a single forward pass.

Final Thoughts

Switching to IAsyncEnumerable turned a fragile, memory‑hungry export into a robust streaming endpoint that scales to millions of rows on a modest instance. The pattern appears in many libraries — Entity Framework Core’s AsAsyncEnumerable, System.Text.Json’s JsonSerializer.SerializeAsyncEnumerable, and even gRPC server‑side streaming. Once you internalise the “pull‑based, back‑pressured” mindset, you’ll spot opportunities to replace batch loads with streams throughout your codebase.