Streaming EF Core Query Results with IAsyncEnumerable: What It Saves and What It Costs

A nightly export reads every order created in the last 90 days and writes each one to a file. The obvious EF Core code is await context.Orders.Where(...).ToListAsync(). That works until the result set grows: ToListAsync materializes every row, and every tracked entity, before your loop body runs once. Memory scales with the size of the result, not with the amount of work you are doing.

IAsyncEnumerable<T> is the alternative. Instead of handing you one big list, the query yields rows as the underlying data reader produces them, and await foreach processes each one before the next is fetched.

Deferred execution is not the same as streaming

An IQueryable is already lazy — nothing runs until you enumerate it. The difference is what enumeration does. ToListAsync() enumerates the whole thing into a List<T>. await foreach over the same query pulls one element at a time from the reader.

EF Core has exposed this since EF Core 3.0. The examples below assume EF Core 5 or later on .NET 5 or later, with C# 8 or later for await foreach.

A worked example: streaming a projection

// Console app or background service. EF Core 5+ / .NET 5+.
// ct is a CancellationToken supplied by your host or request.
public async Task ExportOrdersAsync(
    OrdersContext context, DateTime since, TextWriter writer, CancellationToken ct)
{
    var query = context.Orders
        .Where(o => o.CreatedUtc >= since)
        .OrderBy(o => o.Id)
        .Select(o => new { o.Id, o.CustomerId, o.Total });

    await foreach (var row in query.AsAsyncEnumerable().WithCancellation(ct))
    {
        await writer.WriteLineAsync($"{row.Id},{row.CustomerId},{row.Total}");
    }
}

Three details carry most of the benefit:

• The Select projects to an anonymous type, so no entity is materialized and nothing enters the change tracker. That is what makes the memory saving real rather than theoretical.
• AsAsyncEnumerable() states the streaming intent explicitly. In EF Core 3.0+ you can also write await foreach directly over the IQueryable.
• WithCancellation(ct) flows cancellation into the enumeration. Without it, a cancelled job keeps pulling rows until the loop finishes.

The DbContext must stay alive for the whole loop. The database connection is held open while the reader is active, so a long export occupies a pooled connection for its duration. On a busy service that is a capacity decision, not only a memory one.

The trade-off: no tracking, and no second query

If you stream entities instead of a projection, EF Core still tracks them as they are read. The change tracker holds a reference to every entity it has seen, so memory grows with the result set anyway — you have moved the allocation from the result list to the state manager. Use AsNoTracking() when streaming entities you do not intend to update.

Two further constraints are worth knowing before you commit to the pattern:

• One active enumeration per DbContext. You cannot start a second query while the first await foreach is still running. EF Core detects concurrent use of the same context instance and throws rather than silently interleaving. Buffer what you need, or use a separate context.
• Streaming is a poor fit for "update every row." Reading a million rows so you can save them one at a time is slower than a set-based statement. EF Core 7+ provides ExecuteUpdateAsync and ExecuteDeleteAsync, which translate to a single UPDATE or DELETE without loading anything into memory.

Provider behavior, and how to verify it

The IAsyncEnumerable shape belongs to EF Core; the SQL and the reader behavior belong to the provider and its ADO.NET driver. Most relational providers translate the same LINQ patterns, but a provider can reject a method it cannot translate, and a driver can buffer rows before handing them back. Do not assume streaming from the C# syntax alone.

Check it in three steps, against your target database:

1. Confirm the baseline. Run dotnet --version and check the EF Core package version in the .csproj. await foreach requires C# 8; async streaming requires EF Core 3.0 or later.
2. Log the SQL. Add optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information); where you configure the context, then run the query once. Confirm the provider emits a single SELECT and does not warn about client evaluation.
3. Measure allocation, not impressions. Run the two variants — ToListAsync() and await foreach — in a small benchmark and compare GC.GetAllocatedBytesForCurrentThread() before and after, or watch dotnet-counters while the job runs. If the streaming version allocates just as much, something is still buffering or tracking.

Run these checks with a realistic row count. A few thousand rows will show no difference at all; the point is the shape of the curve as the result set grows.

When to reach for it

Stream when the result set is large, the work per row is independent, and you do not need change tracking or a second query on the same context. Materialize with ToListAsync when the set is small, when you need to iterate it more than once, or when you intend to modify and save the entities. When the operation is really "change every row," skip both and use ExecuteUpdateAsync.

The practical test: if you cannot say what bounds the result set — a date range, a page size, a top-N — streaming buys time but not a guarantee. Add the bound anyway.