Entity Framework Core: Avoiding N+1 Queries and Overfetching

Entity Framework Core makes database access convenient, but convenient code does not always produce efficient SQL. Two common performance problems are the N+1 query pattern and overfetching. Both can increase database load and response times as an application grows.

Understand the N+1 query problem

The N+1 problem occurs when an application executes one query to retrieve a collection and then executes another query for each item to retrieve related data.

Consider loading customers and then accessing each customer’s orders:

var customers = await dbContext.Customers.ToListAsync();

foreach (var customer in customers)
{
    Console.WriteLine(customer.Orders.Count);
}

Depending on the relationship configuration and loading strategy, accessing Orders can trigger additional database queries. One query loads the customers, and subsequent queries load their orders.

The actual number of queries depends on the EF Core configuration and how navigation properties are loaded. Lazy loading is a common cause of this pattern.

Load related data deliberately

When the application needs related entities, Include can retrieve them as part of the query:

var customers = await dbContext.Customers
    .Include(customer => customer.Orders)
    .ToListAsync();

This reduces the risk of repeatedly querying for each customer’s orders. However, eager loading is not automatically the best solution for every endpoint. Loading several large collections can produce excessive joins and duplicate data in the result set.

Use eager loading when the application genuinely needs the related entities, and inspect the generated SQL when performance matters.

Project only the required fields

An API often needs a small representation of an entity rather than the entire database object.

Projection with Select makes that requirement explicit:

var customers = await dbContext.Customers
    .Select(customer => new CustomerSummaryDto
    {
        Id = customer.Id,
        Name = customer.Name,
        OrderCount = customer.Orders.Count()
    })
    .ToListAsync();

EF Core can translate many such expressions into SQL, allowing the database to return only the fields required by the application.

Projection is particularly useful for list endpoints, dashboards, and reporting queries. It also avoids accidentally serializing unnecessary entity properties.

Use pagination for large collections

Returning thousands of records in one response can increase database work, memory consumption, network traffic, and serialization time.

For straightforward page-based pagination:

var customers = await dbContext.Customers
    .OrderBy(customer => customer.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(customer => new CustomerSummaryDto
    {
        Id = customer.Id,
        Name = customer.Name
    })
    .ToListAsync();

The application should validate page and pageSize before using them. Stable ordering is also important. For very large datasets or frequently changing records, keyset pagination may perform better than large offsets.

Use no-tracking queries where appropriate

For read-only queries, AsNoTracking avoids the overhead of tracking returned entities:

var products = await dbContext.Products
    .AsNoTracking()
    .Where(product => product.IsActive)
    .ToListAsync();

This is useful for read-heavy endpoints that do not modify the returned entities. Tracking remains appropriate when the application intends to update entities through the same context.

Measure before optimizing

Use EF Core logging, database query plans, and application tracing to determine where time is spent. Inspect the number of SQL commands, the size of returned datasets, and whether the database uses suitable indexes.

Avoid assuming that fewer queries always means faster execution. One enormous query can be worse than a small number of well-designed queries.

Conclusion

Avoiding N+1 queries and overfetching requires deliberate data-access design. Load related data only when needed, project to DTOs, paginate large collections, and measure actual database behavior. These practices help maintain predictable API performance as data volumes increase.