EF Core — Performance

Key Points

• Top 5 wins: AsNoTracking, projection, Include only what's needed, indexes, bulk operations.
• Identity resolution stays even with AsNoTracking, via AsNoTrackingWithIdentityResolution() if you need it.
• SaveChanges interceptors for cross-cutting (audit, soft-delete, change events).
• Compiled queries for hot paths.
• Bulk update/delete (ExecuteUpdate/ExecuteDelete) — no entity load.
• Connection pooling is at the connection-string level — see Connection Pooling & Isolation.
• Profile with MiniProfiler or EF Core's built-in logging before optimizing.

Concepts (deep dive)

Bulk operations (.NET 7+)

// Update without loading
await db.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-7))
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.IsArchived, true));

// Delete without loading
await db.Orders
    .Where(o => o.Status == OrderStatus.Cancelled)
    .ExecuteDeleteAsync();

Single SQL UPDATE/DELETE. Doesn't go through change tracker — interceptors that watch SavedChanges won't see these. Pair with explicit audit logic or use IInterceptor for raw SQL.

SaveChanges interceptors

public class AuditInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData ed, InterceptionResult<int> r, CancellationToken ct = default)
    {
        var ctx = ed.Context!;
        foreach (var entry in ctx.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added) entry.Entity.CreatedAt = DateTimeOffset.UtcNow;
            if (entry.State == EntityState.Modified) entry.Entity.UpdatedAt = DateTimeOffset.UtcNow;
        }
        return ValueTask.FromResult(r);
    }
}

builder.Services.AddDbContext<AppDb>(opt =>
    opt.UseSqlServer(connStr).AddInterceptors(new AuditInterceptor()));

Interceptors run for SaveChanges and SaveChangesAsync (not for ExecuteUpdate/ExecuteDelete).

Indexes

b.Entity<Order>(o =>
{
    o.HasIndex(x => x.CustomerId);
    o.HasIndex(x => x.CreatedAt);
    o.HasIndex(x => new { x.CustomerId, x.Status });   // composite
    o.HasIndex(x => x.Email).IsUnique();
});

Indexes are 80% of the perf game. Verify against your query patterns; add as your queries grow. See SQL Server Internals for App Devs.

Identity resolution

// Without identity resolution: distinct in-memory rows for same DB row across queries
var ordersA = db.Orders.AsNoTracking().Where(...).ToList();
var ordersB = db.Orders.AsNoTracking().Where(...).ToList();
// ordersA[0] and ordersB[0] are different objects even if same DB row

// With identity resolution: same DB row → same object
var orders = db.Orders.AsNoTrackingWithIdentityResolution().Where(...).ToList();

AsNoTrackingWithIdentityResolution is the middle ground: skip tracking but cache identity within the query. Useful when you have nested includes that share entities.

Streaming queries

await foreach (var order in db.Orders.AsNoTracking().AsAsyncEnumerable().WithCancellation(ct))
{
    Process(order);   // streamed; not buffered into memory
}

For very large result sets, stream instead of ToListAsync. Caveat: holds the connection open during enumeration.

Connection counts

Each context has one connection (lazily opened). At SaveChanges, a transaction wraps the changes. Long-lived contexts → long-lived connections → pool exhaustion under load.

Query plan caching

EF Core caches translated SQL by query shape. Two semantically-different queries that look the same to EF (e.g., a constant in code vs. a captured variable) can flip caching behavior:

const int Limit = 10;
db.Orders.Take(Limit).ToList();   // Limit baked in — single query plan

int limit = 10;
db.Orders.Take(limit).ToList();    // Parameterized — single plan with parameter

Modern EF Core parameterizes both correctly. Older versions had quirks; verify with ToQueryString.

Tracking entry overhead

Each tracked entity carries an EntityEntry with the original snapshot, current values, and metadata. ~1–2 KB per entry. Loading 100,000 tracked entities = 100–200 MB just for tracking entries. Use AsNoTracking for large reads.

Logging vs perf

opt.UseSqlServer(connStr)
   .EnableSensitiveDataLogging()    // ❌ leak parameter values to logs; dev only
   .LogTo(Console.WriteLine, LogLevel.Information);

EnableSensitiveDataLogging exposes parameter values in logs. Production: off. For high-traffic services, even Information-level logging adds up — use Warning or higher in prod.

Code: correct vs wrong

❌ Wrong: load entities just to delete

var olds = await db.Orders.Where(o => o.CreatedAt < cutoff).ToListAsync();
db.Orders.RemoveRange(olds);
await db.SaveChangesAsync();

✅ Correct: bulk

await db.Orders.Where(o => o.CreatedAt < cutoff).ExecuteDeleteAsync();

❌ Wrong: tracking on a 1M-row read

var orders = await db.Orders.ToListAsync();   // 100M+ memory in change tracker

✅ Correct: stream, no-track

await foreach (var o in db.Orders.AsNoTracking().AsAsyncEnumerable())
    Process(o);

❌ Wrong: missing index for hot query

db.Orders.Where(o => o.CustomerId == id).ToList();
// no index on CustomerId → table scan

✅ Correct: index it

b.Entity<Order>().HasIndex(o => o.CustomerId);

Design patterns for this topic

Pattern 1 — "Bulk for batch operations"

• Intent: single SQL, no entity load.

Pattern 2 — "AsNoTracking by default; explicit tracking for writes"

• Intent: minimize change-tracker overhead.

Pattern 3 — "Audit / soft-delete via SaveChanges interceptor"

• Intent: consistent cross-cutting.

Pattern 4 — "Stream large results"

• Intent: memory-bounded processing.

Pattern 5 — "Compiled queries for hottest paths"

• Intent: skip parser/translator on every call.

Pros & cons / trade-offs

When to use / when to avoid

• Use bulk operations for large updates/deletes.
• Use interceptors for app-wide audit / soft-delete.
• Use streaming for very large reads.
• Avoid EnableSensitiveDataLogging in production.
• Avoid loading 100k+ tracked entities — use AsNoTracking.

Interview Q&A

Q1. What's the cost of change tracking? ~1-2 KB per entity for tracking entries. For 100k entities, that's 100-200 MB. Plus CPU for change detection on SaveChanges.

Q2. When does ExecuteUpdate/ExecuteDelete not fire interceptors? The bulk variants bypass SaveChanges flow entirely — they're direct SQL. Audit interceptors that hook SavingChanges won't see them.

Q3. Difference between AsNoTracking and AsNoTrackingWithIdentityResolution? Both skip tracking. The latter still maintains identity within a query (same DB row → same in-memory object). Useful with shared includes.

Q4. How do you stream results? AsAsyncEnumerable() + await foreach. Doesn't buffer to a list.

Q5. What's a SaveChangesInterceptor? Hook into SavingChanges / SavedChanges events. Use for audit (timestamps), soft-delete (set IsDeleted), domain event dispatch.

Q6. What does EnableSensitiveDataLogging do? Logs parameter values in EF Core logs. Convenient for dev; security risk in prod (PII leak).

Q7. How do indexes affect EF Core? EF doesn't use them — the database does. EF generates SQL; the optimizer picks indexes. Add HasIndex so the migration creates them.

Q8. What's compiled queries' real win? Skip the LINQ → SQL translation step. For queries called millions of times, this is measurable. EF Core 8+ auto-compiles common cases — manual only for the hottest.

Q9. Why might a tracked query be slow even with few rows? Change-tracker setup + identity-map updates + relationship fixup. For 10 entities, fixed overhead (microseconds) outweighs row count.

Q10. How do you debug a slow EF query? 1) ToQueryString() to see SQL. 2) Run in SSMS / psql with execution plan. 3) Check indexes. 4) Check N+1. 5) Project instead of full entity if perf-critical.

Gotchas / common mistakes

• ⚠️ Tracking on huge read — memory blowup.
• ⚠️ Bulk ops bypass interceptors — silent audit gap.
• ⚠️ Sensitive data in prod logs — PII leak.
• ⚠️ Missing indexes for hot queries — table scans.
• ⚠️ Streaming leaving connection open — connection-pool exhaustion.
• ⚠️ Include after Select — error or unintended client-eval.

Further reading

• EF Core performance
• SaveChangesInterceptor
• Bulk operations
• Compiled queries