Your EF Core code can look clean, pass every functional test, and still make hundreds of database calls for a single API request.
That is the N+1 query problem.
It is one of those performance issues that can remain invisible during development because small datasets make inefficient database access look harmless. Once the same application handles larger datasets and real concurrency, the extra database round trips can become a significant performance bottleneck.
In this article, we'll look at:
- What the N+1 query problem actually means
- How it develops in EF Core
- Why it is easy to miss during development
- How to measure database query behavior
- When to use
Include(),Select(), or explicit loading - Why fewer queries isn't always the goal
- How to prevent N+1 problems from reaching production
What Is the N+1 Query Problem?
The N+1 pattern occurs when an application executes:
- 1 query to retrieve a collection of records
- N additional queries to retrieve related data for each record
For example, suppose an API retrieves 100 invoices:
var invoices = await context.Invoices
.ToListAsync();
That is one database query.
Now imagine the application accesses a related customer for every invoice:
foreach (var invoice in invoices)
{
Console.WriteLine(invoice.Customer.Name);
}
If lazy loading is enabled and configured, accessing invoice.Customer can trigger another database query for each invoice.
The resulting database activity could look like:
1 query for invoices
+ 100 queries for customers
---------------------------
101 database queries
That is the N+1 pattern.
The important point is that N is not always 100. It depends on how many parent records are returned.
For 10 records:
1 + 10 = 11 queries
For 1,000 records:
1 + 1,000 = 1,001 queries
The problem scales with the data.
Does EF Core Automatically Cause N+1 Queries?
No.
Simply using EF Core or having navigation properties does not automatically mean your application has an N+1 problem.
N+1 behavior typically develops when related data is loaded repeatedly, such as through lazy loading or application code that performs additional queries inside a loop.
For example:
var orders = await context.Orders
.ToListAsync();
foreach (var order in orders)
{
var customer = await context.Customers
.FirstAsync(c => c.Id == order.CustomerId);
Console.WriteLine(customer.Name);
}
The problem is easier to see here:
1 query → Orders
N queries → Customer for each Order
The code is functionally correct.
That is what makes N+1 dangerous.
Why N+1 Queries Are Easy to Miss
N+1 problems often survive development and testing because developers test with small datasets.
Imagine testing an API with five records:
1 + 5 = 6 queries
Six queries may not look concerning.
But production could return 500 records:
1 + 500 = 501 queries
Now consider concurrent requests.
If 20 users trigger the endpoint at roughly the same time:
501 queries × 20 requests
= 10,020 database queries
The exact impact depends on query complexity, latency, database capacity, concurrency, indexing, and other workload characteristics, but the example illustrates how quickly query counts can grow.
This is why functional correctness is not enough to validate database performance.
What Does N+1 Look Like in Production?
The problem is not just the number of SQL statements.
Every additional database round trip can contribute to:
- Higher API latency
- Increased database workload
- More connection and resource pressure
- Higher infrastructure costs
- Reduced scalability
- Greater risk of SLA/SLO violations
- Poorer user experience
And the problem can compound as data volume and concurrent traffic increase.
A page that feels perfectly responsive with development data can behave very differently when the application processes thousands or millions of production records.
How to Detect N+1 Queries in EF Core
The first rule is simple:
Measure the SQL behavior instead of assuming the LINQ code tells the whole story.
1. Enable EF Core SQL Logging
During development, EF Core logging can help reveal how many SQL commands are being executed.
For example:
builder.Services.AddDbContext<AppDbContext>(options =>
{
options
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information);
});
You can then inspect the generated SQL.
If you expected one database operation but see a repeated pattern such as:
SELECT ... FROM Orders
SELECT ... FROM Customers WHERE Id = 1
SELECT ... FROM Customers WHERE Id = 2
SELECT ... FROM Customers WHERE Id = 3
...
you may have an N+1 problem.
2. Count Queries Per Request
SQL logs are useful, but query counts are even more valuable when you want to detect regressions.
For example, you might establish a rule such as:
Endpoint: GET /api/orders
Expected SQL commands: ≤ 5
Actual SQL commands: 87
That immediately gives the development team something measurable to investigate.
For critical APIs, query-count thresholds can become part of automated performance testing.
3. Inspect Generated SQL
LINQ can hide database behavior.
This query:
var orders = await context.Orders
.Where(o => o.Status == "Open")
.ToListAsync();
looks simple.
But what matters in production is the SQL EF Core actually sends to the database.
Use SQL logging and profiling tools to understand:
- Number of SQL commands
- Query duration
- Returned rows
- Repeated queries
- Large result sets
- Expensive joins
- Unexpected database access
The database workload—not just the C# syntax—is what ultimately affects performance.
How to Fix the N+1 Problem in EF Core
There isn't one universal solution.
The right approach depends on the data you actually need.
Three common approaches are:
Include()Select()- Explicit loading
1. Use Include() When You Need Related Entities
If an endpoint genuinely needs the related entity data, eager loading can avoid repeatedly loading related records.
For example:
var orders = await context.Orders
.Include(o => o.Customer)
.ToListAsync();
You can then access:
foreach (var order in orders)
{
Console.WriteLine(order.Customer.Name);
}
without relying on per-record lazy loading for the customer relationship.
However, Include() should not automatically be treated as the best solution for every query.
If you load a large or deeply connected object graph, you may retrieve much more data than the application actually needs.
2. Prefer Select() When You Only Need Specific Fields
For read-only APIs, projections are often a better fit.
Instead of loading complete Order and Customer entities:
var orders = await context.Orders
.Include(o => o.Customer)
.ToListAsync();
you might project directly into the response model:
var orders = await context.Orders
.Select(o => new OrderSummary
{
OrderId = o.Id,
CustomerName = o.Customer.Name,
Total = o.Total
})
.ToListAsync();
This communicates exactly what the API needs.
Projection can help reduce:
- Unnecessary columns
- Object materialization
- Memory usage
- Data transfer
- Complex entity graphs
For read-heavy APIs and reporting scenarios, Select() is often worth considering before reaching for broad Include() calls.
3. Use Explicit Loading When You Need More Control
Sometimes related data is needed conditionally.
Explicit loading allows you to decide when the relationship should be loaded:
var order = await context.Orders
.FirstAsync(o => o.Id == orderId);
await context.Entry(order)
.Reference(o => o.Customer)
.LoadAsync();
This provides more control, but it also adds complexity.
It is most useful when the application genuinely needs conditional or targeted loading rather than loading an entire object graph upfront.
Include() vs Select() vs Explicit Loading
| Approach | Best For | Main Benefit | Potential Concern |
|---|---|---|---|
Include() |
Entity graphs | Convenient related-data loading | Can load more data than necessary |
Select() |
Read APIs / DTOs | Retrieves only required fields | Requires projection design |
| Explicit loading | Conditional relationships | Fine-grained control | More verbose and easier to misuse |
The important question isn't:
"Which one is always fastest?"
Instead ask:
"What data does this operation actually need?"
Fewer Queries Isn't Always Better
This is an important distinction.
The goal isn't simply to reduce:
100 queries → 1 query
at any cost.
A single enormous query can also create problems.
For example, loading a large object graph with multiple collection relationships can result in:
- Large result sets
- Excessive joins
- Duplicate data in result rows
- Higher memory consumption
- More complicated SQL
- Longer database execution times
So the real objective is:
Predictable database behavior and appropriate data access—not simply the lowest possible query count.
Sometimes two well-designed queries are better than one unnecessarily large query.
A Practical Example
Consider an insurance dashboard that displays:
- Policy information
- Customer details
- Coverage information
- Recent claims
A simplistic implementation might load policies first and then retrieve related data repeatedly.
With 400 policies, the application could unexpectedly generate hundreds of database calls.
The better approach is to first understand what the dashboard actually needs.
If it only needs:
Policy Number
Customer Name
Coverage Type
Claim Count
then loading complete entities and their entire relationship graph may be unnecessary.
A projection could retrieve the required read model directly:
var dashboard = await context.Policies
.Where(p => p.IsActive)
.Select(p => new PolicyDashboardDto
{
PolicyNumber = p.PolicyNumber,
CustomerName = p.Customer.Name,
CoverageType = p.Coverage.Type,
ClaimCount = p.Claims.Count()
})
.ToListAsync();
The key is that the application is designed around the required data rather than around the entity structure alone.
How to Prevent N+1 Problems Before Production
Fixing an N+1 problem after customers report slow performance is much more expensive than detecting it during development.
A practical prevention strategy includes:
1. Review SQL behavior
Don't review only the LINQ.
Review the generated SQL and query count.
2. Test with realistic datasets
Five records aren't enough to validate a query pattern that will process 5,000 records in production.
3. Monitor query counts
For important APIs, establish reasonable query-count thresholds.
4. Profile slow endpoints
Look at:
- Request duration
- SQL execution time
- Number of database commands
- Rows returned
- Database resource usage
5. Review data-loading strategy
Ask whether the endpoint really needs:
Full entity
+ related entity
+ nested collection
+ another nested collection
or whether a small projection is enough.
6. Include database behavior in performance testing
A performance test should measure more than HTTP response time.
It should also help answer:
How much database work was required to produce this response?
A Simple EF Core N+1 Checklist
Before shipping a data-heavy endpoint, ask:
- [ ] How many SQL commands does one request execute?
- [ ] Does a loop trigger additional database calls?
- [ ] Is lazy loading enabled?
- [ ] Are related entities actually required?
- [ ] Could
Select()return only the required fields? - [ ] Would
Include()load too much data? - [ ] Would explicit loading provide better control?
- [ ] Have we tested with realistic data volumes?
- [ ] Have we tested under concurrent requests?
- [ ] Do we have query-count or performance thresholds?
- [ ] Have we inspected the generated SQL?
If you can't answer these questions, the endpoint probably needs more database-level testing.
The Real Lesson
The N+1 query problem is not simply an EF Core coding mistake.
It is a data-access and scalability problem.
The C# code may look clean.
The API may return the correct response.
The automated tests may pass.
But the database may still be doing far more work than necessary.
The most effective approach is to:
Measure → Understand → Optimize → Test at scale → Monitor
And remember:
The goal isn't simply fewer queries. The goal is predictable performance at scale.
If you're building or maintaining ASP.NET Core applications with EF Core, measuring actual database behavior should be part of your performance workflow—not something you investigate only after production slows down.
Read the Full Analysis
For a deeper breakdown of the N+1 problem, production impact, measurement techniques, and EF Core optimization strategies, read the full ConvergeSol guide: 100 Database Queries vs 1 Query: Measuring the N+1 Problem in EF Core
Top comments (0)