The Silent Killer of ASP.NET Core Performance: The N+1 Query Problem

It’s a classic story:

You build an API endpoint in ASP.NET Core using Entity Framework Core. You test it locally with 10 dummy records in your database, and it responds in a lightning-fast 15 milliseconds.

You push it to production. A few weeks later, as the user base and data grow, users start complaining that the app is loading painfully slow. You check your APM logs, and to your horror, a single HTTP GET request is triggering 1,001 SQL queries to the database.

Welcome to the N+1 Query Problem.

---

What Actually Happens Under the Hood?

The N+1 problem occurs when your application executes 1 query to fetch a parent record (or list of records), and then executes N additional queries to fetch related child data for every single parent item in that list.

Consider this innocent-looking LINQ code:

// 1 Query to fetch 100 active customers
var customers = await _context.Customers
    .Where(c => c.IsActive)
    .ToListAsync();

foreach (var customer in customers)
{
    // N Queries executed inside the loop!
    var latestOrder = await _context.Orders
        .FirstOrDefaultAsync(o => o.CustomerId == customer.Id);

    // Process order...
}

If you have 100 customers:

1 Query fetches the list of customers.

100 Queries are fired inside the foreach loop to get each customer's order.

Total database roundtrips = 101. Multiply that by network latency, and your database connection pool starts crying for help.

How to Catch It Before It Hits Production
EF Core makes lazy loading or lazy queries inside loops deceptively easy. Here are three ways to hunt down N+1 queries in your codebase:

1. Enable SQL Logging in Development In your appsettings.Development.json, set EF Core logging to Information:

"Logging": {
  "LogLevel": {
    "Default": "Information",
    "Microsoft.EntityFrameworkCore.Database.Command": "Information"
  }
}

Watch your terminal output. If you see a wall of identical SELECT statements scrolling past for a single request, you've got an N+1 issue.

1. Throw Exceptions on Unintended Queries If you use Lazy Loading, you can explicitly configure EF Core to throw an exception in development whenever a query is triggered implicitly:

optionsBuilder.UseSqlServer(connectionString)
    .ConfigureWarnings(warnings => 
        warnings.Throw(RelationalEventId.MultipleCollectionIncludeWarning));

The Fix: Eager Loading & Projection
Instead of fetching data inside loops, tell EF Core exactly what related data you need up-front so it generates a single JOIN, or project directly into a DTO.

Approach A: Eager Loading (Include)

// Generates a SINGLE SQL JOIN query
var customersWithOrders = await _context.Customers
    .Include(c => c.Orders)
    .Where(c => c.IsActive)
    .ToListAsync();

Approach B: Direct Projection (Best Performance)
Only fetch the fields your API actually returns. This avoids fetching unneeded columns and forces EF Core to construct an optimized SQL query:

var customerDtos = await _context.Customers
    .Where(c => c.IsActive)
    .Select(c => new CustomerDto
    {
        CustomerId = c.Id,
        CustomerName = c.Name,
        LatestOrderDate = c.Orders.Max(o => o.OrderDate)
    })
    .ToListAsync();

Conclusion
N+1 queries rarely show up as errors—they just quietly degrade your performance as your database scales. By adopting direct DTO projection and monitoring SQL logs during local development, you can catch these bottlenecks before your production database takes a hit.

How do you usually catch hidden ORM query issues in your team? Do you rely on APM tools, EF Core logging, or static analysis tools?

Let me know in the comments below!

Comments

[Mads Hansen]

Projection is usually the right default, but I’d be careful with “single JOIN = fixed.” Multiple collection Includes can replace N+1 with a cartesian explosion; that warning is about that shape, not implicit lazy-loading queries. AsSplitQuery() can deliberately use a small fixed number of round trips and still be better than one huge duplicated result set. A regression test can count DbCommands per request with a command interceptor and assert a constant upper bound while scaling fixture parents from 10 to 1,000. Pair that with returned-row/byte counts and p95 connection-pool wait. For “latest order,” make the ordering deterministic (OrderByDescending(OrderDate).ThenByDescending(Id).Select(...).FirstOrDefault()), since FirstOrDefault without ordering and Max alone do not necessarily return the same business record.