JetBrains Rider 2024.3 Help

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, JetBrains Rider 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: 11 February 2024