Overview
Understanding query execution, database round-trips, and change tracking overhead is crucial for building performant data access layers. Most performance issues stem from unnecessary queries or loading too much data.
Core Concepts
Understanding the N+1 Problem
public class Category
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public ICollection<Product> Products { get; set; } = new List<Product>();
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public int CategoryId { get; set; }
public Category Category { get; set; } = null!;
}
// ❌ N+1 Problem - Multiple Queries
public async Task<List<string>> GetProductsWithCategoriesBad()
{
// Query 1: Get all products
var products = await _context.Products.ToListAsync();
// SQL: SELECT * FROM Products
var result = new List<string>();
foreach (var product in products)
{
// Query 2, 3, 4, ... N: Get category for each product
result.Add($"{product.Name} - {product.Category.Name}");
// SQL: SELECT * FROM Categories WHERE Id = @p0 (repeated N times!)
}
return result;
// Total queries: 1 + N (where N = number of products)
// For 100 products: 101 database round-trips!
}
// ✅ Solution 1: Include (Eager Loading)
public async Task<List<string>> GetProductsWithCategoriesInclude()
{
var products = await _context.Products
.Include(p => p.Category) // JOIN in single query
.ToListAsync();
// SQL: SELECT p.*, c.* FROM Products p INNER JOIN Categories c ON p.CategoryId = c.Id
return products.Select(p => $"{p.Name} - {p.Category.Name}").ToList();
// Total queries: 1
}
// ✅ Solution 2: Select Projection (Best Performance)
public async Task<List<string>> GetProductsWithCategoriesSelect()
{
return await _context.Products
.Select(p => $"{p.Name} - {p.Category.Name}")
.ToListAsync();
// SQL: SELECT p.Name, c.Name FROM Products p INNER JOIN Categories c ON p.CategoryId = c.Id
// Only loads needed columns, no tracking overhead
// Total queries: 1
}
AsNoTracking for Read-Only Queries
public class PerformanceComparison
{
// ❌ With Tracking (Default) - Slower, More Memory
public async Task<List<Product>> GetProductsTracked()
{
var products = await _context.Products.ToListAsync();
// EF Core:
// 1. Loads all columns into memory
// 2. Creates change tracking snapshots for each entity
// 3. Monitors for property changes
// 4. Maintains identity map (ensures same entity = same object)
return products;
// Memory: ~2x entity size (entity + snapshot)
// CPU: Change detection overhead
}
// ✅ AsNoTracking - Faster, Less Memory
public async Task<List<Product>> GetProductsNoTracking()
{
var products = await _context.Products
.AsNoTracking()
.ToListAsync();
// EF Core:
// 1. Loads all columns into memory
// 2. No snapshots created
// 3. No change detection
// 4. No identity map
return products;
// Memory: ~1x entity size
// CPU: Minimal overhead
// ~30-40% faster for large result sets
}
// ✅✅ AsNoTracking + Select Projection (Best)
public async Task<List<ProductDto>> GetProductsProjection()
{
return await _context.Products
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
Price = p.Price
})
.ToListAsync();
// Automatically no tracking (projection to non-entity type)
// Only loads needed columns
// Smallest memory footprint
// Fastest option
}
}
Compiled Queries
public class ProductRepository
{
private readonly AppDbContext _context;
// Define compiled query as static field
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductByIdCompiled =
EF.CompileAsyncQuery(
(AppDbContext context, int id) =>
context.Products
.Include(p => p.Category)
.FirstOrDefault(p => p.Id == id));
private static readonly Func<AppDbContext, decimal, IAsyncEnumerable<Product>> GetProductsByMinPriceCompiled =
EF.CompileAsyncQuery(
(AppDbContext context, decimal minPrice) =>
context.Products
.Where(p => p.Price >= minPrice)
.OrderBy(p => p.Name));
public ProductRepository(AppDbContext context)
{
_context = context;
}
// ❌ Regular Query - Recompiled Every Time
public async Task<Product?> GetByIdRegular(int id)
{
return await _context.Products
.Include(p => p.Category)
.FirstOrDefaultAsync(p => p.Id == id);
// EF Core: Parse LINQ → Translate to SQL → Cache (but still overhead)
}
// ✅ Compiled Query - Pre-compiled
public async Task<Product?> GetByIdCompiled(int id)
{
return await GetProductByIdCompiled(_context, id);
// Pre-compiled expression tree, direct SQL execution
// ~50% faster for hot paths (frequently called queries)
}
public async Task<List<Product>> GetByMinPriceCompiled(decimal minPrice)
{
var results = new List<Product>();
await foreach (var product in GetProductsByMinPriceCompiled(_context, minPrice))
{
results.Add(product);
}
return results;
}
}
// When to use compiled queries:
// ✅ Hot path queries (called very frequently)
// ✅ Simple, parameterized queries
// ✅ Known at compile time
// ❌ Complex dynamic queries
// ❌ Rarely called queries
Batch Operations
public class BatchOperations
{
private readonly AppDbContext _context;
// ❌ Individual SaveChanges - Multiple Round-Trips
public async Task AddProductsBad(List<Product> products)
{
foreach (var product in products)
{
_context.Products.Add(product);
await _context.SaveChangesAsync(); // Database round-trip per product!
}
// 100 products = 100 database round-trips
}
// ✅ Batched SaveChanges - Single Transaction
public async Task AddProductsGood(List<Product> products)
{
_context.Products.AddRange(products);
await _context.SaveChangesAsync(); // Single transaction
// 100 products = 1 database transaction with batched INSERT statements
// EF Core 8+ automatically batches into optimal chunks
}
// ✅ Bulk Update/Delete (EF Core 7+)
public async Task UpdatePricesBulk(decimal multiplier)
{
// Old way: Load → Modify → Save
var products = await _context.Products.ToListAsync(); // Query 1
foreach (var product in products)
{
product.Price *= multiplier;
}
await _context.SaveChangesAsync(); // Multiple UPDATEs
// New way: ExecuteUpdate (set-based operation)
await _context.Products
.Where(p => p.Price > 0)
.ExecuteUpdateAsync(setters =>
setters.SetProperty(p => p.Price, p => p.Price * multiplier));
// Single UPDATE statement, no loading entities
// SQL: UPDATE Products SET Price = Price * @multiplier WHERE Price > 0
}
public async Task DeleteOldProducts(DateTime cutoffDate)
{
// ExecuteDelete - set-based delete
await _context.Products
.Where(p => p.CreatedAt < cutoffDate)
.ExecuteDeleteAsync();
// Single DELETE statement
// SQL: DELETE FROM Products WHERE CreatedAt < @cutoffDate
}
}
Bad vs Good Examples
Bad: Loading Entire Entities for Simple Checks
// Bad - loads all columns
public async Task<bool> ProductExistsBad(int id)
{
var product = await _context.Products.FindAsync(id);
return product != null;
// Loads entire entity from database
}
Good: Use AnyAsync
// Good - just checks existence
public async Task<bool> ProductExistsGood(int id)
{
return await _context.Products.AnyAsync(p => p.Id == id);
// SQL: SELECT CASE WHEN EXISTS(SELECT 1 FROM Products WHERE Id = @id) THEN 1 ELSE 0 END
}
Bad: Multiple Queries for Count and Data
// Bad - two separate queries
public async Task<PagedResult<Product>> GetPagedProductsBad(int page, int pageSize)
{
var total = await _context.Products.CountAsync(); // Query 1
var products = await _context.Products
.Skip((page - 1) * pageSize)
.Take(pageSize)
.ToListAsync(); // Query 2
return new PagedResult<Product> { Total = total, Items = products };
}
Good: GroupJoin or Multiple Queries Only When Needed
// Good - if you need both count and data
public async Task<PagedResult<Product>> GetPagedProductsGood(int page, int pageSize)
{
// For most cases, you don't need exact count
var products = await _context.Products
.Skip((page - 1) * pageSize)
.Take(pageSize + 1) // Take one extra
.ToListAsync();
var hasMore = products.Count > pageSize;
if (hasMore)
products = products.Take(pageSize).ToList();
return new PagedResult<Product>
{
Items = products,
HasMore = hasMore
// No exact count, but client knows if there's more
};
}
Interview Tips
Tip 1: The N+1 problem is the #1 performance issue in EF Core. Always explain it with concrete examples showing query count.
Tip 2: Mention that Select projections are faster than Include + AsNoTracking because they load fewer columns and have no tracking overhead.
Tip 3: Know that EF Core 7+ has ExecuteUpdate/ExecuteDelete for set-based operations without loading entities.
Common Interview Questions
-
What is the N+1 problem and how do you prevent it?
- Loading related data in a loop causes N additional queries. Prevent with Include (eager loading) or Select projection. Example: 100 products with categories = 101 queries vs 1 query.
-
When should you use AsNoTracking?
- Always use for read-only queries (GET APIs, reports, DTOs). Tracking adds 30-40% overhead for change detection and snapshots. Only skip for entities you'll modify and save.
-
What's the difference between Include and Select for loading related data?
- Include loads entire entities with JOIN, enables navigation property access, tracks entities. Select projects to specific shape, loads only needed columns, no tracking, fastest option. Use Select for DTOs.
-
How do compiled queries improve performance?
- Pre-compile LINQ expression trees, skip query translation step. ~50% faster for frequently called queries. Best for hot paths with simple parameterized queries.
-
Explain ExecuteUpdate and ExecuteDelete in EF Core 7+.
- Set-based operations that execute directly on database without loading entities. Much faster than load → modify → save for bulk updates/deletes. Bypass change tracker completely.
-
How does EF Core handle batching?
- EF Core 8+ automatically batches multiple INSERT/UPDATE/DELETE in SaveChanges. Configurable with MaxBatchSize. Reduces round-trips. For massive bulk operations, use ExecuteUpdate/Delete or EFCore.BulkExtensions.
-
What is split query and when would you use it?
- Separate queries for collections instead of single cartesian product query. Use when Including multiple collections causes data duplication. Enable with AsSplitQuery().