Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 41 ยท Performance and Concurrency

Compiled Queries, Split Queries and DbContext Pooling

Learn EF Core performance features for hot paths: compiled queries with EF.CompileAsyncQuery, split queries to avoid cartesian explosion, DbContext pooling and context factories, with runnable stand-ins.

Once you have removed N+1 queries and fetched only the data you need (query performance), a few more features can squeeze out extra speed or avoid specific traps. They matter on hot paths: code that runs thousands of times per second. Do not apply them everywhere by default; measure first.

EF Core snippets here are read-only. The runnable blocks are stand-ins that demonstrate the mechanism behind each feature.

How EF Core runs a query

Every time you run a LINQ query, EF Core must turn the expression tree into SQL. It caches this work, so repeated identical-shaped queries are cheap, but each execution still pays small costs: building the expression tree, computing a cache key, looking up the cache. A compiled query lets you pay that once and skip it afterwards.

flowchart LR
    A["LINQ expression"] --> B["Cache lookup by shape"]
    B -- miss --> C["Translate to SQL (expensive)"]
    C --> D["Store in cache"]
    B -- hit --> E["Reuse SQL + parameters"]
    D --> E
    E --> F["Execute"]

Compiled queries skip the first two boxes on every call.

Compiled queries

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

// usage
var product = await GetProductById(db, 42);

Define it once (a static readonly field) and call the delegate. For queries returning many rows, EF.CompileAsyncQuery returns IAsyncEnumerable<T>:

C#
private static readonly Func<AppDbContext, string, IAsyncEnumerable<Product>> ByCategory =
    EF.CompileAsyncQuery((AppDbContext db, string category) =>
        db.Products.Where(p => p.Category == category));

await foreach (var p in ByCategory(db, "Books")) { /* ... */ }

When it helps: tiny queries run very often (lookups by key), where framework overhead is a big share. When it does not: queries dominated by database time, which is most of them. Typical gains are modest, which is why this is an optimization to measure, not a habit.

Limits: the query shape is fixed at compile time, so you cannot append Where clauses conditionally; parameters are passed as delegate arguments.

The stand-in below demonstrates the principle with a deliberately "expensive" translation step that is cached:

C# Stand-in: translate once, reuse many times

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Split queries

Remember the cartesian explosion from eager loading: Include of two collections joins them and multiplies rows. Split queries tell EF Core to send one SQL statement per included collection and assemble the graph in memory:

C#
var blogs = await db.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .AsSplitQuery()
    .ToListAsync();
// SELECT ... FROM Blogs ORDER BY Id
// SELECT ... FROM Posts INNER JOIN Blogs ... ORDER BY Blogs.Id
// SELECT ... FROM Tags  INNER JOIN Blogs ... ORDER BY Blogs.Id

Or make it the default for a context and opt out where needed:

C#
options.UseSqlServer(cs, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
// per query:   .AsSingleQuery()

Trade-offs:

Single query Split query
Round trips 1 1 + number of collections
Data consistency One snapshot (one statement) Data could change between statements (use a transaction/snapshot isolation if it matters)
Rows transferred Can explode Linear
Ordering Not needed Needs deterministic ordering

EF Core warns you in the log when it sees multiple collection includes without a splitting choice; read that warning.

DbContext pooling

Creating a DbContext is cheap but not free: the options, services and internal objects are set up each time. In a high-throughput web app (many short requests) a context pool reuses instances, resetting their state between uses:

C#
builder.Services.AddDbContextPool<AppDbContext>(o => o.UseSqlServer(cs), poolSize: 256);

Rules for pooling:

  • Context state is reset, but your own fields on the context are not. Do not keep per-request data in fields of a pooled context (such as the current tenant); pass it through scoped services or use a context-per-request factory with its own state.
  • The pool is a speed-up (a few percent up to noticeably more under load), not a requirement.
  • A context is still not thread-safe: each request gets its own instance from the pool.
C# Stand-in: a tiny object pool

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

IDbContextFactory

Where dependency injection does not give you a natural scope (Blazor Server components, background workers, parallel tasks), inject a factory and create short-lived contexts yourself:

C#
builder.Services.AddDbContextFactory<AppDbContext>(o => o.UseSqlServer(cs));

public class ReportService(IDbContextFactory<AppDbContext> factory)
{
    public async Task<int> CountAsync()
    {
        await using var db = await factory.CreateDbContextAsync();
        return await db.Orders.CountAsync();
    }
}

A pooled factory exists too (AddPooledDbContextFactory). See DbContext in ASP.NET Core for lifetimes.

Other knobs

  • Batch size for SaveChanges: o.MaxBatchSize(100).
  • Command timeout: o.CommandTimeout(30); fix slow queries rather than raising it.
  • Value comparers and snapshots: large mutable properties (byte arrays, JSON) make DetectChanges expensive; consider AsNoTracking or change-tracking proxies.
  • Query tags: .TagWith("Orders/Index: open orders") puts a comment in the SQL so slow-query logs point back to code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a compiled query avoid on each call?

    Compiled queries translate once; each call reuses the result but still executes against the database.
  2. 2. What problem does AsSplitQuery() solve?

    It sends one statement per collection instead of one giant join.
  3. 3. What is a downside of split queries?

    Several statements mean several round trips and no single-statement consistency.
  4. 4. What must you avoid when using a pooled DbContext?

    Only EF Core's own state is reset between uses; your custom fields would leak into the next request.
  5. 5. Why use IDbContextFactory?

    The factory creates and disposes contexts on demand.

Summary

  • Compiled queries (EF.CompileAsyncQuery) skip repeated translation for hot, tiny queries; measure before using them.
  • AsSplitQuery avoids cartesian explosion at the cost of more round trips and weaker consistency.
  • AddDbContextPool reuses context instances; keep custom state out of pooled contexts.
  • IDbContextFactory is for places without a request scope; TagWith helps trace slow SQL back to code.

Next: Optimistic concurrency