EF Core Performance Optimization

Introduction

When ShopNest News serves millions of article views, EF Core defaults aren't enough. This advanced lesson covers tracking, compiled queries, keyset pagination, split queries, indexing, and bulk operations.

After this article you will

• Apply AsNoTracking and compiled queries
• Implement keyset vs offset pagination
• Use AsSplitQuery to tame cartesian explosion
• Log and fix slow queries
• Understand bulk extension options

Prerequisites

• Article 19 — EF Core Repository Pattern and Unit of Work
• ShopNest DbContext with Products/Orders from Articles 13–15
• SQL Server or LocalDB configured

Concept deep-dive

// Compiled query — parse once
private static readonly Func<ShopNestDbContext, int, Task<Article?>> _getArticle
    = EF.CompileAsyncQuery((ShopNestDbContext db, int id) =>
        db.Articles.AsNoTracking().FirstOrDefault(a => a.Id == id));

// Keyset pagination (fast at scale)
var articles = await _db.Articles.AsNoTracking()
    .Where(a => a.Id > lastId)
    .OrderBy(a => a.Id)
    .Take(20)
    .ToListAsync();

// Split query — avoid huge JOIN
var posts = await _db.Posts
    .Include(p => p.Comments)
    .Include(p => p.Tags)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync();

Index strategy: filter columns in WHERE/ORDER BY. Connection pooling: default in SqlClient — don't disable. EFCore.BulkExtensions for mass inserts in migrations/imports.

Hands-on — ShopNest High-Traffic News Portal

1. Article list API: keyset pagination with cursor query param.
2. Enable LogTo(Console.WriteLine, LogLevel.Information) in Development.
3. Add composite index on (PublishedAt, CategoryId) via migration.
4. Compare SQL row count: Include all vs AsSplitQuery.

Common errors & best practices

• Tracking 10k rows for a report — always AsNoTracking.
• Offset pagination page 5000 — SQL scans skipped rows; use keyset.
• Missing index on foreign keys — join performance dies.

Interview questions

Q: AsNoTracking when?
A: Any read-only query — lists, exports, API GET.

Q: Keyset vs offset pagination?
A: Keyset uses WHERE id > lastId — constant time; offset Skip(N) scans N rows.

Q: AsSplitQuery?
A: Multiple SQL queries instead of one giant JOIN — fixes cartesian explosion with many Includes.

Summary

• Read paths: AsNoTracking + DTO projection
• Keyset pagination for high-traffic news feeds
• Indexes and split queries fix real production bottlenecks
• Compiled queries for hot paths hit millions of times

Previous: EF Core Repository Pattern and Unit of Work
Next: Database First Approach with EF Core

FAQ