EF Core Performance — N+1 Queries, Projections and Compiled Queries

EF Core performance problems fall into predictable patterns: N+1 queries (one query per item in a loop), over-fetching (loading full entities when only a few columns are needed), and missing projections (no Select() to limit what is retrieved). EF Core 7+ adds ExecuteUpdateAsync and ExecuteDeleteAsync — bulk operations that generate a single UPDATE or DELETE SQL statement without loading entities first, equivalent to UPDATE ... WHERE ... in raw SQL.

N+1 Detection and Bulk Operations #

// ── N+1 query — the most common EF Core performance problem ───────────────
// ❌ WRONG: 1 query for posts + 1 query per post for author = N+1 queries
var posts = await _db.Posts.Where(p => p.IsPublished).ToListAsync(ct);
foreach (var post in posts)
{
    // Each access to post.Author triggers a separate SQL query!
    Console.WriteLine(post.Author!.DisplayName);  // N additional queries
}

// ✅ CORRECT: Include() loads all authors in a single JOIN query
var posts = await _db.Posts
    .AsNoTracking()
    .Include(p => p.Author)
    .Where(p => p.IsPublished)
    .ToListAsync(ct);
// Single query: SELECT p.*, u.* FROM Posts p JOIN Users u ON u.Id = p.AuthorId

// ✅ BETTER: Projection avoids loading full Author entity
var posts = await _db.Posts
    .AsNoTracking()
    .Where(p => p.IsPublished)
    .Select(p => new { p.Title, AuthorName = p.Author!.DisplayName })
    .ToListAsync(ct);
// Single query with JOIN, returns only 2 columns

// ── ExecuteUpdateAsync — bulk update without loading entities ─────────────
// ❌ WRONG: Load all draft posts, set UpdatedAt, then save (N entities loaded)
var drafts = await _db.Posts.Where(p => p.Status == "draft").ToListAsync(ct);
drafts.ForEach(p => p.UpdatedAt = DateTime.UtcNow);
await _db.SaveChangesAsync(ct);  // N UPDATE statements

// ✅ CORRECT: Single UPDATE statement (EF Core 7+)
await _db.Posts
    .Where(p => p.Status == "draft")
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow),
    ct);
// Generated SQL: UPDATE Posts SET UpdatedAt = @p0 WHERE Status = 'draft'

// ── ExecuteDeleteAsync — bulk delete without loading entities ─────────────
await _db.Comments
    .Where(c => !c.IsApproved
             && c.CreatedAt < DateTime.UtcNow.AddDays(-30))
    .ExecuteDeleteAsync(ct);
// Generated SQL: DELETE FROM Comments WHERE IsApproved = 0 AND CreatedAt < @p0

// ── Compiled queries — eliminate LINQ-to-SQL translation overhead ─────────
// EF Core 8+ — for hot paths executed thousands of times per second:
private static readonly Func<AppDbContext, string, Task<Post?>> GetBySlugQuery
    = EF.CompileAsyncQuery((AppDbContext db, string slug) =>
        db.Posts
          .AsNoTracking()
          .Include(p => p.Author)
          .FirstOrDefault(p => p.Slug == slug && p.IsPublished));

// Usage:
var post = await GetBySlugQuery(_db, slug);

Common Mistakes #

Mistake 1 — Lazy loading in a loop (N+1 queries) #

❌ Wrong — accessing navigation properties in a foreach without Include(); 1 query per item.

✅ Correct — use Include() for eager loading, or Select() projection to inline the data.

Mistake 2 — Loading full entities for bulk updates (N SQL statements) #

❌ Wrong — load 500 entities, set one property, SaveChanges → 500 UPDATE statements.

✅ Correct — ExecuteUpdateAsync → 1 UPDATE statement regardless of affected rows.