What is the N+1 query problem and how do you solve it?

N+1 Query Problem occurs when you execute 1 query to fetch N records, then N additional queries to fetch related data for each record.

Example of N+1 Problem:

// 1 query to get orders
var orders = context.Orders.ToList();

// N queries (one per order) to get customers
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // Lazy loading triggers query
}
// Total: 1 + N queries!

Solutions:

1. Eager Loading with Include()

// Single query with JOIN
var orders = context.Orders
    .Include(o => o.Customer)
    .ToList();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // No additional query
}

2. Select/Projection

// Query only needed data
var orderSummaries = context.Orders
    .Select(o => new 
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        Total = o.Total
    })
    .ToList();

3. Split Queries for Multiple Includes

// Instead of one large JOIN, use multiple queries
var orders = context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSplitQuery() // EF Core 5.0+
    .ToList();

4. Explicit Loading with Batch

var orders = context.Orders.ToList();

// Load all customers in one query
var customerIds = orders.Select(o => o.CustomerId).Distinct();
var customers = context.Customers
    .Where(c => customerIds.Contains(c.Id))
    .ToDictionary(c => c.Id);

// Map in memory
foreach (var order in orders)
{
    order.Customer = customers[order.CustomerId];
}

5. Disable Lazy Loading

// Don't enable lazy loading proxies
services.AddDbContext(options =>
    options.UseSqlServer(connectionString)
    // Don't use: .UseLazyLoadingProxies()
);

Detection Tools:

// Enable sensitive data logging and detailed errors
optionsBuilder
    .EnableSensitiveDataLogging()
    .EnableDetailedErrors()
    .LogTo(Console.WriteLine, LogLevel.Information);