How do you optimize Entity Framework queries?
4 minadvancedEF-Coreperformancequeryoptimization
Quick Answer
Optimize EF Core queries by: using `AsNoTracking()` for read-only reads, projecting only needed columns with `Select` instead of loading whole entities, avoiding N+1 via `Include`/projection, using `AsSplitQuery()` for large `Include` cartesian explosions, paginating with `Skip/Take`, leveraging compiled queries on hot paths, and pushing filtering/ordering to the database. Profile generated SQL to confirm.
Detailed Answer
1. Use AsNoTracking() for Read-Only Queries
// 30-40% faster for read-only scenarios
var products = context.Products
.AsNoTracking()
.Where(p => p.IsActive)
.ToList();
2. Project Only Needed Columns
// Bad: Fetches all columns
var products = context.Products.ToList();
// Good: Fetch only needed data
var products = context.Products
.Select(p => new { p.Id, p.Name, p.Price })
.ToList();
3. Use Compiled Queries
private static readonly Func GetProductById =
EF.CompileQuery((AppDbContext context, int id) =>
context.Products.FirstOrDefault(p => p.Id == id));
// Usage
var product = GetProductById(context, 123);
4. Batch Operations
// Bad: Multiple round trips
foreach (var product in products)
{
context.Products.Add(product);
context.SaveChanges(); // Don't do this!
}
// Good: Single batch
context.Products.AddRange(products);
context.SaveChanges();
5. Use Pagination
var products = context.Products
.OrderBy(p => p.Name)
.Skip((page - 1) * pageSize)
.Take(pageSize)
.ToList();
6. Filter Before Loading
// Bad: Load all then filter in memory
var activeProducts = context.Products
.ToList()
.Where(p => p.IsActive);
// Good: Filter in database
var activeProducts = context.Products
.Where(p => p.IsActive)
.ToList();
7. Use Indexes
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasIndex(p => p.Name);
modelBuilder.Entity()
.HasIndex(o => new { o.CustomerId, o.OrderDate });
}
8. Avoid Cartesian Explosion with Split Queries
// Multiple collections can create huge result sets
var customers = context.Customers
.Include(c => c.Orders)
.Include(c => c.Addresses)
.AsSplitQuery() // Prevents cartesian product
.ToList();
9. Use Raw SQL for Complex Queries
var results = context.Products
.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", minPrice)
.ToList();
10. Configure Query Splitting Strategy
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(connectionString,
options => options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}
11. Use Database-Side Evaluation
// Good: Evaluated in database
var count = context.Products
.Count(p => p.Price > 100);
// Bad: All data loaded into memory first
var count = context.Products
.ToList()
.Count(p => p.Price > 100);