The N+1 Query Problem

The N+1 problem happens when code loads a list of N records with one query, then runs another query for each record to load related data. Fifty orders become fifty-one database round trips.

It is easy to create with ORMs such as Entity Framework or Hibernate, because lazy-loaded navigation properties look like normal property access.

Diagram

Key concepts

Lazy loading

Related data is fetched the first time it is accessed — convenient, and the main cause of N+1.

Eager loading

Related data is fetched up front with a join or a second batched query (Include, JOIN FETCH).

Batching

Collect all IDs and run one WHERE id IN (…) query. GraphQL DataLoader does this automatically.

Projection

Select only the fields needed into a DTO, letting the database do the join.

Example

// N+1: one query for orders, then one per order for its customer
var orders = await db.Orders.ToListAsync();
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name);   // lazy load = extra query each time

// Fix: load the related data in the same query
var orders = await db.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// Or project only what you need
var rows = await db.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToListAsync();

Trade-offs

Interview questions