Intro

The N+1 problem is a common performance issue in ORMs (Object-Relational Mappers) like Entity Framework Core, where an initial query retrieves the parent objects, followed by N additional queries to retrieve the related child objects. This can lead to significant performance degradation, especially when dealing with large datasets.

In this article, we'll explore the N+1 problem in detail, its solutions (eager loading, explicit loading, and lazy loading), and best practices for optimizing data access in EF Core applications.

Use Case: E-Commerce Order Management System

Let's consider an e-commerce application where we need to display a list of orders along with customer information. Our database schema includes:

• Orders (Id, CustomerId, OrderDate, TotalAmount)
• Customers (Id, Name, Email)

The N+1 Problem in Action

// Controller action that retrieves all orders with their customers
public async Task<IActionResult> Index()
{
    var orders = await _context.Orders.ToListAsync(); // 1 query
    foreach (var order in orders)
    {
        var customer = await _context.Customers.FindAsync(order.CustomerId); // N queries
        // Process order and customer data
    }
    return View(orders);
}

Generated SQL:

-- First query to get all orders
SELECT * FROM Orders;

-- Then one query per order to get the customer (N queries)
SELECT * FROM Customers WHERE Id = 1;
SELECT * FROM Customers WHERE Id = 2;
-- ... and so on for each order

This approach results in 1 + N queries, where N is the number of orders. For a page with 50 orders, this means 51 database round trips!

Solutions to the N+1 Problem

1. Eager Loading using .Include()

Eager loading loads both parent and child entities in a single query using a JOIN.

var orders = await _context.Orders
    .Include(o => o.Customer) // Eager load the Customer navigation property
    .ToListAsync();

Generated SQL:

SELECT o.*, c.* FROM Orders AS o
LEFT JOIN Customers AS c ON c.Id = o.CustomerId;

This reduces the number of queries to just 1, but beware of the Cartesian Explosion problem (more on this later).

2. Explicit Loading

Explicit loading loads related data only when explicitly requested.

var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    // Load customer only if needed
    await _context.Entry(order)
        .Reference(o => o.Customer)
        .LoadAsync();
}

Generated SQL:

-- First query to get all orders
SELECT * FROM Orders;

-- Then one query per order when accessed (N queries)
SELECT * FROM Customers WHERE Id = 1;
SELECT * FROM Customers WHERE Id = 2;
-- ... and so on for each order

While this still results in N+1 queries, it gives you more control over when related data is loaded.

3. Lazy Loading

Lazy loading loads navigation properties automatically when they're first accessed.

// Enable lazy loading in DbContext configuration
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies();
}

// Then you can access related data without explicit loading
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    // Customer will be loaded automatically when accessed
    string customerName = order.Customer.Name;
}

Generated SQL:

-- First query to get all orders
SELECT * FROM Orders;

-- Then one query per order when Customer is first accessed (N queries)
SELECT * FROM Customers WHERE Id = 1;
SELECT * FROM Customers WHERE Id = 2;
-- ... and so on for each order

While convenient, lazy loading can lead to unexpected performance issues if not managed carefully.

Cartesian Explosion - the Pitfall of .Include()

When using .Include() with multiple navigation properties or collections, you might encounter a Cartesian Explosion where the result set grows exponentially.

var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems) // Each order can have many items
    .ToListAsync();

Generated SQL:

SELECT o.*, c.*, oi.*
FROM Orders AS o
LEFT JOIN Customers AS c ON c.Id = o.CustomerId
LEFT JOIN OrderItems AS oi ON oi.OrderId = o.Id;

If an order has 10 items, and you have 5 orders, the result set will contain 5 * 10 = 50 rows (Cartesian Product).

Solution: .AsSplitQuery()

Entity Framework Core 5.0 introduced .AsSplitQuery() to split complex queries into multiple SQL statements.

var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSplitQuery() // Splits the query into multiple SQL statements
    .ToListAsync();

Generated SQL:

-- First query to get orders and customers
SELECT o.*, c.*
FROM Orders AS o
LEFT JOIN Customers AS c ON c.Id = o.CustomerId;

-- Second query to get order items
SELECT oi.*
FROM OrderItems AS oi
WHERE oi.OrderId IN (1, 2, 3, 4, 5);

This reduces the Cartesian Explosion while maintaining eager loading benefits.

Best Practice: Load Only What You Need

Instead of loading entire entities with all their properties, select only the data you need for the current operation. This is known as select projection:

var orders = await _context.Orders
    .Select(o => new {
        o.Id,
        OrderDate = o.OrderDate.ToString("yyyy-MM-dd"),
        CustomerName = o.Customer.Name,
        TotalAmount = o.TotalAmount,
        ItemCount = o.OrderItems.Count()
    })
    .ToListAsync();

Generated SQL:

SELECT
    o.Id AS Id,
    CONVERT(VARCHAR, o.OrderDate, 23) AS OrderDate,
    c.Name AS CustomerName,
    o.TotalAmount AS TotalAmount,
    (
        SELECT COUNT(*)
        FROM OrderItems AS oi
        WHERE oi.OrderId = o.Id
    ) AS ItemCount
FROM Orders AS o
LEFT JOIN Customers AS c ON c.Id = o.CustomerId;

This approach:

1. Reduces the amount of data transferred over the network
2. Minimizes memory usage in your application
3. Can improve query performance by avoiding unnecessary joins or calculations

Performance Comparison

Performance Boost for read-only access

When you don't need to modify and write back the data to the database, be shure to always start your EF queries with .AsNoTracking() for these benefits:

1. Reduced Memory Overhead:

   • Entity Framework won't create change trackers for each entity
   • Saves memory by not maintaining the change detection graph
     
     

2. Faster Query Execution:

   • No need to populate the internal change tracking collections
   • Simpler SQL queries generated (no additional metadata)
     
     

3. Improved Concurrency:

   • Avoids potential deadlocks from concurrent modifications
   • Reduces database load by not maintaining snapshots

// For repository pattern
public async Task<IReadOnlyList<Product>> GetAllProductsAsync()
{
    return await _context.Products.AsNoTracking().ToListAsync();
}

// For API controllers
[HttpGet("products")]
public async Task<IActionResult> GetProducts()
{
    var products = await _context.Products
        .AsNoTracking()
        .Select(p => new ProductDto {
            Id = p.Id,
            Name = p.Name,
            Category = p.Category.Name
        })
        .ToListAsync();

    return Ok(products);
}

The performance gains are particularly noticeable in:

• High-traffic applications
• Reporting systems with large datasets
• API endpoints that return read-only data

This simple addition can provide significant performance improvements with minimal code changes.

Conclusion

The N+1 problem is a common performance pitfall in ORM-based applications. By understanding the different loading strategies and their implications, you can optimize your data access layer effectively.

Key takeaways:

1. Use eager loading (.Include()) for most cases where related data is needed
2. Be aware of Cartesian Explosions and use .AsSplitQuery() when necessary
3. Consider explicit loading when you need more control over when data is loaded
4. Avoid lazy loading in performance-critical paths or web applications
5. Always select only the data you actually need to minimize overhead
6. Use .AsNoTracking() wherever possible for read-only access

By following these best practices, you can build efficient, scalable EF Core applications that avoid the N+1 problem and deliver optimal performance.