The N+1 Query Problem
What is the N+1 Problem?
The N+1 query problem occurs when you execute 1 query to fetch N records, then execute N additional queries to fetch related data for each record.
Example Problem
// ❌ BAD: N+1 Problem
var orders = await context.Orders.ToListAsync(); // 1 query
foreach (var order in orders) // N queries!
{
var customer = await context.Customers
.FirstAsync(c => c.Id == order.CustomerId);
Console.WriteLine($"Order {order.Id} by {customer.Name}");
}
// Result: If you have 100 orders, this executes 101 queries!
Detection
Enable SQL Logging
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
// Output shows multiple SELECT queries
// SELECT * FROM Orders
// SELECT * FROM Customers WHERE Id = 1
// SELECT * FROM Customers WHERE Id = 2
// SELECT * FROM Customers WHERE Id = 3
// ... 100 times!
Use MiniProfiler
services.AddMiniProfiler(options =>
{
options.RouteBasePath = "/profiler";
}).AddEntityFramework();
// Shows duplicate queries in UI
Solution 1: Eager Loading with Include
// ✅ GOOD: Single query with JOIN
var orders = await context.Orders
.Include(o => o.Customer)
.ToListAsync();
foreach (var order in orders)
{
Console.WriteLine($"Order {order.Id} by {order.Customer.Name}");
}
// Generated SQL:
// SELECT o.*, c.*
// FROM Orders o
// INNER JOIN Customers c ON o.CustomerId = c.Id
Multiple Levels
var orders = await context.Orders
.Include(o => o.Customer)
.Include(o => o.OrderItems)
.ThenInclude(oi => oi.Product)
.ToListAsync();
Filtered Include (EF Core 5+)
var blogs = await context.Blogs
.Include(b => b.Posts.Where(p => p.IsPublished))
.ToListAsync();
Solution 2: Projection with Select
// ✅ BEST: Only select needed data
var orderDtos = await context.Orders
.Select(o => new OrderDto
{
OrderId = o.Id,
OrderDate = o.OrderDate,
CustomerName = o.Customer.Name,
TotalAmount = o.OrderItems.Sum(oi => oi.Quantity * oi.UnitPrice)
})
.ToListAsync();
// Benefits:
// - Single query
// - Only needed columns
// - No tracking overhead
// - Less memory usage
Solution 3: Split Query (EF Core 5+)
// For multiple collections to avoid cartesian explosion
var blogs = await context.Blogs
.Include(b => b.Posts)
.Include(b => b.Tags)
.AsSplitQuery()
.ToListAsync();
// Executes 3 queries:
// SELECT * FROM Blogs
// SELECT * FROM Posts WHERE BlogId IN (...)
// SELECT * FROM Tags WHERE BlogId IN (...)
// Better than N+1, better than cartesian explosion
Solution 4: Explicit Loading
// Load parent first
var orders = await context.Orders.ToListAsync();
// Then explicitly load related data once
await context.Entry(orders.First())
.Collection(o => o.OrderItems)
.LoadAsync();
// Or load for all
await context.Orders
.Include(o => o.OrderItems)
.LoadAsync();
Real-World Scenarios
Scenario 1: API Endpoint
// ❌ BAD
[HttpGet]
public async Task<IActionResult> GetOrders()
{
var orders = await _context.Orders.ToListAsync();
var result = orders.Select(o => new
{
o.Id,
CustomerName = _context.Customers
.First(c => c.Id == o.CustomerId).Name // N+1!
});
return Ok(result);
}
// ✅ GOOD
[HttpGet]
public async Task<IActionResult> GetOrders()
{
var orders = await _context.Orders
.Select(o => new
{
o.Id,
CustomerName = o.Customer.Name
})
.ToListAsync();
return Ok(orders);
}
Scenario 2: Nested Collections
// ❌ BAD: Nested N+1
foreach (var customer in customers)
{
foreach (var order in customer.Orders) // N+1
{
foreach (var item in order.OrderItems) // N+1 again!
{
// Process
}
}
}
// ✅ GOOD
var customers = await context.Customers
.Include(c => c.Orders)
.ThenInclude(o => o.OrderItems)
.ToListAsync();
Scenario 3: Conditional Loading
// ❌ BAD
foreach (var product in products)
{
if (product.CategoryId == targetCategory)
{
var category = await context.Categories
.FindAsync(product.CategoryId); // Multiple queries
}
}
// ✅ GOOD
var products = await context.Products
.Where(p => p.CategoryId == targetCategory)
.Include(p => p.Category)
.ToListAsync();
Performance Comparison
[MemoryDiagnoser]
public class N1Benchmark
{
[Benchmark(Baseline = true)]
public async Task<int> WithN1Problem()
{
var orders = await _context.Orders.Take(100).ToListAsync();
var count = 0;
foreach (var order in orders)
{
var customer = await _context.Customers
.FirstAsync(c => c.Id == order.CustomerId);
count++;
}
return count;
}
[Benchmark]
public async Task<int> WithInclude()
{
var orders = await _context.Orders
.Include(o => o.Customer)
.Take(100)
.ToListAsync();
return orders.Count;
}
[Benchmark]
public async Task<int> WithProjection()
{
var orders = await _context.Orders
.Select(o => new { o.Id, o.Customer.Name })
.Take(100)
.ToListAsync();
return orders.Count;
}
}
// Results (100 orders):
// WithN1Problem: 2,500ms (101 queries)
// WithInclude: 150ms (1 query)
// WithProjection: 80ms (1 optimized query)
Decision Tree
Need related data?
├─ Read-only display? → Use Projection (Select)
├─ Need to modify entities? → Use Include
├─ Multiple collections? → Use AsSplitQuery
├─ Conditional loading? → Use filtered Include
└─ Large dataset? → Consider batching or pagination
Interview Questions
Q: What is the N+1 query problem?
A: Executing 1 query to fetch N records, then N additional queries to fetch related data for each record. This results in N+1 total queries instead of a single optimized query.
Q: Name three solutions to the N+1 problem.
A:
- Eager Loading: Use Include() to fetch related data in one query
- Projection: Use Select() to fetch only needed data
- Split Query: Use AsSplitQuery() for multiple collections
Q: When would you use AsSplitQuery?
A: When loading multiple collections to avoid cartesian explosion. Instead of one huge JOIN, it executes separate queries for each collection.
Q: How do you detect N+1 problems?
A: Enable SQL logging, use MiniProfiler, or use a profiling tool to see actual SQL queries being executed.