The N+1 Query Problem: Why Your LINQ Query Makes 1001 Database Calls

Your query returns 1000 orders. Your database log shows 1001 queries. One to fetch the orders, then one for each order's customer. This is the N+1 problem — and it's probably happening in your codebase right now.

The Innocent-Looking Code

var orders = dbContext.Orders.ToList();

foreach (var order in orders)
{
    Console.WriteLine($"Order {order.Id} by {order.Customer.Name}");
}

What you expect: one query fetching orders with customer names.

What happens:

1. SELECT * FROM Orders — fetches 1000 orders
2. SELECT * FROM Customers WHERE Id = 1 — for order 1
3. SELECT * FROM Customers WHERE Id = 2 — for order 2
4. ... 998 more queries ...

If each query takes 5ms, you've turned a 10ms operation into a 5-second disaster.

Why It Happens

Entity Framework uses lazy loading by default (or used to — EF Core made it opt-in). When you access order.Customer, EF sees the navigation property isn't loaded and fires a query.

The insidious part: it works perfectly in development with 10 records. In production with 10,000? Your API times out.

The Solution: Eager Loading with Include

var orders = dbContext.Orders
    .Include(o => o.Customer)  // Load customers with orders
    .ToList();

foreach (var order in orders)
{
    Console.WriteLine($"Order {order.Id} by {order.Customer.Name}");
}

Now EF generates one query with a JOIN. Customer data comes with the order in a single round-trip.

Nested Includes

For deeper relationships:

var orders = dbContext.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)  // Nested: OrderItem's Product
    .ToList();

ThenInclude continues from the last included collection. You can chain them for complex graphs.

The Projection Alternative

Include loads entire entities. Often, you don't need all columns:

// Instead of Include...
var orders = dbContext.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ToList();

// ...project to what you actually need
var orders = dbContext.Orders
    .Select(o => new 
    {
        o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.OrderItems.Count,
        Total = o.OrderItems.Sum(oi => oi.Price)
    })
    .ToList();

The projection approach generates one query that fetches exactly the columns needed. No N+1, minimal data transfer.

Fun fact: The term "N+1" comes from the mathematical pattern: 1 query for the parent collection, plus N queries for each child. It was popularized by the Ruby on Rails community around 2006, but the problem existed anywhere ORMs did automatic lazy loading. The ActiveRecord pattern (from Martin Fowler's 2002 book) made it endemic.

AsSplitQuery: When Include Gets Too Wide

Wide includes (many navigation properties or deep nesting) produce massive JOINs. EF Core 5+ offers split queries:

var orders = dbContext.Orders
    .Include(o => o.OrderItems)
    .Include(o => o.ShippingAddress)
    .Include(o => o.BillingAddress)
    .AsSplitQuery()  // Separate queries instead of mega-JOIN
    .ToList();

AsSplitQuery fires multiple simple queries instead of one giant JOIN. For complex includes with many columns, split queries can actually be faster.

Trade-off: multiple round-trips vs one huge result set. Profile to decide.

Detecting N+1 in the Wild

Option 1: EF Core Logging

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

Watch the output. Multiple SELECT statements for navigation properties = N+1.

Option 2: Simple Query Count

int queryCount = 0;
optionsBuilder.LogTo(msg => 
{
    if (msg.Contains("Executing DbCommand")) queryCount++;
}, LogLevel.Information);

If queryCount >> 1 for what should be one query, investigate.

Option 3: Third-Party Tools

MiniProfiler, Glimpse, or similar tools visualize queries per request. N+1 shows as a fan of identical query patterns.

Common N+1 Patterns

The Loop Print

// Bad
foreach (var order in dbContext.Orders)
    Console.WriteLine(order.Customer.Name);  // N queries

// Good
foreach (var order in dbContext.Orders.Include(o => o.Customer))
    Console.WriteLine(order.Customer.Name);  // 1 query

The Innocent Serialize

// Bad — serializer triggers lazy loading
return Json(dbContext.Orders.ToList());  // Triggers N+1 during serialization

// Good
return Json(dbContext.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToList());

The Collection Count

// Bad
var orders = dbContext.Orders.ToList();
var withItems = orders.Where(o => o.OrderItems.Any());  // N queries

// Good
var orders = dbContext.Orders.Include(o => o.OrderItems).ToList();
var withItems = orders.Where(o => o.OrderItems.Any());  // 0 additional queries

The Rule

1. Accessing navigation in loops? Include before iterating
2. Don't need full entities? Project to DTOs (avoids N+1 and fetches less data)
3. Wide includes? Consider AsSplitQuery
4. Serialize entities? Project to anonymous/DTO first
5. Always profile — EF logging reveals the real query count

---

Next time, we'll look at async LINQ — when to use ToListAsync, why you can't make LINQ operators truly async, and the Task patterns that work with database queries. Hope to see you!