Optimize Entity Framework Core queries by fixing N+1 problems, choosing correct tracking modes, using compiled queries, and avoiding common performance traps. Use when EF Core queries are slow, generating excessive SQL, or causing high database load.
| Input | Required | Description |
|---|---|---|
| Slow EF Core queries | Yes | The LINQ queries or DbContext usage to optimize |
| SQL output or logs | No | EF Core generated SQL or query execution logs |
// In Program.cs or DbContext configuration:
optionsBuilder
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging() // shows parameter values (dev only!)
.EnableDetailedErrors();
Or use the Microsoft.EntityFrameworkCore log category:
{
"Logging": {
"LogLevel": {
"Microsoft.EntityFrameworkCore.Database.Command": "Information"
}
}
}
The #1 EF Core performance killer. Happens when loading related entities in a loop.
Before (N+1 — 1 query for orders + N queries for items):
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
// Each access triggers a lazy-load query!
var items = order.Items.Count;
}
After (eager loading — 1 or 2 queries total):
// Option 1: Include (JOIN)
var orders = await db.Orders
.Include(o => o.Items)
.ToListAsync();
// Option 2: Split query (separate SQL, avoids cartesian explosion)
var orders = await db.Orders
.Include(o => o.Items)
.AsSplitQuery()
.ToListAsync();
// Option 3: Explicit projection (best - only fetches needed columns)
var orderSummaries = await db.Orders
.Select(o => new OrderSummary
{
OrderId = o.Id,
Total = o.Items.Sum(i => i.Price),
ItemCount = o.Items.Count
})
.ToListAsync();
When to use Split vs Single query:
| Scenario | Use |
|---|---|
| 1 level of Include | Single query (default) |
| Multiple Includes (Cartesian risk) | AsSplitQuery() |
| Include with large child collections | AsSplitQuery() |
| Need transaction consistency | Single query |
Change tracking overhead is significant. Disable it when you don't need to update entities:
// Per-query
var products = await db.Products
.AsNoTracking()
.Where(p => p.IsActive)
.ToListAsync();
// Global default for read-heavy apps
services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString)
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
Use AsNoTrackingWithIdentityResolution() when the query returns duplicate entities to avoid duplicated objects in memory.
// Define once as static
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
EF.CompileAsyncQuery((AppDbContext db, int id) =>
db.Orders
.Include(o => o.Items)
.FirstOrDefault(o => o.Id == id));
// Use repeatedly — skips query compilation overhead
var order = await GetOrderById(db, orderId);
| Trap | Problem | Fix |
|---|---|---|
ToList() before Where() | Loads entire table into memory | Filter first: .Where().ToList() |
Count() to check existence | Scans all rows | Use .Any() instead |
.Select() after .Include() | Include is ignored with projection | Remove Include, use Select only |
string.Contains() in Where | May not translate, falls to client eval | Use EF.Functions.Like() for SQL LIKE |
Calling .ToList() inside Select() | Causes nested queries | Use projection with Select all the way |
When LINQ can't express it efficiently:
var results = await db.Orders
.FromSqlInterpolated($@"
SELECT o.* FROM Orders o
INNER JOIN (
SELECT OrderId, SUM(Price) as Total
FROM OrderItems
GROUP BY OrderId
HAVING SUM(Price) > {minTotal}
) t ON o.Id = t.OrderId")
.AsNoTracking()
.ToListAsync();
AsNoTracking()| Pitfall | Solution |
|---|---|
| Lazy loading silently creating N+1 | Remove Microsoft.EntityFrameworkCore.Proxies or disable lazy loading |
| Global query filters forgotten in perf analysis | Check HasQueryFilter in model config; use IgnoreQueryFilters() if needed |
DbContext kept alive too long | DbContext should be scoped (per-request); don't cache it |
| Batch updates fetching then saving | EF Core 7+: use ExecuteUpdateAsync / ExecuteDeleteAsync for bulk operations |
String interpolation in FromSqlRaw | SQL injection risk — use FromSqlInterpolated (parameterized) |