Async LINQ and streaming with IAsyncEnumerable

Await foreach, the operators EF Core provides, and why System.Linq.Async is a tool with a real cost that is not always worth paying.

IAsyncEnumerable and await foreach

// EF Core T-SQL style: the query streams, rows arrive in batches
public async IAsyncEnumerable<Order> OpenOrdersAsync(
    [EnumeratorCancellation] CancellationToken ct = default)
{
    await foreach (var order in db.Orders
        .Where(o => o.Status == OrderStatus.Open)
        .OrderBy(o => o.Id)
        .AsAsyncEnumerable()
        .WithCancellation(ct))
    {
        yield return order;
    }
}

// The operators EF Core supports on IAsyncEnumerable
var list = await db.Orders.Where(o => o.Total > 100).ToListAsync(ct);
var one = await db.Orders.FirstOrDefaultAsync(o => o.Id == id, ct);
var any = await db.Orders.AnyAsync(o => o.Status == OrderStatus.Open, ct);
var sum = await db.Orders.SumAsync(o => o.Total, ct);
await db.Orders.ForEachAsync(o => Console.WriteLine(o.Id), ct);

// AsAsyncEnumerable to mix streaming with a long-running consumer
await foreach (var order in db.Orders.AsAsyncEnumerable().WithCancellation(ct))
{
    await ProcessAsync(order, ct);
}

// Batch a stream into pages without loading everything
public static async IAsyncEnumerable<IReadOnlyList<T>> ChunkAsync<T>(
    IAsyncEnumerable<T> source, int size,
    [EnumeratorCancellation] CancellationToken ct = default)
{
    var buffer = new List<T>(size);
    await foreach (var item in source.WithCancellation(ct))
    {
        buffer.Add(item);
        if (buffer.Count == size)
        {
            yield return buffer;
            buffer = new List<T>(size);
        }
    }
    if (buffer.Count > 0) yield return buffer;
}

• Add [EnumeratorCancellation] to the token parameter of an async iterator, or the token a caller passes through WithCancellation is ignored.
• EF Core buffers internally in batches; a streaming query still holds a connection open for its duration, so keep the consumer fast.
• An await foreach cannot cross a transaction boundary casually: a long stream inside an open transaction holds locks for its whole duration.
• Returning IAsyncEnumerable to a client is what ASP.NET Core uses to stream a response, but the enumerator must be disposed — always use await foreach.

System.Linq.Async and its limits

// The System.Linq.Async package adds Where, Select and friends over IAsyncEnumerable
using System.Linq;

IAsyncEnumerable<int> doubled = source.SelectAwait(async x => x * 2);

// Inside a database query this is a mistake: the operator cannot be translated,
// so every row is fetched first and the work happens in memory.
var wrong = db.Orders
    .ToAsyncEnumerable()
    .Where(o => o.Total > 100)          // client side!
    .ToListAsync();

// The right order: filter and project in the query, then switch to async streaming
var right = db.Orders
    .Where(o => o.Total > 100)
    .Select(o => new { o.Id, o.Total })
    .AsAsyncEnumerable();

await foreach (var o in right.WithCancellation(ct))
    Console.WriteLine(o.Id);

FAQ

Related

Deferred execution and querying a database Query performance and avoiding N+1

Last refreshed 2026-09-18.