skip to content

In EF Core 10, why does a read-only product search that projects with Select into a DTO usually beat loading full Product entities?

level: juniorimportance: must knowfreq 66%

answer

  1. fewer columns over the wire
  2. queries track by default
  3. snapshot plus identity map per entity
  4. no entity in the result, nothing tracked
  5. an entity inside an anonymous type

basics

~20 s

A Select projection makes EF Core fetch only the projected columns and materialize small DTOs that are never tracked, so it skips change-tracking snapshots and identity resolution; loading full tracked entities transfers and allocates far more for the same page.

solid answer

~40 s

Queries track by default, so loading `Product` entities brings back every mapped column, builds a full instance per row, records it in the identity map and stores a snapshot for `SaveChanges`. A `Select` into a DTO changes the SQL to list only the projected columns, lets the database compute expressions, and materializes records EF never tracks, because a result with no entity instances leaves the change tracker empty. The EF Core docs' own benchmark shows the allocations dropping from about 1.3 MB for tracked entities to about 540 KB without tracking and about 252 KB with a projection. The caveats: an entity placed inside an anonymous type is still tracked, projections cannot be saved back, and when a read path really needs entities, `AsNoTracking()` or a context-wide no-tracking default removes the tracking cost.

code

csharp · 16 lines
csharp
public sealed record ProductCard(int Id, string Name, string Brand, int PriceCents, bool InStock);

var cards = await db.Products
    .TagWith("product search")
    .Where(p => p.Category == category && p.IsActive)
    .OrderBy(p => p.PriceCents)
    .Select(p => new ProductCard(
        p.Id,
        p.Name,
        p.Brand.Name,
        p.PriceCents,
        p.StockCount > 0))
    .Take(40)
    .ToListAsync();

// cards are not entities: db.ChangeTracker.Entries() is still empty here

go deeper

for a junior

Recall that queries track by default, that Select narrows the columns, and that a DTO projection leaves the change tracker empty.

for a middle

Explain what tracking costs per row, snapshots and identity resolution, and which projection shapes still track an entity hidden inside them.

for a senior

Show how you would prove the gain with logged SQL and allocation numbers, and when a context-wide no-tracking default is safe for a read path.

for a principal

Decide how read and write paths are separated across the codebase so read models stay projections and tracking is reserved for real units of work.

## The search that allocates too much An outdoor-gear shop's search page shows one card per product: id, name, brand, price and an in-stock flag. A first version loads entities: `var products = await db.Products.Where(p => p.Category == category).ToListAsync();` Every row brings back **every mapped column**, including long `Description` and `CareInstructions` text the card never shows, and EF builds a full `Product` for each row. Because EF Core queries **track by default** (`QueryTrackingBehavior.TrackAll`), each instance is also registered with the context's change tracker. ## What tracking costs on a read For every tracked entity EF: - looks the key up in its **identity map**, so a row loaded twice becomes one instance (identity resolution); - stores a **snapshot** of the original property values, so `SaveChanges` can later detect what changed; - keeps the instance referenced from the context until the context is disposed. None of that helps a page that only renders cards. The EF Core documentation benchmarks the same calculation three ways: tracked entities allocated about 1.3 MB, the same entities without tracking about 540 KB, and a projection of the single needed column about 252 KB, with the running time falling in the same order. ## What a projection changes A `Select` into a DTO or an anonymous type changes both the SQL and the materialization: 1. The `SELECT` lists **only the columns the projection reads**. A reference used inside the projection, such as `p.Brand.Name`, is joined in the same statement. 2. Expressions such as `p.StockCount > 0` are evaluated by the database, so only the result crosses the wire. 3. EF materializes small `ProductCard` records instead of entities. **If a result contains no entity instances, nothing is tracked**, so there are no snapshots and no identity-map entries. The search therefore transfers less, allocates less, and leaves less for the garbage collector after every request. ## Where tracking still sneaks in - An anonymous type that **contains an entity**, such as `new { Product = p, BrandName = p.Brand.Name }`, still tracks that `Product`. - A projection cannot be written back: change tracking works only with entity instances, so update paths load entities or use a set-based update. - A method EF cannot translate is allowed in the **final** `Select` and runs on the client after the rows arrive; filters, ordering and paging must stay translatable or the query fails. ## No-tracking at scale, when you need entities Sometimes a read path needs whole entities, for example to hand them to a mapper. Then the tracking choice matters: | Choice | Effect on a large read | |---|---| | Tracking (the default) | snapshots and identity resolution; worth it only if `SaveChanges` follows | | `AsNoTracking()` | no snapshots, no identity map; a brand shared by 100 products is materialized 100 times | | `AsNoTrackingWithIdentityResolution()` | not tracked, but a stand-alone identity map de-duplicates within one query | | `UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)` on the options | every query of that context is no-tracking unless it calls `AsTracking()` | For a context that serves only the shop's catalogue reads, the context-wide default removes a whole class of accidental tracking; write paths opt back in or use their own context. ## The mistakes that undo a projection A projection only helps if EF translates it. Three patterns quietly load full entities again: 1. **Materializing first.** `(await db.Products.Where(...).ToListAsync()).Select(p => new ProductCard(...))` runs the `Select` in memory, after every column of every row has been fetched and tracked. 2. **Switching to LINQ to Objects early.** An `AsEnumerable()` before the `Select` has the same effect: everything after it runs on the client. 3. **Returning entities from the endpoint and shaping them later.** Serializing `Product` and letting the client ignore fields still pays for loading and tracking them, and can drag navigations along. Keep the order *filter, sort, page, project, materialize*, with `ToListAsync()` as the last call, so the database does the narrowing. ## How to confirm the change worked - The logged SQL lists only the card's columns. - `ChangeTracker.Entries()` is empty after the query runs. - Allocations per request drop in a profiler or a benchmark of the endpoint.

  • Is a query that returns an anonymous type always untracked?
    No. EF tracks every entity instance that appears in the result, wherever it sits. `Select(p => new { Product = p, p.Brand.Name })` tracks each `Product`. Only when the projection contains no entity instances, just scalar values or DTOs built from them, does the change tracker stay empty.
  • When would you set UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) on a context instead of calling AsNoTracking per query?
    When the context exists mainly for reads, such as a catalogue or reporting context: one default stops forgotten `AsNoTracking()` calls from costing memory. The risk is code that loads, modifies and saves; its changes are silently ignored unless that query calls `AsTracking()` or the write path uses a tracking context.

  • A Select into a DTO still tracks the DTOs, so AsNoTracking must be added as well.
  • A projection loads the whole row and then copies the needed fields in memory.
  • AsNoTracking makes EF Core fetch fewer columns from the table.
  • Projected DTOs can be edited and saved back with SaveChangesAsync.
  • An anonymous type that contains a Product entity is never tracked.