Entity Framework N+1 Query Problem Fix

In this tutorial, you'll learn about Entity Framework N+1 Query Problem Fix. We cover key concepts, practical examples, and best practices.

Loading a list of orders and their customer details:

var orders = db.Orders.Take(100).ToList();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

This executes 101 SQL queries — one for the orders, then 100 individual queries for each customer. This is the N+1 query problem and it will cripple your application's performance as data grows.

Step-by-Step Fix

1. Use Include() for eager loading

WRONG — lazy loading each navigation property individually:

var orders = db.Orders.ToList();
foreach (var order in orders)
{
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

RIGHT — eager load with Include:

var orders = db.Orders.Include(o => o.Customer).ToList();
foreach (var order in orders)
{
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

Expected output: one SQL query with a JOIN, not 101 queries.

2. Chain ThenInclude for nested relationships

WRONG — nested navigation causing deeper N+1:

var orders = db.Orders.Include(o => o.Customer).ToList();
foreach (var order in orders)
{
    foreach (var item in order.Items)
    {
        Console.WriteLine(item.Product.Name); // N+1 on Product!
    }
}

RIGHT — use ThenInclude to load deeply:

var orders = db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToList();

3. Use projection with Select

For read-only scenarios, project directly:

WRONG — loading entire entities then selecting fields:

var orders = db.Orders.Include(o => o.Customer).ToList();
var result = orders.Select(o => new { o.Id, CustomerName = o.Customer.Name });

RIGHT — project at the query level:

var result = db.Orders
    .Select(o => new OrderSummary
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count
    })
    .ToList();

This generates a single efficient SQL query with only the needed columns.

4. Disable lazy loading in production

WRONG — lazy loading enabled with no Include guards:

services.AddDbContext<AppDbContext>(options =>
    options.UseLazyLoadingProxies()
           .UseSqlServer(connectionString));

RIGHT — disable lazy loading for API/Service contexts:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)); // no LazyLoadingProxies

5. Batch with AutoMapper's ProjectTo

WRONG — loading entities and mapping manually:

var orders = db.Orders.Include(o => o.Customer).ToList();
var dtos = orders.Select(o => new OrderDto { Id = o.Id, Customer = o.Customer.Name });

RIGHT — use ProjectTo for SQL-level projection:

var dtos = db.Orders
    .ProjectTo<OrderDto>(mapperConfig)
    .ToList();

This generates a SELECT with only the required columns.

Prevention

• Log all SQL queries during development with optionsBuilder.LogTo(Console.WriteLine).
• Watch for repeated queries with the same shape — that's N+1.
• Disable lazy loading in production ASP.NET applications.
• Use Include/ThenInclude for all navigation properties in a query.
• Consider batching or caching for read-heavy scenarios.

Common Mistakes with framework n plus 1

1. Non-exhaustive pattern matches that compile with warnings then crash at runtime
2. Misunderstanding that String is [Char] with poor performance for large text operations
3. Using foldl instead of foldl' causing stack overflow on large lists

These mistakes appear frequently in real-world ENTITY code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ