Entity Framework Core: Performance and Production Gotchas

Introduction

EF Core questions are common in .NET interviews because everyone uses it - and everyone has performance war stories. This post covers what interviewers actually care about: can you write performant queries, and do you understand what's happening under the hood?

---

EF Core Performance — What Actually Matters

TL;DR

AsNoTracking() for read-only queries, avoid the N+1 problem with .Include(), use compiled queries for hot paths, and know when to drop to raw SQL.

The Real Explanation

// 1. AsNoTracking for read-only queries
var users = await dbContext.Users
    .AsNoTracking()  // ~30% faster for read-only
    .Where(u => u.IsActive)
    .ToListAsync();

// 2. Avoiding N+1 with Include (Eager Loading)
// Bad: N+1 queries
var orders = await dbContext.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // Query per order!
}

// Good: Single query with Include
var orders = await dbContext.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToListAsync();

// 3. Select only what you need (Projection)
var summary = await dbContext.Orders
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        Total = o.OrderItems.Sum(i => i.Quantity * i.UnitPrice)
    })
    .ToListAsync();

// 4. Compiled queries for hot paths
private static readonly Func<AppDbContext, int, Task<User?>> GetUserById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Users.FirstOrDefault(u => u.Id == id));

// Usage - no query compilation overhead on each call
var user = await GetUserById(dbContext, userId);

The projection pattern (selecting into DTOs) is my go-to optimization. Instead of loading full entities with all their navigation properties, you get exactly the data you need. I've seen queries go from 500ms to 20ms just by switching from Include() chains to targeted projections.

The Gotcha

// Split queries can cause data inconsistency
var orders = await dbContext.Orders
    .Include(o => o.OrderItems)
    .AsSplitQuery()  // Generates multiple queries
    .ToListAsync();
// Problem: If data changes between queries, you get inconsistent results

// Cartesian explosion with multiple Includes
var orders = await dbContext.Orders
    .Include(o => o.OrderItems)      // 10 items per order
    .Include(o => o.Shipments)       // 3 shipments per order
    .Include(o => o.Payments)        // 2 payments per order
    .ToListAsync();
// Result set: 10 × 3 × 2 = 60 rows per order!
// With 100 orders, you're fetching 6,000 rows

// Fix: Split query OR separate queries OR projections

The tracking query gotcha:

// This doesn't update the database!
var user = await dbContext.Users
    .AsNoTracking()
    .FirstAsync(u => u.Id == 1);

user.Name = "New Name";
await dbContext.SaveChangesAsync();  // Nothing saved!

// Because AsNoTracking means EF doesn't watch for changes

Interview Tip

Real-world EF Core performance issues are almost always: wrong query patterns (N+1), missing indexes, or tracking overhead. Mention you'd use logging to see generated SQL.

---

Efficient Querying Patterns

Pagination Done Right

// Bad: Skip/Take without ordering is non-deterministic
var page = await dbContext.Products
    .Skip(20)
    .Take(10)
    .ToListAsync();  // Order not guaranteed!

// Good: Keyset pagination for large datasets
var products = await dbContext.Products
    .Where(p => p.Id > lastSeenId)  // Keyset - uses index
    .OrderBy(p => p.Id)
    .Take(10)
    .ToListAsync();

// Better for smaller datasets: Offset pagination with explicit order
var products = await dbContext.Products
    .OrderBy(p => p.CreatedAt)
    .ThenBy(p => p.Id)  // Tiebreaker for determinism
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Bulk Operations

// EF Core 7+ ExecuteUpdate/ExecuteDelete
// Old way: Load all entities, modify, save
var users = await dbContext.Users
    .Where(u => u.LastLoginDate < cutoffDate)
    .ToListAsync();
foreach (var user in users)
    user.IsActive = false;
await dbContext.SaveChangesAsync();  // N update queries!

// New way: Single UPDATE statement
await dbContext.Users
    .Where(u => u.LastLoginDate < cutoffDate)
    .ExecuteUpdateAsync(u => u.SetProperty(x => x.IsActive, false));

// Single DELETE statement
await dbContext.Users
    .Where(u => u.IsDeleted && u.DeletedAt < cutoffDate)
    .ExecuteDeleteAsync();

I used ExecuteUpdateAsync to update 50,000 user records that were previously taking 10 minutes with the load-modify-save pattern. It completed in 2 seconds.

---

EF Core Migrations — The Production Gotchas

TL;DR

Migrations are easy in dev, tricky in production. Know how to handle data migrations, rollbacks, and team conflicts.

The Real Explanation

// Basic workflow
// dotnet ef migrations add AddUserEmail
// dotnet ef database update

// Migration with data transformation
public partial class AddUserEmail : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Email",
            table: "Users",
            type: "nvarchar(256)",
            nullable: true);  // Nullable first!

        // Data migration
        migrationBuilder.Sql(@"
            UPDATE Users
            SET Email = LOWER(FirstName) + '.' + LOWER(LastName) + '@company.com'
            WHERE Email IS NULL");

        // Now make it required
        migrationBuilder.AlterColumn<string>(
            name: "Email",
            table: "Users",
            type: "nvarchar(256)",
            nullable: false,
            defaultValue: "");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "Email", table: "Users");
    }
}

The Gotcha

// Team conflict: Two developers create migrations from same base
// Developer A: Add_UserEmail
// Developer B: Add_UserPhone

// Both have same "previous migration" in snapshot
// When merged, you get conflicts in the model snapshot

// Fix: After merge, recreate the later migration
// dotnet ef migrations remove
// dotnet ef migrations add Add_UserPhone

// DON'T do this in production
public void Configure(IApplicationBuilder app)
{
    using var scope = app.ApplicationServices.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    db.Database.Migrate();  // Race conditions, blocks startup, no rollback
}

// Better: Separate migration step in CI/CD pipeline
// Or use EF Bundles:
// dotnet ef migrations bundle -o efbundle
// ./efbundle --connection "connection-string"

Zero-Downtime Migration Pattern

For columns that need to go from nullable to required:

1. Migration 1: Add column as nullable
2. Deploy: Code writes to new column, reads from old
3. Migration 2: Backfill data
4. Deploy: Code reads from new column
5. Migration 3: Make column required, drop old column

---

Global Query Filters

Useful for soft-delete and multi-tenancy:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Soft delete filter - automatically excludes deleted records
    modelBuilder.Entity<User>()
        .HasQueryFilter(u => !u.IsDeleted);

    // Multi-tenant filter
    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => o.TenantId == _tenantService.CurrentTenantId);
}

// Bypass when needed
var allUsers = await dbContext.Users
    .IgnoreQueryFilters()
    .ToListAsync();

---

Interview Questions

Q: How do you diagnose slow EF Core queries?

Answer:

1. Enable logging to see generated SQL:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

2. Look for N+1 patterns (multiple similar queries)
3. Check for missing AsNoTracking() on read-only queries
4. Examine cartesian explosion from multiple Include()
5. Use SQL Server Query Store or explain plans

Q: When would you use raw SQL over LINQ?

Answer:

• Complex queries that LINQ generates poorly
• Performance-critical paths where you need specific query plans
• Database-specific features (window functions, CTEs)
• Bulk operations before EF Core 7

var users = await dbContext.Users
    .FromSqlRaw(@"
        SELECT * FROM Users
        WHERE SOUNDEX(LastName) = SOUNDEX({0})", searchName)
    .ToListAsync();

Q: Explain change tracking and when to disable it.

Answer: EF Core tracks changes to entities loaded from the database. On SaveChanges(), it compares current values to original values and generates appropriate SQL.

Disable with AsNoTracking() when:

• Read-only queries (reports, listings)
• High-volume reads where you won't modify data
• You're immediately projecting to DTOs

---

Summary

Optimization	When to Use	Impact
AsNoTracking	Read-only queries	30% faster
Projections	When you don't need full entities	50-90% faster
Compiled Queries	Hot paths called frequently	Removes compilation overhead
Split Queries	Many Includes causing cartesian explosion	Reduces data transfer
ExecuteUpdate/Delete	Bulk operations	100x+ faster

Next up: Azure Integration Patterns - Service Bus, Azure Functions, and production gotchas.

---

Part 4 of the C# Interview Prep series.