Async Query Execution, Streaming vs Buffering, Cancellation, and Connection Lifetimes

Learning outcomes

A worker exports thousands of ServiceHub work orders. The team changes ToListAsync to streaming to lower peak memory, then accidentally starts a second query on the same DbContext while the first reader is still active. Async and streaming change resource timing; they do not make a context concurrent. This lesson makes the connection/reader lifetime and cancellation boundaries observable.

1. Async belongs on operations that perform I/O

// Existing course model — deliberately not redesigned for Chapter 08.public sealed partial class WorkOrder{    public int Id { get; private set; }    public WorkOrderPublicId PublicId { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public string Summary => _summary;    public WorkOrderPriority Priority { get; private set; }    public DateTimeOffset OpenedUtc { get; private set; }    // Chapter 04 also maps shadow DateTime "CreatedUtc" -> created_utc    // so the SQLite lab can compare/order a UTC timestamp server-side.    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ServiceAddress ServiceAddress { get; private set; } = null!;    public Guid Revision { get; private set; }}public enum WorkOrderPriority{    Low = 1,    Normal = 2,    High = 3}

var rows = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High) // builds tree    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc")) // builds tree    .Take(100)                                        // builds tree    .ToListAsync(ct);                                 // executes I/O

There is no WhereAsync because Where does not perform database I/O; it builds the query expression. ToListAsync, SingleAsync, CountAsync, and similar terminal methods execute provider work.

2. Buffering materializes the whole result before returning

var rows = await db.WorkOrders    .AsNoTracking()    .OrderBy(w => w.Id)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary })    .ToListAsync(ct);// Database reader is finished by the time ToListAsync completes.foreach (var row in rows)    WriteCsv(row);

Buffering gives a stable in-memory list that can be revisited without re-querying, at the cost of memory proportional to result size. The context is available for another operation after the awaited query completes.

3. Streaming processes rows while enumeration is active

var stream = db.WorkOrders    .AsNoTracking()    .OrderBy(w => w.Id)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary })    .AsAsyncEnumerable();await foreach (var row in stream.WithCancellation(ct)){    WriteCsv(row);}

AsAsyncEnumerable() executes the EF query as asynchronous enumeration begins. Rows can be processed incrementally instead of all being retained. During enumeration the data reader—and typically the database connection borrowed for that operation—remains active. Slow downstream work can therefore extend database resource occupancy.

4. DbContext lifetime is not permanent connection lifetime

dbug: Microsoft.EntityFrameworkCore.Database.Connection      Opening connection to database 'servicehub-lab.db'.info: Microsoft.EntityFrameworkCore.Database.Command      Executed DbCommand (...)dbug: Microsoft.EntityFrameworkCore.Database.Connection      Closing connection to database 'servicehub-lab.db'.

A short-lived context does not normally keep the physical connection open for its entire object lifetime. EF opens/closes around database operations as needed, while the ADO.NET provider may independently use connection pooling. If application code explicitly opens the connection, ownership/lifetime changes and must be managed deliberately.

5. Cancellation is requested, not magically guaranteed

public sealed record WorkOrderQueueRow(    int Id, string Number, string Customer,    WorkOrderPriority Priority, DateTime CreatedUtc);public async Task<IReadOnlyList<WorkOrderQueueRow>> LoadQueueAsync(    ServiceHubContext db,    CancellationToken cancellationToken){    return await db.WorkOrders        .AsNoTracking()        .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))        .ThenBy(w => w.Id)        .Select(w => new WorkOrderQueueRow(            w.Id, w.WorkOrderNumber, w.CustomerName, w.Priority,            EF.Property<DateTime>(w, "CreatedUtc")))        .Take(100)        .ToListAsync(cancellationToken);}

EF passes cancellation tokens to the provider, but Microsoft notes providers may differ in how fully/quickly cancellation is honored. Application cancellation also does not roll back an unrelated already-committed transaction. Test cancellation on the actual provider and workload.

6. Deliberately wrong: concurrent operations on one context

// WRONG: one DbContext is not a parallel query scheduler.var highCountTask = db.WorkOrders    .CountAsync(w => w.Priority == WorkOrderPriority.High, ct);var technicianCountTask = db.Technicians    .CountAsync(ct);await Task.WhenAll(highCountTask, technicianCountTask);

If the operations overlap, EF’s concurrency detector commonly throws InvalidOperationException with a message that a second operation started before the previous one completed. Even when a provider could multiplex, EF Core does not support concurrent use of one context instance.

7. Repair A: await sequentially

var highCount = await db.WorkOrders    .CountAsync(w => w.Priority == WorkOrderPriority.High, ct);var technicianCount = await db.Technicians    .CountAsync(ct);

This is the simplest repair when both queries belong to one request/unit of work and parallel latency is not required.

8. Repair B: independent contexts for independent parallel work

await using var db1 = await factory.CreateDbContextAsync(ct);await using var db2 = await factory.CreateDbContextAsync(ct);var highCountTask = db1.WorkOrders    .CountAsync(w => w.Priority == WorkOrderPriority.High, ct);var technicianCountTask = db2.Technicians.CountAsync(ct);await Task.WhenAll(highCountTask, technicianCountTask);

This removes the shared-context violation, but it also creates two independent units of work/connections. Parallel database work can increase pool pressure and database contention. Measure throughput before assuming lower per-request latency is globally beneficial.

9. Streaming can create accidental long-lived database work

await foreach (var row in query.AsAsyncEnumerable().WithCancellation(ct)){    // BAD boundary: a 500 ms HTTP call can keep the DB reader open per row.    await remoteTicketSystem.SendAsync(row.WorkOrderNumber, ct);}

Repair by separating database read from slow external I/O: buffer a bounded page/batch, close the reader, then call the remote system; or design a durable queue/outbox when reliability requires it. Streaming is not automatically “more scalable” if each row stalls the database resource.

10. Hands-on lab: connection and cancellation evidence

1. Enable EF connection and command logs for the disposable SQLite lab.
2. Run a 500-row ToListAsync projection; record when connection open/close events occur.
3. Run the same query with AsAsyncEnumerable, add a small artificial per-row delay, and observe how long enumeration/connection activity lasts.
4. Cancel both forms with a short CancellationTokenSource; record the provider outcome instead of assuming timing.
5. Run the deliberate same-context Task.WhenAll case and capture the concurrency exception if operations overlap.
6. Repair sequentially, then optionally repeat with two factory-created contexts.
7. Record any connection-pool/provider assumptions if you repeat against SQL Server/PostgreSQL.

11. Production judgment and bridge

Default to clear awaited operations. Stream when result cardinality makes buffering expensive and downstream processing is fast enough not to hold database resources excessively. Propagate cancellation, but test provider behavior. Use separate contexts—not one context—for independent parallel database operations, and account for connection-pool/database pressure. Lesson 5 ties the whole chapter together by correlating LINQ intent, ToQueryString, command logs/tags, parameters, and a real SQLite execution plan before tuning.

Authoritative references

• Asynchronous Programming - EF Core — async operators, cancellation, one-operation-per-context warning
• DbContext Lifetime, Configuration, and Initialization — context lifetime/thread-safety rules
• Efficient Querying - EF Core — buffering versus streaming and result-size considerations
• Connection Resiliency - EF Core — provider execution behavior context
• Microsoft.Data.Sqlite overview — SQLite ADO.NET/provider context for the free lab