EF Core Query Optimization: The Traps That Kill Performance

The N+1 Query Death Trap

This innocent-looking code is a performance killer:

CSHARP

// 😱 WRONG - Generates N+1 queries
var orders = await context.Orders.Take(100).ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine($"Customer: {order.Customer.Name}"); // Lazy loading triggers here
}
// Result: 1 query for orders + 100 queries for customers = 101 database hits

The Fix: Explicit loading with Include:

CSHARP

// ✅ CORRECT - Single optimized query
var orders = await context.Orders
    .Include(o => o.Customer)
    .Take(100)
    .ToListAsync();
// Result: 1 query with JOIN

Pro tip: Use AsSingleQuery() vs AsSplitQuery() strategically:

CSHARP

// For narrow results - single query with JOINs
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSingleQuery() // One complex query
    .ToListAsync();

// For wide results - split into multiple queries
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .AsSplitQuery() // Separate optimized queries
    .ToListAsync();

AsNoTracking: Not Always Your Friend

Everyone knows AsNoTracking() improves performance, but timing matters:

CSHARP

// 😱 WRONG - AsNoTracking applied too early
var expensiveProducts = await context.Products
    .AsNoTracking() // Applied here
    .Where(p => p.Price > 1000)
    .Select(p => new ProductDto
    {
        Name = p.Name,
        Price = p.Price
    })
    .ToListAsync();
// AsNoTracking is pointless here since Select creates new objects anyway

CSHARP

// ✅ CORRECT - AsNoTracking where it matters
var products = await context.Products
    .Where(p => p.Price > 1000)
    .AsNoTracking() // Only when returning entities
    .ToListAsync();

// ✅ EVEN BETTER - Skip AsNoTracking with projections
var productDtos = await context.Products
    .Where(p => p.Price > 1000)
    .Select(p => new ProductDto
    {
        Name = p.Name,
        Price = p.Price
    })
    .ToListAsync(); // No AsNoTracking needed

The Projection Performance Multiplier

Always project to exactly what you need:

CSHARP

// 😱 WRONG - Loading entire entities
var customers = await context.Customers
    .Include(c => c.Orders)
        .ThenInclude(o => o.OrderItems)
    .Where(c => c.IsActive)
    .ToListAsync();
// Loads massive amounts of unnecessary data

// ✅ CORRECT - Project to specific needs
var customerSummaries = await context.Customers
    .Where(c => c.IsActive)
    .Select(c => new CustomerSummaryDto
    {
        Name = c.Name,
        Email = c.Email,
        OrderCount = c.Orders.Count(),
        TotalSpent = c.Orders.Sum(o => o.Total)
    })
    .ToListAsync();
// Generates efficient GROUP BY query

The Contains() Performance Trap

Large Contains() calls can break SQL parameter limits:

CSHARP

// 😱 PROBLEMATIC - Too many parameters
var productIds = GetLargeListOfIds(); // 5000+ IDs
var products = await context.Products
    .Where(p => productIds.Contains(p.Id))
    .ToListAsync();
// Can hit SQL Server's 2100 parameter limit

The Fix: Batch your queries or use temp tables:

CSHARP

// ✅ SOLUTION 1 - Batch processing
const int batchSize = 1000;
var allProducts = new List<Product>();

for (int i = 0; i < productIds.Count; i += batchSize)
{
    var batch = productIds.Skip(i).Take(batchSize).ToList();
    var batchProducts = await context.Products
        .Where(p => batch.Contains(p.Id))
        .ToListAsync();
    allProducts.AddRange(batchProducts);
}

// ✅ SOLUTION 2 - FromSqlRaw with table-valued parameter (SQL Server)
var products = await context.Products
    .FromSqlRaw("SELECT p.* FROM Products p INNER JOIN @ids i ON p.Id = i.Value",
                new SqlParameter("@ids", CreateTableValuedParameter(productIds)))
    .ToListAsync();

Async/Await: The Memory Leak Trap

Wrong async usage can exhaust your connection pool:

CSHARP

// 😱 WRONG - Blocking async calls
public List<Customer> GetCustomers()
{
    return context.Customers.ToListAsync().Result; // Deadlock risk
}

// 😱 WRONG - Not disposing context properly
public async Task<List<Customer>> GetCustomersWrong()
{
    var context = new AppDbContext();
    return await context.Customers.ToListAsync(); // Context never disposed
}

CSHARP

// ✅ CORRECT - Proper async pattern
public async Task<List<Customer>> GetCustomersAsync()
{
    return await context.Customers.ToListAsync();
}

// ✅ CORRECT - Using statements for explicit disposal
public async Task<List<Customer>> GetCustomersWithDisposalAsync()
{
    using var context = new AppDbContext();
    return await context.Customers.ToListAsync();
}

The Query Compilation Cache Trick

EF compiles LINQ expressions into SQL. Help it cache efficiently:

CSHARP

// 😱 BAD - Prevents query caching
public async Task<List<Product>> SearchProductsWrong(string searchTerm, bool includeInactive)
{
    var query = context.Products.AsQueryable();

    if (!string.IsNullOrEmpty(searchTerm))
        query = query.Where(p => p.Name.Contains(searchTerm));

    if (!includeInactive)
        query = query.Where(p => p.IsActive);

    return await query.ToListAsync(); // Different query shapes hurt caching
}

// ✅ GOOD - Cache-friendly approach
public async Task<List<Product>> SearchProducts(string searchTerm, bool includeInactive)
{
    return await context.Products
        .Where(p => (string.IsNullOrEmpty(searchTerm) || p.Name.Contains(searchTerm))
            && (includeInactive || p.IsActive))
        .ToListAsync(); // Same query shape, parameters vary
}

Performance Monitoring Pro Tip

Tip: Always log your SQL to catch performance issues:

CSHARP

// In your DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging() // Only in development
        .EnableDetailedErrors();
}

Key Takeaways

Always use Include for related data - Don't rely on lazy loading in loops
Project early, project often - Only load the data you actually need
AsNoTracking() only helps when returning entities - Projections make it redundant
Batch large Contains() operations - Avoid parameter limit issues
Keep query shapes consistent - Help EF's query compilation cache
Monitor your SQL - The generated queries tell the real performance story

Conclusion

Remember: EF Core is powerful, but it's not magic. Understanding the SQL it generates is the key to writing high-performance applications.

Want to dive deeper? Check out the Efficient Querying by Microsoft.