ReSharper
 
2023.2
Shortcuts: Visual Studio
Get ReSharper
Get your hands on the new features ahead of the release by joining the Early Access Program for ReSharper 2025.1! Learn more

Code Inspection: Possible multiple queries to the database (N+1 problem)

Last modified: 11 August 2023

tip

You can suppress this inspection to ignore specific issues, change its severity level to make the issues less or more noticeable, or disable it altogether.

This inspection detects the N+1 Select problem, where unnecessary database calls are made when iterating a collection.

The problem can be illustrated with the following example, where Products and Details are 2 database tables and each Product entry is linked to multiple Detail entries.

// First DB query: get all 'Product' entries from the database
var products = dbContext.Products;

// Iterating 'Product' entries in memory
foreach (var product in products)
{
    // Accessing linked items with 'product.Details'
    // generates a new DB query for each 'Product',
    // which makes it N queries + the first query.
    foreach (var detail in product.Details)
    {
        // Do something
    }
}

To fix this problem, ReSharper suggests using the Include feature of the Entity Framework Core, which will load all linked Detail entries right in the first query:

// Gets all 'Product' and all linked 'Detail' entries from the database
var products = dbContext.Products.Include(x => x.Details);

// Iterating 'Product' entries in memory
foreach (var product in products)
{
    // Accessing linked items with 'product.Details' in memory
    foreach (var detail in product.Details)
    {
        // Do something
    }
}

If the chain of navigational property calls is longer, the quick-fix will add ThenInclude as required:

// Gets all 'Product', all linked 'Detail', and all linked 'Line' entries from the database
var products = dbContext.Products.Include(x => x.Details).ThenInclude(x => x.Lines);
// Iterating 'Product' entries in memory
foreach (var product in products)
{
    // Accessing linked items with 'product.Details' in memory
    foreach (var detail in product.Details)
    {
        // Accessing linked items with 'detail.Lines' in memory
        foreach (var line in detail.Lines)
        {
            // Do something
        }
    }
}

note

Using multi-level Include/ThenInclude calls may lead to a cartesian explosion (depending on your database configuration). You may want to consider adding the AsSplitQuery() method to your query when using Entity Framework Core.

See also

Concepts