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

Imagine this: You build a new dashboard for your application. You test it locally with a local SQL Server database. It loads instantly. You are a genius.

You deploy it to production. Suddenly, your APM tools are firing alerts, the database CPU is pinned at 100%, and the dashboard takes 8 seconds to load.

What went wrong? Nine times out of ten, you are a victim of the N+1 Query Problem.

What is the N+1 Query Problem?

The N+1 query problem occurs when your ORM (Entity Framework Core) executes one query to retrieve a list of records (the "1"), and then executes an additional query for each record to retrieve related data (the "N").

Let's look at a classic example. You have a Blog entity that contains a list of Post entities.

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

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

If you have 100 blogs, EF Core will execute 101 separate SQL queries. Locally, with zero network latency, 101 queries run in 5 milliseconds. In production, where the database is hosted in a separate AWS availability zone with 2ms of network latency per hop... you just introduced a 200ms delay just from network travel time alone.

Multiply that by a few concurrent users, and your database connection pool is instantly exhausted.

How to Spot It

The easiest way to spot N+1 queries is to turn on EF Core SQL logging in development. In your Program.cs, configure your DbContext to log to the console:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

If you load a page and your console looks like the Matrix with a waterfall of identical SELECT statements scrolling by, you have an N+1 problem.

How to Fix It

1. Eager Loading (Include)

The most common fix is to tell EF Core to fetch the related data in the initial query using .Include(). This generates a SQL JOIN and returns everything in a single trip to the database.

// ✅ FIXED: Executes exactly 1 query with a SQL JOIN
var blogs = await _db.Blogs
    .Include(b => b.Posts)
    .ToListAsync();

foreach (var blog in blogs)
{
    Console.WriteLine($"Blog {blog.Name} has {blog.Posts.Count} posts."); 
}

2. Projection (The Best Approach)

While .Include() is good, it still pulls down the entire Post entity (including huge text content columns) just to get the count.

The absolute best way to solve this is using .Select() to project the data into an anonymous type or a DTO. EF Core will translate this into an incredibly efficient SQL statement.

// ✅ THE OPTIMAL WAY
var blogStats = await _db.Blogs
    .Select(b => new 
    {
        BlogName = b.Name,
        PostCount = b.Posts.Count // Translates to a SQL COUNT()!
    })
    .ToListAsync();

foreach (var stat in blogStats)
{
    Console.WriteLine($"Blog {stat.BlogName} has {stat.PostCount} posts."); 
}

This generates a single query with a GROUP BY and COUNT(), transferring just bytes of data instead of megabytes.

Final Thoughts

ORMs like Entity Framework Core are incredible productivity boosters, but they hide the reality of the database from you. They make database calls look like simple C# property accesses (blog.Posts.Count).

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