The N+1 Problem in Entity Framework Core: A Detective’s Guide to Spotting and Fixing It

February 19, 2025

In this post, Paul Gradie reveals how the notorious N+1 problem in EF Core—where seemingly simple data retrieval turns into a series of inefficient database trips—can be the hidden culprit behind performance woes. He breaks down how lazy loading and loops can lead to unnecessary queries, and shares actionable fixes to streamline your code. Dive in to uncover tips that will help you optimize your data access and keep your app running smoothly!

What Is the N+1 Problem?

In summary - it is a database lookup problem where your code is making far too many trips back and forth to the store to retrieve records one at a time instead of returning them all at once.

Imagine you're hosting a pizza party for your friends. You call up your favorite pizza place and order 10 pizzas - one for each friend.

The delivery service brings you the 10 pizzas, but here's the catch: they only bring the crust and sauce. No cheese, no toppings. They promise to bring the toppings separately. So, one by one, they return, each time bringing just a single topping for one pizza. They do this 10 times—once for each pizza.

By the time the last pizza finally gets its toppings, your friends are starving and frustrated. The pizza party has turned into a disaster, and you're wondering why you didn’t just order the whole pizza, toppings and all, in a single trip.

This is the N+1 problem in EF Core. It happens when your application makes 1 query to fetch a list of entities (e.g., 10 pizzas) and then N additional queries to fetch related data for each item in that list (e.g., the toppings for each pizza). Instead of efficiently retrieving everything in one go, the app repeatedly fetches extra data in individual queries, causing delays and inefficiency.

How Does It Happen in Code?

The N+1 problem usually sneaks in when lazy loading or bad loops interact with EF Core. Here are two common ways this can manifest:

1. Lazy Loading

By default, EF Core doesn’t fetch related data unless you ask for it. But when lazy loading is enabled, it sneaks off to the database and grabs related data when you access a navigation property.

var books = context.Books.ToList();
foreach(var book in books) {
    // Triggers a database query for every book
    Console.WriteLine(book.Chapters.Count);
}

Here, EF Core fetches all the Books in one query, but when we access book.Chapters, it fires another query for each book. So for 100 books, you’ve just made 101 trips to the database.

2. Explicit Queries in Loops

Even without lazy loading, looping over entities and running queries for each one can mimic the same problem.

var books = context.Books.ToList();
foreach(var book in books) {
    // Another query per book
    var chapters = context.Chapters.Where(c => c.BookId == book.Id).ToList();
}

This isn’t EF Core’s fault—this is you manually recreating an N+1 scenario. Bummer!

How to Spot the N+1 Problem

1. Use Query Logs

EF Core can log every SQL query it generates. Turn on logging and run your suspicious code. If you see one query for the main entity and then N queries for related entities, you’ve got a problem.

optionsBuilder.LogTo(Console.WriteLine);

2. Look for Loops with Queries

Scan your codebase for patterns like this:

foreach(var entity in entities) {
    var related = context.RelatedEntities.Where(...).ToList();
}

These are screaming "N+1" louder than a pager going off at 3 a.m.

3. Analyze Performance

N+1 problems aren’t always obvious with small datasets. But when your database grows, they make your app grind to a halt. If adding more data causes exponential performance degradation, it’s time to investigate.

Fixing the N+1 Problem

1. Eager Loading

Tell EF Core to grab related data upfront using .Include(). This ensures everything is fetched in one trip.

var books = context.Books.Include(b => b.Chapters).ToList();

No more extra queries when you access book.Chapters.

2. Projections

If you don’t need the full entities, project the data into a lightweight structure (like a DTO).

var books = context.Books
    .Include(b => b.Chapters) // Eagerly load the related Chapters
    .Select(b => new {
        b.Title, // Project the data to get just the chapter titles
        Chapters = b.Chapters.Select(c => c.Title).ToList()
    }).ToList();

This avoids loading unnecessary properties and keeps your queries efficient.

3. Disable Lazy Loading (If possible)

Lazy loading can be a convenient way to automatically retrieve related data on request, but as we’ve seen, it can also cause performance issues if misused. Some code bases may have the option to disable lazy loading entirely if it is becoming a main source of pain - forcing everyone to be explicit about what data they’re fetching.

optionsBuilder.UseLazyLoadingProxies(false);

Lazy loading, however, can be a deliberate decision (as it is at Empower) to improve dev velocity. In such cases, ensuring teams are aware of this behavior and are deliberate about its usage is the way to go. These sorts of decisions should be carefully considered, keeping in mind the needs and capabilities of the engineers involved.

How to Teach Your Team to Spot and Avoid N+1

1. Use Real Examples

Take a piece of your existing code with an N+1 issue. Log the queries and show your team how the SQL explodes. Then, fix it together and show the performance improvement.

2. Create a Checklist

Share this mental checklist:

Are you using .Include() or .Select() for related data?
Are there queries inside loops?
Does the query log show more than you expect?

3. Hands-On Exercises

Give them exercises where they deliberately create an N+1 problem. Then ask them to refactor it. Let them feel the pain—and then the joy of fixing it.

4. Code Reviews

Make query patterns part of your code review checklist. If you see loops with queries or unexplained lazy loading, ask questions.

Conclusion

The N+1 problem isn’t some abstract academic concern. It’s a real-world issue that can silently cripple your app. But the good news is it’s not hard to spot - and it’s even easier to fix once you know how. You’ll save yourself hours of debugging, speed up your app, and maybe even get that 3 a.m. pager to stay quiet by teaching your team to think critically about how EF Core fetches data.

And remember: Always grab the toppings with the pizza.