Entity Framework Core Performance Tuning for High-Traffic Applications

Overview

Proven EF Core optimisations: split queries, projection, compiled queries, and avoiding N+1 pitfalls.

The N+1 Query Problem

The N+1 problem is the most common EF Core performance killer. It occurs when loading a collection with related data using lazy loading or separate queries for each parent.

The fix is eager loading with Include(), but be careful—cartesian explosion occurs when you include multiple collection navigations, multiplying result rows.

// BAD: N+1 queries
var orders = await ctx.Orders.ToListAsync();
foreach (var order in orders)
    var items = await ctx.OrderItems.Where(i => i.OrderId == order.Id).ToListAsync();

// GOOD: Single query with Include
var orders = await ctx.Orders.Include(o => o.Items).ToListAsync();

// BETTER: Use AsSplitQuery for multiple collections
var orders = await ctx.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .ToListAsync();

Projection Is Your Best Friend

Never load full entities when you only need a few columns. Projection via Select() generates an optimised SQL SELECT with only the required columns—reducing data transfer and memory pressure dramatically.

// BAD: Loads entire entity graph
var names = await ctx.Products.ToListAsync();
return names.Select(p => p.Name);

// GOOD: Projects only needed fields
var names = await ctx.Products.Select(p => p.Name).ToListAsync();

Compiled Queries for Hot Paths

LINQ queries are compiled to SQL on every execution. For high-traffic endpoints queried thousands of times per second, compiled queries cache the SQL translation—saving 2-5ms per call.

We apply compiled queries to the 20 most-called endpoints in production systems, typically seeing 15-30% reduction in p99 latency.

• Use compiled queries for hot paths
• Use AsSplitQuery for multiple collection includes
• Always project with Select() in list endpoints
• Use AsNoTracking() for read-only queries
• Index columns used in Where() and OrderBy()

Key Takeaways

• Always profile before optimising—use Azure Monitor or MiniProfiler
• AsSplitQuery prevents cartesian explosion in complex includes
• Projection can reduce query time by 10-100x on wide tables
• Compiled queries eliminate repeated LINQ translation overhead
• Connection pooling is critical under high concurrency