The Silent Performance Killer: Understanding the N+1 Query Problem in EF Core

If you have been working with Entity Framework Core for a while, you have probably written a query that looked completely innocent, only to discover later that your application was making hundreds of database queries.

The annoying part is that the C# code can look perfectly reasonable.

This is one of those problems that is easy to miss during development because everything works fine when you have 10 records in your database. Then production arrives with 10,000 records, and suddenly the database is doing a lot more work than you expected.

What is the N+1 problem?

Imagine we have a simple application.

We have:

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }

    public Customer Customer { get; set; } = null!;
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

Now we want to display all orders together with the customer's name.

A beginner might write:

var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

At first glance, nothing looks wrong.

We execute one query to retrieve the orders.

Then we access order.customer.

But here's the problem:

// var orders = await context.Orders.ToListAsync();
   |__ 1 query --> get all orders

// foreach (var order in orders)
// order.Customer.Name
   |__ + 1 query --> get customer for order 1
   |__ + 1 query --> get customer for order 2
   |__ + 1 query --> get customer for order 3
   |__ + ...
   |__ + 1 query --> get customer for order N

So instead of executing one query, we execute:

1 + N queries

That's where the name comes from.

If we have 100 orders: 1 + 100 = 101 queries

If we have 10,000 orders: 1 + 10,000 = 10,001 queries

The application hasn't changed. The amount of data has.

And suddenly a perfectly innocent-looking loop becomes a performance problem.

Why is it called N+1?

The 1 is the first query:

SELECT *
FROM Orders;

The N represents the additional queries needed for the N records:

SELECT *
FROM Customers
WHERE Id = 1;

SELECT *
FROM Customers
WHERE Id = 2;

SELECT *
FROM Customers
WHERE Id = 3;

The problem is the number of round trips to the database.

But does Entity Framework Core always cause N+1?

No.

This is an important distinction.

Simply having a navigation property does not automatically mean Entity Framework Core will execute another query whenever you access it.

For example, EF Core does not enable lazy loading by default.

N+1 commonly appears when you explicitly use lazy loading, manually query related entities inside a loop, or structure your application in a way that causes repeated database access.

For example, with lazy loading enabled:

var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

The first query loads the orders.

When order.Customer is accessed, EF Core can transparently execute another query.

That transparency is exactly what makes the problem so easy to miss.

Solution 1: loading with Include

One of the most common solutions is to explicitly load the related data with Include.

Instead of:

var orders = await context.Orders.ToListAsync();

we can write:

var orders = await context.Orders
    .Include(x => x.Customer)
    .ToListAsync();

Now you're telling EF Core:

"I know that I need the Customer information. Load it as part of this query."

Depending on the relationship and query, EF Core can generate a SQL query using a join:

SELECT ...
FROM Orders
LEFT JOIN Customers
    ON Orders.CustomerId = Customers.Id;

Instead of:

1 query
+
N queries

we can get:

1 query

Solution 2: Projection

Let's say our API doesn't actually need the entire Order and Customer entities.

We only need:

Order ID
Customer name

Why load everything?

Instead, project directly into the object we need:

var orders = await context.Orders
    .Select(order => new OrderDto
    {
        Id = order.Id,
        CustomerName = order.Customer.Name
    })
    .ToListAsync();

Now EF Core can translate the projection into SQL.

Something conceptually similar to:

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

This is powerful because you're not saying:

"Give me the Order and Customer entities."

You're saying:

"Give me exactly the data required by this use case."

Solution 3: Query the relationship directly

Sometimes you don't need to load the related entities at all.

Imagine you're displaying the number of orders belonging to each customer.

You might be tempted to do:

var customers = await context.Customers.ToListAsync();

foreach (var customer in customers)
{
    var count = await context.Orders
        .CountAsync(x => x.CustomerId == customer.Id);
}

This is another N+1 pattern.

Instead, let the database perform the aggregation:

var customers = await context.Customers
    .Select(customer => new
    {
        customer.Id,
        customer.Name,
        OrderCount = customer.Orders.Count()
    })
    .ToListAsync();

Now the database can calculate the counts as part of the query.

How I think about N+1

When reviewing EF Core code, I try to ask myself one simple question:

"How many SQL queries will this code execute?"

Don't only look at the C#.

This:

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

looks harmless.

But if Customer is lazy-loaded, the database might see:

SELECT Orders...

SELECT Customer...
SELECT Customer...
SELECT Customer...
SELECT Customer...
...

That's the real code your application is executing from the database's perspective.

Once you get into the habit of thinking about the SQL behind your LINQ, N+1 becomes much easier to spot.