The N+1 Query Problem: How EF Core is Silently Killing Your App

7 min readAjay Patel

Imagine this scenario: You just finished building a beautiful new data dashboard for your application. You test it locally against a local SQL Server Express database. The page loads in 15 milliseconds. You pat yourself on the back, merge the Pull Request, and deploy the code.

The next day, you deploy it to production. Suddenly, your APM tools are screaming. The database CPU is pinned at 100%, memory usage is spiking, and the dashboard takes 8 seconds to load for your end users.

What went wrong? Nine times out of ten, you are the latest victim of the most notorious performance killer in modern ORMs: The N+1 Query Problem.

Here is an exhaustive guide on exactly what this problem is, why Entity Framework Core allows it to happen, how to detect it, and the specific query strategies you must use to eradicate it from your codebase.

Table of Contents

1. The Localhost Illusion

Before we dive into the code, we need to understand why this bug almost never gets caught during local development.

When you run your ASP.NET Core app on your laptop connecting to a local database, the network latency between the web server and the database is effectively zero milliseconds. If your code executes 500 individual SQL queries to render a page, it might still complete in 30ms locally.

However, in a production cloud environment, your web server (e.g., an AWS EC2 instance) and your database (e.g., AWS RDS) are physically separate machines. Even in the same availability zone, network physics dictates a ~1ms to 2ms round-trip latency for every single query.

If your code executes 500 queries, that is immediately 1 full second of dead time where your thread is just waiting for network packets. Combine that with a spike of concurrent users, and your database connection pool is instantly exhausted. This is why .NET 8 Performance optimizations must focus heavily on network I/O.

2. What Exactly is the N+1 Query Problem?

The N+1 query problem occurs when an Object-Relational Mapper (like Entity Framework Core) executes one initial query to retrieve a list of records (the "1"), and then sequentially executes an additional query for each record in that list to retrieve related data (the "N").

Let's look at a classic, highly destructive example. You have a Blog entity that contains a list of Post entities. You want to print out how many posts each blog has.

// 🚨 THE TRAP
// Query 1: Gets all 100 blogs from the database
var blogs = await _db.Blogs.ToListAsync(); 

foreach (var blog in blogs)
{
    // Query N: EF Core hits the DB to get the posts for EACH INDIVIDUAL blog
    Console.WriteLine($"Blog {blog.Name} has {blog.Posts.Count} posts."); 
}

If you have 100 blogs in your system, EF Core will execute 101 separate SQL queries. One to get the blogs, and 100 individual SELECT * FROM Posts WHERE BlogId = x queries inside the loop.

3. The Lazy Loading Trap

How is this even possible? Why does blog.Posts.Count trigger a database call?

In older versions of Entity Framework (and currently if you explicitly opt-in via Proxies), Lazy Loading is enabled by default. Lazy loading is a feature where EF Core intercepts your property access. If the related Posts data hasn't been loaded into memory yet, EF Core will automatically pause execution, construct a SQL query, hit the database, hydrate the objects, and return them seamlessly.

It is designed to make database programming feel like standard Object-Oriented C#. But it is a massive architectural trap. It hides the reality of network I/O behind a simple dot property access.

4. How to Spot N+1 Queries (Logging)

The easiest way to spot N+1 queries before they hit production is to enable raw SQL logging in your development environment.

In your Program.cs, when configuring your Dependency Injection for the DbContext, tell it to log directly to the console:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString)
           // Log SQL queries to the console in development
           .LogTo(Console.WriteLine, LogLevel.Information)
           // Throw an exception if we evaluate a query on the client instead of SQL
           .ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning)));

If you load a page locally and your terminal suddenly looks like the Matrix with a massive waterfall of identical SELECT statements scrolling by rapidly, you have just diagnosed an N+1 problem.

5. Solution 1: Eager Loading (Include)

The most common (and heavily documented) fix is to use Eager Loading. This tells EF Core to fetch all the related data in the initial query using a SQL LEFT JOIN. 

// ✅ FIXED: Executes exactly 1 query with a SQL JOIN
var blogs = await _db.Blogs
    .Include(b => b.Posts) // Force EF Core to pull down the posts now
    .ToListAsync();

foreach (var blog in blogs)
{
    // No database call here! The data is already in memory.
    Console.WriteLine($"Blog {blog.Name} has {blog.Posts.Count} posts."); 
}

This transforms 101 network calls into exactly 1 network call. It solves the latency problem immediately.

6. Solution 2: Projection (The Ultimate Fix)

While .Include() stops the N+1 problem, it introduces a different performance issue: Over-fetching.

In our example, we only care about the count of the posts. But .Include(b => b.Posts) forces the database to pull down the entire Post entity. If those posts contain massive VARCHAR(MAX) columns with thousands of words of blog content, you are pulling hundreds of megabytes of text across the network into RAM just to count the number of rows.

The absolute best way to solve this is using LINQ Projections. By using .Select(), you project the data directly into an anonymous type or a lightweight DTO (like a C# Record). 

// ✅ THE OPTIMAL WAY
var blogStats = await _db.Blogs
    .Select(b => new BlogStatsDto
    {
        BlogName = b.Name,
        // EF Core translates this directly into a SQL COUNT() aggregate function!
        PostCount = b.Posts.Count 
    })
    .ToListAsync();

When EF Core parses this LINQ expression tree, it is smart enough to see that you don't actually need the Post data. It generates an incredibly efficient SQL statement:

SELECT b.Name, (
    SELECT COUNT(*) FROM Posts p WHERE p.BlogId = b.Id
) AS PostCount
FROM Blogs b

This generates a single query, transferring just bytes of data instead of megabytes, and utilizing the SQL engine's highly optimized counting algorithms.

7. Solution 3: Split Queries (Cartesian Explosions)

There is one final edge case. What if you actually do need all the data, but you have multiple .Include() statements?

var users = await _db.Users
    .Include(u => u.Orders)
    .Include(u => u.Comments)
    .ToListAsync();

When you join multiple collection navigation properties, SQL Server generates a Cartesian Product. If a user has 100 orders and 100 comments, the resulting SQL table will have 10,000 rows (100x100) of duplicated data sent across the network.

To solve this, EF Core provides Split Queries:

var users = await _db.Users
    .Include(u => u.Orders)
    .Include(u => u.Comments)
    // Tells EF Core to run 3 separate efficient queries instead of 1 massive JOIN
    .AsSplitQuery() 
    .ToListAsync();

This intentionally trades 1 query for 3 queries to save massive amounts of network bandwidth and RAM allocation.

Final Thoughts

Object-Relational Mappers like Entity Framework Core are incredible productivity boosters. But they are dangerous precisely because they hide the brutal reality of the database from you. They make database network calls look identical to simple C# property accesses.

Always remember: every dot you type could be triggering a network call. Log your SQL, use projections whenever possible, and stop the N+1 killer before your application hits production.

(If EF Core LINQ is still generating terrible SQL despite your best efforts, it might be time to bypass the ORM entirely. Check out my guide on mixing EF Core and Dapper for the ultimate data access architecture).

Need to generate C# DTOs fast? If you are writing projections and constantly manually typing out C# DTO records, you are wasting time. Check out my completely free JSON to C# Converter to instantly generate pristine C# classes from your data payloads!

About the Author

Ajay Patel

Software Developer specializing in .NET, C#, and ASP.NET Core. Built .NET Toolbox to give developers privacy-first, browser-based utilities for their daily workflow.

Read more about Ajay
Back to Blog