Project Only Needed Columns, Avoid Accidental Client Work, and Bound Result Sets

Learning outcomes

1. Performance begins with the shape crossing the database boundary

ServiceHub has accumulated a rich WorkOrder aggregate: identifiers, customer data, summary, priority, timestamps, concurrency state, relationships, complex/owned values, JSON, and navigation collections. A dispatch-list endpoint does not need that whole graph. If it asks EF for tracked entities anyway, the database may send columns the endpoint never reads, EF must materialize entity instances and snapshots, and the change tracker must index each tracked identity.

A projection chooses the columns and result type needed by the use case. A bounded result set puts an explicit upper bound on returned rows. A client boundary such as ToList or AsEnumerable moves later LINQ work from the provider-translated IQueryable pipeline into .NET. None of those terms implies “always faster”; the lesson measures the consequences.

2. Establish a deliberately expensive baseline

The first query is legal, simple, and often wasteful for a read-only list. Tracking is the default because the result is a normal entity query.

await using var db = factory.CreateDbContext();var full = await db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(50)    .ToListAsync(cancellationToken);Console.WriteLine($"Rows: {full.Count}");Console.WriteLine($"Tracked: {db.ChangeTracker.Entries<WorkOrder>().Count()}");

The mandatory SQLite path orders by the Chapter 04 shadow CreatedUtc : DateTime rather than OpenedUtc : DateTimeOffset, preserving the provider boundary established earlier. A list of 50 rows is deliberately bounded and ordered by a unique tie-breaker.

3. Project the read model EF actually needs to materialize

For a queue card, project only the fields the UI needs. Because no entity instance appears in the projection, normal entity tracking is not involved.

public sealed record WorkOrderQueueItem(    int Id,    string Number,    string Customer,    WorkOrderPriority Priority,    DateTime CreatedUtc);var queueQuery = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(50)    .Select(w => new WorkOrderQueueItem(        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.Priority,        EF.Property<DateTime>(w, "CreatedUtc")));Console.WriteLine(queueQuery.ToQueryString());var queue = await queueQuery.ToListAsync(cancellationToken);Console.WriteLine($"Tracked: {db.ChangeTracker.Entries().Count()}");

Expected SQLite SQL is narrow: it selects the identifier, work-order number, customer, priority, and created_utc column rather than every mapped scalar/JSON/owned field. The exact quoting and parameter names are provider output, not a portable SQL contract.

SELECT "w"."work_order_id", "w"."work_order_number",       "w"."customer_name", "w"."priority", "w"."created_utc"FROM "work_orders" AS "w"WHERE "w"."priority" = @__High_0ORDER BY "w"."created_utc" DESC, "w"."work_order_id"LIMIT @__p_1

4. Projection and AsNoTracking solve different problems

AsNoTracking() matters when EF materializes entity instances but you do not intend to persist changes through that context. A pure scalar/DTO projection already avoids tracking because there is no entity instance to track. A projection that contains an entity—such as new { Entity = w, NoteCount = w.Notes.Count }—can still track that entity unless you opt out.

5. Keep aggregate work on the server when SQL can express it

Counting notes does not require loading every note. Chapter 09 showed aggregates and correlated subqueries; performance engineering now treats that translation as a data-movement decision.

var cards = await db.WorkOrders    .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(50)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        NoteCount = w.Notes.Count,        HasTechnician = w.AssignedTechnicianId != null    })    .ToListAsync(cancellationToken);

Inspect ToQueryString() and the command log. The important evidence is that the database returns one aggregate value per projected work order, not a materialized collection merely so .NET can call Count.

6. Failure case: materialize first, then discover the filter

var all = await db.WorkOrders    .AsNoTracking()    .ToListAsync(cancellationToken); // database command executes herevar visible = all    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => w.WorkOrderNumber)    .Take(50)    .Select(w => new { w.Id, w.WorkOrderNumber })    .ToList();

This code may appear fine against ten development rows. With a large production table it transfers and materializes every work order before discarding most of them. The repair is not “use a faster collection”; it is to move the filter/order/limit/projection before the terminal operator so EF can translate them.

var visible = await db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => w.WorkOrderNumber)    .ThenBy(w => w.Id)    .Take(50)    .Select(w => new { w.Id, w.WorkOrderNumber })    .ToListAsync(cancellationToken);

7. Measure without pretending one stopwatch proves causality

A local harness can compare the shapes, but disclose what it includes. Run Release mode, seed a deterministic row count, warm the model once, use the same database file/indexes, and record whether the OS/database cache is warm. Stopwatch captures end-to-end elapsed time; it does not separate translation, SQLite execution, materialization, garbage collection, and scheduler noise.

static async Task<Sample> MeasureAsync(    IDbContextFactory<ServiceHubContext> factory,    Func<ServiceHubContext, CancellationToken, Task<int>> operation,    CancellationToken ct){    await using var db = await factory.CreateDbContextAsync(ct);    var before = GC.GetTotalAllocatedBytes(precise: true);    var sw = Stopwatch.StartNew();    var rows = await operation(db, ct);    sw.Stop();    var allocated = GC.GetTotalAllocatedBytes(precise: true) - before;    return new Sample(rows, sw.Elapsed, allocated,        db.ChangeTracker.Entries().Count());}public sealed record Sample(int Rows, TimeSpan Elapsed,    long ProcessAllocatedBytes, int TrackedEntries);

GC.GetTotalAllocatedBytes is process-wide, so keep the lab single-purpose and label the number approximate. For trustworthy microbenchmarks, Lesson 5 introduces BenchmarkDotNet and a fuller evidence protocol.

8. Lab: prove the data-movement difference

1. Clone/reset the disposable ServiceHub SQLite file and seed at least 5,000 deterministic work orders with a known priority distribution.
2. Run the full tracked query and the DTO projection after one warm-up each.
3. Capture ToQueryString(), EF command count, returned rows, tracker-entry count, elapsed time, and approximate allocations.
4. Repeat with 50 versus 500 rows to show that result cardinality can dominate small translation differences.
5. Move a Where after ToListAsync, observe the wider/unbounded SQL, then repair it.
6. Delete/reset only the disposable benchmark database; do not mutate the course migration history for a performance experiment.

9. Production judgment

Prefer projections for read models when the caller does not need entity behavior; use no-tracking entity queries when a real entity-shaped read is useful; and bound interactive result sets. Do not contort update workflows into DTO-only shapes just to avoid tracking, and do not assume a narrow SELECT fixes a missing index or poor predicate. Inspect SQL and the database plan. Chapter 17 next removes a smaller layer of CPU overhead: EF’s query compilation/cache lookup.

Authoritative references

Performance behavior is workload-, provider-, and version-sensitive. Re-check these primary sources before carrying a result into production.

• Efficient Querying - EF Core — projection, limiting result sets, pagination and query efficiency
• Tracking vs. No-Tracking Queries — tracking semantics and entity instances inside projections
• Pagination - EF Core — deterministic ordering and bounded pages
• Advanced Performance Topics — performance methodology and EF overhead boundaries
• SQLite provider limitations — provider-specific type/query limitations
• Query Tags - EF Core — correlating application queries and SQL evidence