TL;DR:

  • The N+1 query problem in EF Core causes excessive database round trips and poor performance.
  • Use Include() for eager loading to fetch related data in a single query and avoid N+1 issues.
  • For complex relationships or large datasets, use AsSplitQuery() to prevent cartesian explosion and reduce memory usage.
  • Monitor your queries with logging or profiling tools like MiniProfiler to catch N+1 problems early.
  • Use projections with Select() for optimal performance when you only need a subset of related data.
  • Make split queries the default in your DbContext if you frequently deal with complex relationships.

The N+1 query problem is one of those performance killers that can sneak up on you when you least expect it.

You write what looks like perfectly reasonable code, everything works fine in development with your small dataset, and then production hits with thousands of records. Suddenly, your API that used to respond in 50ms is taking 5 seconds.

I’ve been there, and it’s not fun. The good news is that Entity Framework Core gives us powerful tools to fix this problem. Let’s look at how N+1 queries happen and how to prevent them using Include() and AsSplitQuery().

What Exactly Is the N+1 Problem?

The N+1 query problem happens when your application executes one query to fetch a list of entities, then executes additional queries for each entity to load related data. If you have N parent records, you end up with N+1 total queries: one for the parents, plus one for each parent’s children.

Here’s a typical scenario that causes N+1 queries. Let’s say you’re building an e-commerce system with orders and order items:

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; }
    public DateTime OrderDate { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
    public Order Order { get; set; }
}

Now imagine you want to display a list of recent orders with their items. You might write code like this:

public async Task<List<OrderSummary>> GetRecentOrdersAsync()
{
    var orders = await _context.Orders
        .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
        .ToListAsync();

    var orderSummaries = new List<OrderSummary>();
    
    foreach (var order in orders)
    {
        // This triggers a separate query for each order!
        var totalAmount = order.Items.Sum(i => i.Price * i.Quantity);
        
        orderSummaries.Add(new OrderSummary
        {
            OrderId = order.Id,
            CustomerName = order.CustomerName,
            OrderDate = order.OrderDate,
            TotalAmount = totalAmount,
            ItemCount = order.Items.Count
        });
    }
    
    return orderSummaries;
}

This innocent-looking code is a performance disaster waiting to happen.

How EF Core Creates the N+1 Problem

When you access the order.Items property in the loop above, EF Core’s lazy loading kicks in. Since the items weren’t loaded with the original query, EF Core executes a separate query to fetch the items for that specific order.

The database queries look something like this:

-- Initial query (1 query)
SELECT [o].[Id], [o].[CustomerName], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[OrderDate] >= '2024-05-27'

-- Then for each order (N queries)
SELECT [i].[Id], [i].[OrderId], [i].[ProductName], [i].[Price], [i].[Quantity]
FROM [OrderItems] AS [i]
WHERE [i].[OrderId] = 1

SELECT [i].[Id], [i].[OrderId], [i].[ProductName], [i].[Price], [i].[Quantity]
FROM [OrderItems] AS [i]
WHERE [i].[OrderId] = 2

-- ... and so on for every single order

If you have 100 orders, you’re looking at 101 database queries instead of 1 or 2. That’s a lot of unnecessary round trips to the database.

Here’s a visual representation of what’s happening:


sequenceDiagram
    participant App as Application
    participant EF as EF Core
    participant DB as Database
    
    App->>EF: Get Recent Orders
    EF->>DB: SELECT * FROM Orders WHERE OrderDate >= @date
    DB-->>EF: Return N Orders
    EF-->>App: Return N Order objects
    
    Note over App,DB: Lazy Loading causes N additional queries
    
    loop For each Order
        App->>EF: Access Order.Items
        EF->>DB: SELECT * FROM OrderItems WHERE OrderId = @id
        DB-->>EF: Return Order Items
        EF-->>App: Return Items collection
    end

The N+1 Query Problem in EF Core

Detecting N+1 Queries

Before we fix the problem, let’s talk about how to spot it. EF Core’s logging can help you see what queries are being executed. Add this to your appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

You’ll start seeing SQL queries in your logs. If you see the same query pattern repeated multiple times with different parameter values, you’ve probably found an N+1 issue.

Another great tool is MiniProfiler. It shows you exactly how many queries each request generates and highlights potential N+1 problems automatically.

Fixing N+1 with Include()

The most straightforward fix is to use eager loading with the Include() method. This tells EF Core to load the related data as part of the initial query:

public async Task<List<OrderSummary>> GetRecentOrdersAsync()
{
    var orders = await _context.Orders
        .Include(o => o.Items) // Eagerly load the items
        .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
        .ToListAsync();

    var orderSummaries = orders.Select(order => new OrderSummary
    {
        OrderId = order.Id,
        CustomerName = order.CustomerName,
        OrderDate = order.OrderDate,
        TotalAmount = order.Items.Sum(i => i.Price * i.Quantity),
        ItemCount = order.Items.Count
    }).ToList();
    
    return orderSummaries;
}

Now EF Core generates a single query with a JOIN:

SELECT [o].[Id], [o].[CustomerName], [o].[OrderDate],
       [i].[Id], [i].[OrderId], [i].[ProductName], [i].[Price], [i].[Quantity]
FROM [Orders] AS [o]
LEFT JOIN [OrderItems] AS [i] ON [o].[Id] = [i].[OrderId]
WHERE [o].[OrderDate] >= '2024-05-27'
ORDER BY [o].[Id]

One query instead of 101. Much better!


flowchart LR
    A[Application] --> B[EF Core]
    B -->|"orders.Include(o => o.Items)"| C{Single Query}
    C -->|"SELECT Orders JOIN OrderItems"| D[(Database)]
    D -->|"Returns orders with items"| B
    B -->|"Complete result set"| A
    
    style C fill:#f96,stroke:#333,stroke-width:2px
    style D fill:#69b,stroke:#333,stroke-width:2px

Eager Loading with Include() Method

You can also include multiple levels of related data:

var orders = await _context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product) // Load product details for each item
    .Include(o => o.Customer) // Also load customer information
    .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
    .ToListAsync();

When Single Queries Become a Problem

While Include() solves the N+1 problem, it can create a different issue. When you join multiple tables, especially in one-to-many relationships, you get cartesian explosion.

Let’s say an order has 5 items, and you’re also including customer data. The database returns 5 rows (one for each item), but most of the order and customer data is duplicated across those rows. This wastes bandwidth and memory.

Here’s where AsSplitQuery() comes in handy.

Using AsSplitQuery() for Better Performance

Split queries tell EF Core to use separate queries for each Include(), but still execute them efficiently:

public async Task<List<OrderSummary>> GetRecentOrdersAsync()
{
    var orders = await _context.Orders
        .Include(o => o.Items)
        .Include(o => o.Customer)
        .AsSplitQuery() // Use separate queries for includes
        .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
        .ToListAsync();

    // Rest of your code...
}

With AsSplitQuery(), EF Core generates multiple queries, but executes them as a batch:

-- Query 1: Get the orders
SELECT [o].[Id], [o].[CustomerName], [o].[OrderDate], [o].[CustomerId]
FROM [Orders] AS [o]
WHERE [o].[OrderDate] >= '2024-05-27'

-- Query 2: Get all related items in one go
SELECT [i].[Id], [i].[OrderId], [i].[ProductName], [i].[Price], [i].[Quantity]
FROM [OrderItems] AS [i]
WHERE [i].[OrderId] IN (1, 2, 3, 4, 5, ...) -- All order IDs from query 1

-- Query 3: Get all related customers in one go
SELECT [c].[Id], [c].[Name], [c].[Email]
FROM [Customers] AS [c]
WHERE [c].[Id] IN (101, 102, 103, ...) -- All customer IDs from query 1

This gives you the best of both worlds: no N+1 queries, and no cartesian explosion.


flowchart LR
    subgraph "N+1 Problem"
        A1[Application] --> B1[EF Core]
        B1 -->|"Query 1"| C1[(Database)]
        C1 -->|"Orders"| B1
        B1 -->|"Query 2...N+1"| C1
        C1 -->|"Items for each Order"| B1
        B1 -->|"Final Result"| A1
    end
    
    subgraph "Include()"
        A2[Application] --> B2[EF Core]
        B2 -->|"Single JOIN Query"| C2[(Database)]
        C2 -->|"Orders + Items"| B2
        B2 -->|"Final Result"| A2
        
    end
    
    subgraph "AsSplitQuery()"
        A3[Application] --> B3[EF Core] 
        B3 -->|"Query 1"| C3[(Database)]
        C3 -->|"Orders"| B3
        B3 -->|"Query 2 (IN clause)"| C3
        C3 -->|"All related Items"| B3
        B3 -->|"Final Result"| A3
        
    end

Comparison of EF Core Query Strategies

Performance Trade-offs: Single vs Split Queries

Here’s a comparison of the different approaches:

ApproachQueriesData TransferMemory UsageBest For
No IncludeN+1Minimal per queryLowNever recommended
Include1High (duplicated data)HighSimple relationships, small datasets
AsSplitQuery2-3Minimal duplicationModerateComplex relationships, large datasets

Making Split Queries the Default

If you find yourself using AsSplitQuery() frequently, you can make it the default behavior:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
}

Now all your Include() operations will use split queries unless you explicitly call AsSingleQuery().

Advanced Scenarios and Tips

Sometimes you don’t need all the related data. Instead of loading everything with Include(), consider using projections:

public async Task<List<OrderSummary>> GetRecentOrdersAsync()
{
    return await _context.Orders
        .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
        .Select(o => new OrderSummary
        {
            OrderId = o.Id,
            CustomerName = o.CustomerName,
            OrderDate = o.OrderDate,
            TotalAmount = o.Items.Sum(i => i.Price * i.Quantity), // Calculated in SQL
            ItemCount = o.Items.Count() // Also calculated in SQL
        })
        .ToListAsync();
}

This generates a single query with subqueries, and you only transfer the data you actually need.

For read-only scenarios, consider using AsNoTracking() to improve performance:

var orders = await _context.Orders
    .AsNoTracking() // Don't track changes
    .Include(o => o.Items)
    .AsSplitQuery()
    .Where(o => o.OrderDate >= DateTime.Today.AddDays(-30))
    .ToListAsync();

Monitoring and Prevention

Set up monitoring to catch N+1 queries before they hit production. You can create a custom interceptor to log when too many queries are executed in a single request:

public class QueryCountInterceptor : DbCommandInterceptor
{
    private static readonly AsyncLocal<int> _queryCount = new();

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        _queryCount.Value = (_queryCount.Value ?? 0) + 1;
        
        if (_queryCount.Value > 10) // Threshold
        {
            // Log warning or throw exception
            Console.WriteLine($"Potential N+1 detected: {_queryCount.Value} queries");
        }
        
        return base.ReaderExecuting(command, eventData, result);
    }
}

Wrapping Up

N+1 queries are a common performance pitfall, but EF Core gives us excellent tools to handle them. Use Include() for simple cases where you need related data, and AsSplitQuery() when you’re dealing with complex relationships or large datasets.

The key is understanding your data access patterns and choosing the right strategy. Monitor your queries in development, use profiling tools, and don’t be afraid to use projections when you don’t need full entities.

Remember: premature optimization is the root of all evil, but N+1 queries are rarely premature to fix. They’re usually a real problem that will bite you in production. Take the time to understand how your ORM generates queries, and your future self (and your users) will thank you.

Related Posts