One of the most common performance killers in Entity Framework applications
// ❌ BAD - Creates N+1 queries
var products = await _context.Products
.Where(p => p.IsActive)
.Take(50)
.ToListAsync(); // 1st query
// Then for EACH product...
foreach (var product in products)
{
// Additional query per product!
var category = await _context.Categories
.FirstAsync(c => c.Id == product.CategoryId);
// 50 more queries = 51 total!
}// ✅ GOOD - Single query with JOIN
var products = await _context.Products
.Include(p => p.Category) // Eager load
.Where(p => p.IsActive)
.Take(50)
.ToListAsync(); // Single query with JOIN
// Category data already loaded!
foreach (var product in products)
{
// No additional query needed
var categoryName = product.Category.Name;
}By using Include() instead of lazy loading
The N+1 problem occurs when you fetch a list of N entities, then for each entity, you make another query to fetch related data. This results in:
Use eager loading with .Include() to fetch related
data in a single query using SQL JOINs.
| Strategy | When to Use |
|---|---|
.Include() |
Always need related data |
.ThenInclude() |
Multi-level relationships |
.Select() |
Need specific properties only |
| Explicit Loading | Conditionally load later |
// Load product with category
var products = await _context.Products
.Include(p => p.Category)
.Where(p => p.IsActive)
.ToListAsync();// Load multiple related entities
var products = await _context.Products
.Include(p => p.Category)
.Include(p => p.Reviews)
.Include(p => p.Supplier)
.ToListAsync();// Load nested relationships
var products = await _context.Products
.Include(p => p.Category)
.ThenInclude(c => c.Department)
.Include(p => p.Reviews)
.ThenInclude(r => r.User)
.ToListAsync();// Use Select for specific data
var products = await _context.Products
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
CategoryName = p.Category.Name
})
.ToListAsync();// In Program.cs
options.LogTo(
Console.WriteLine,
LogLevel.Information);Watch for multiple similar queries
// Install package
dotnet add package MiniProfiler.AspNetCore.Mvc
// Shows all SQL queries
// in development UIVisual query profiling tool
-- Monitor queries in SQL Server
-- Look for patterns of:
SELECT * FROM Products
SELECT * FROM Categories WHERE Id = 1
SELECT * FROM Categories WHERE Id = 2
SELECT * FROM Categories WHERE Id = 3Database-level monitoring
public async Task<IActionResult> ProductList()
{
// Get products
var products = await _context.Products
.Where(p => p.IsActive)
.Take(20)
.ToListAsync(); // 1 query
// Build view models
var viewModels = new List<ProductViewModel>();
foreach (var product in products)
{
// Query 2-21: Get category
var category = await _context.Categories
.FindAsync(product.CategoryId);
// Query 22-41: Get reviews
var reviews = await _context.Reviews
.Where(r => r.ProductId == product.Id)
.ToListAsync();
viewModels.Add(new ProductViewModel
{
Product = product,
CategoryName = category.Name,
ReviewCount = reviews.Count
});
}
return View(viewModels);
}
// Result: 41 queries for 20 products!Performance: 500-1000ms+ on production
public async Task<IActionResult> ProductList()
{
// Single query with JOINs
var viewModels = await _context.Products
.Include(p => p.Category)
.Include(p => p.Reviews)
.Where(p => p.IsActive)
.Select(p => new ProductViewModel
{
Product = p,
CategoryName = p.Category.Name,
ReviewCount = p.Reviews.Count
})
.Take(20)
.AsNoTracking()
.ToListAsync(); // 1 query total!
return View(viewModels);
}
// Result: 1 optimized queryPerformance: 20-50ms on production
The N+1 problem is one of the most common performance issues in ORM-based applications. Understanding and avoiding it can lead to 10-100x performance improvements in real-world scenarios. Always profile your queries in development and use tools like MiniProfiler, Entity Framework logging, or SQL Server Profiler to catch these issues before they reach production.