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

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
        }
    }
}

Last modified: 08 March 2021