Performance & Query Optimization

AsNoTracking() for read-only queries#

The interview-questions page in this track already touches on AsNoTracking() briefly — this page goes further. By default, every entity a query returns is tracked: EF Core keeps a reference to it and snapshots its property values, so a later SaveChangesAsync() knows exactly what changed. That bookkeeping costs real memory and CPU, and it's entirely wasted for a query whose results are only ever going to be read and displayed, never modified and saved back:

var products = await db.Products
    .AsNoTracking()
    .Where(p => p.StockQuantity > 0)
    .ToListAsync();

For a query that returns the same logical entity more than once — say, a join that produces several rows referencing the same Category — plain AsNoTracking() returns a separate object instance for each occurrence. AsNoTrackingWithIdentityResolution() is the middle ground: it still skips change-tracking overhead, but ensures every occurrence of the same entity in one result set resolves to the same object instance, at a small extra cost over plain AsNoTracking().

A useful default for a larger application: AsNoTracking() on every query that's purely read-only (most GET endpoints, reports, search results), and tracked queries reserved for the specific code paths that actually call SaveChangesAsync() afterward.

Avoiding N+1 with Include, and AsSplitQuery#

The linq-queries-and-relationships page in this track already covers .Include(...) as the fix for the classic N+1 problem — one query for a list of entities, plus one more query per entity for its related data. Include collapses that into a single query with a SQL JOIN. But a single JOIN has its own failure mode once multiple one-to-many collections are included on the same query: the database returns the mathematical cross-product of both collections, duplicating the parent row's data once per combination.

// Category → many Products, Category → many Promotions.
// A single JOIN-based query multiplies rows: 10 products × 5 promotions = 50 rows returned
// for a category that logically only has 15 related records total.
var categories = await db.Categories
    .Include(c => c.Products)
    .Include(c => c.Promotions)
    .ToListAsync();

.AsSplitQuery() tells EF Core to issue one query per included collection instead of one giant JOIN, avoiding the cross-product entirely at the cost of multiple round trips:

var categories = await db.Categories
    .Include(c => c.Products)
    .Include(c => c.Promotions)
    .AsSplitQuery()
    .ToListAsync();

This is a genuine trade-off, not a strictly better default: a single JOIN is one round trip but risks returning far more duplicated data than necessary; split queries return exactly the right amount of data but pay for multiple round trips. It's worth reaching for specifically when a query includes more than one collection navigation and the duplication in a single JOIN is measurably significant.

Compiled queries for hot paths#

Every LINQ query EF Core executes is translated into SQL the first time it runs; EF Core already caches that translation internally for identical query shapes, so most applications never need to think about this. In a genuinely hot path — a query run an enormous number of times per second with an identical shape — EF.CompileAsyncQuery removes even that first-lookup cost by compiling the query once, explicitly, ahead of time:

private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Products.FirstOrDefault(p => p.Id == id));

var product = await GetProductById(db, 42);

Compiled queries are a targeted, measure-first optimization, not a default habit — reach for one only after profiling shows the query-translation step itself (not the database round trip) is a meaningful cost.

Choosing a technique#

Common mistakes#

• Reaching for compiled queries as a first optimization step before checking whether AsNoTracking(), a missing database index, or a plain Include() already explains a slow query — compiled queries solve a narrow, specific cost that's rarely the actual bottleneck.
• Adding AsSplitQuery() to every query with an Include() by default — it trades one round trip for several, which is only a net win when the single-JOIN cross-product problem it solves is actually happening.
• Calling AsNoTracking() and then mutating the returned entities and calling SaveChangesAsync() expecting it to persist — nothing is being tracked, so there's nothing for EF Core to detect as changed, and the call silently does nothing for those entities.

Interview questions#

Q: When would you use AsNoTracking(), and what does it actually skip? On any query whose results are only read and displayed, never modified and saved — a GET endpoint, a report, search results. It skips EF Core's change-tracking bookkeeping (snapshotting property values so a later SaveChanges() knows what changed), which is pure overhead when nothing is ever going to be saved back.

Q: What problem does AsSplitQuery() solve, and what's the trade-off? When a query Include()s more than one collection navigation, a single SQL JOIN returns the cross-product of both collections, duplicating the parent row's data for every combination. AsSplitQuery() issues one query per included collection instead, avoiding that duplication — at the cost of multiple database round trips instead of one.

Q: When is a compiled query (EF.CompileAsyncQuery) actually worth using? Only for a query with a fixed shape that runs an extremely high number of times, after profiling shows the LINQ-to-SQL translation step itself — not the database round trip — is a meaningful cost. EF Core already caches translations for repeated identical query shapes, so most applications never need this; it's a targeted fix for a measured hot path, not a default habit.