How to Fix the EF Core N+1 Query Problem

Introduction

When working with Entity Framework Core (EF Core), performance is one of the most important aspects of building scalable and efficient applications. One of the most common performance issues developers face is the N+1 Query Problem.

The N+1 query problem in EF Core can silently slow down your application by executing multiple unnecessary database queries instead of a single optimized query. If not handled properly, it can lead to serious performance bottlenecks, especially in large-scale applications.

In this article, we will explain what the EF Core N+1 query problem is in simple words, how it happens, and most importantly, how to fix it with practical examples and best practices.

What is the N+1 Query Problem?

The N+1 query problem occurs when your application executes one query to fetch data (the “1”) and then executes additional queries (the “N”) for each related record.

In simple words:

1 query → to get main data
N queries → to get related data

This results in too many database calls, which reduces performance.

Example of N+1 Problem in EF Core

Consider two entities:

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; }
    public List<OrderItem> Items { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public string ProductName { get; set; }
}

Problematic Code

var orders = dbContext.Orders.ToList();

foreach (var order in orders)
{
    var items = order.Items; // Triggers query for each order
}

What Happens Behind the Scenes

1 query → fetch all orders
N queries → fetch items for each order

This creates multiple database calls.

Why N+1 Problem is Dangerous

Increases database load
Slows down API response time
Impacts scalability
Hard to detect in small datasets

How to Fix N+1 Query Problem in EF Core

Now let’s explore the solutions.

1. Use Eager Loading (Include)

Eager loading fetches related data in a single query.

Example

var orders = dbContext.Orders
    .Include(o => o.Items)
    .ToList();

Benefit

Single optimized SQL query
Eliminates multiple round trips

2. Use ThenInclude for Nested Data

For deeper relationships, use ThenInclude.

var orders = dbContext.Orders
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .ToList();

3. Use Select Projection (Best Practice)

Instead of loading full entities, select only required data.

Example

var orders = dbContext.Orders
    .Select(o => new
    {
        o.Id,
        o.CustomerName,
        Items = o.Items.Select(i => i.ProductName).ToList()
    })
    .ToList();

Benefit

Reduces data transfer
Improves performance

4. Disable Lazy Loading (If Enabled)

Lazy loading often causes N+1 issues.

Disable Lazy Loading

optionsBuilder.UseLazyLoadingProxies(false);

Why?

Lazy loading loads data only when accessed, which can trigger multiple queries.

5. Use Explicit Loading Carefully

Explicit loading allows controlled loading of related data.

dbContext.Entry(order)
    .Collection(o => o.Items)
    .Load();

Use it when needed, but avoid inside loops.

6. Use AsNoTracking for Read-Only Queries

var orders = dbContext.Orders
    .Include(o => o.Items)
    .AsNoTracking()
    .ToList();

Benefit

Improves performance
Reduces memory usage

7. Use Split Queries (Advanced)

EF Core allows splitting queries to improve performance.

var orders = dbContext.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToList();

Difference Between Lazy, Eager, and Explicit Loading

Feature	Lazy Loading	Eager Loading	Explicit Loading
Queries	Multiple	Single	Controlled
Performance	Poor (N+1 risk)	Good	Moderate
Use Case	Small data	Most cases	Special scenarios

Real-World Example

In an e-commerce system:

Fetching orders and items
Using lazy loading → multiple queries → slow
Using Include → single query → fast

This directly impacts user experience.

How to Detect N+1 Problem

Enable logging in EF Core
Use SQL Profiler
Check number of queries executed

Common Mistakes to Avoid

1. Using Lazy Loading in Loops

Leads to multiple queries.

2. Fetching Unnecessary Data

Use projection instead.

3. Ignoring Query Logs

Always monitor queries.

Best Practices to Avoid N+1 Problem

Prefer eager loading using Include
Use projection for better performance
Avoid lazy loading in large datasets
Monitor database queries
Optimize queries regularly

Summary

The N+1 query problem in EF Core is a common performance issue caused by executing multiple unnecessary database queries. By using techniques like eager loading, projection, and disabling lazy loading, you can significantly improve performance. Understanding and fixing the N+1 problem is essential for building scalable, efficient, and high-performance applications using Entity Framework Core.