Entity Framework Core 7: N+1 Queries Problem

The N+1 queries problem has been our constant companion since day one of Entity Framework (Core). Entity Framework Core 2 (EF 2) introduced a new feature that caused the "N+1 queries problem" more often and was more difficult to detect, so it was removed in the following version. After a little back and forth, let's see how Entity Framework Core 7 (EF 7) handles this issue and why it will likely remain in the future.

In diesem Artikel:

Entity Framework Core 7: N+1 Queries Problem
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

What is the "N+1 Queries Problem"?

The N+1 Queries Problem is an O/R mapper specific issue. It may occur when trying to load data from a table (like ProductGroups) along with its related data (like Products) having a one-to-many or many-to-many relationship when the related data is on the many-side.

Please note: I this article I use synchronous methods for better readability.
Also note: In this article we are talking about one-to-many/many-to-many relationships only. A one-to-one relationship does not cause the N+1 Queries Problem.

In general, there are 2 reasons for the problem to arise. The first one is either a limitation, bug, a (controversial) feature, or a design flow in the O/R mapper itself. For example, EF 2 executed N+1 SQL statements with following LINQ query: 1 statement for loading N ProductGroups and N statements for loading the Products.

				
					var groups = Context.ProductGroups
          .Where(g => g.Name.Contains("Group"))
          .Select(g => new
          {
            ProductGroup = g,
            Product = g.Products.FirstOrDefault()
          })
          .ToList();
				
			

The second reason is an inappropriate use of the O/R mapper itself. Loading data in a loop, as depicted below, will lead to N+1 roundtrips to the database.

				
					var groups = Context.ProductGroups.ToList();
var products = new List<Product>();

foreach (var group in groups)
{
   var product = Context.Products.FirstOrDefault(p => p.ProductGroupId == group.Id);
   products.Add(product);
}
				
			

Hidden Issues in EF 7?

In the early versions, there were some LINQ queries that lead to N+1 Queries Problem. With Entity Framework Core 3 (EF 3) those queries usually raised an InvalidOperationException.

I’m glad to say that none of the culprit queries from the past are leading to the N+1 issue. Entity Framework gets better with every version. EF 7 even translates GroupBy, which was impossible a few versions ago.

				
					var firstProducts = Context.Products
                  .GroupBy(p => p.ProductGroupId)
                  .Select(g => g.FirstOrDefault())
                  .ToList();
				
			

The previous query translates to the following SQL statement:

				
					SELECT *
FROM (
    SELECT [p].[ProductGroupId]
    FROM [Products] AS [p]
    GROUP BY [p].[ProductGroupId]
) AS [t]
LEFT JOIN (
    SELECT *
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY [p0].[ProductGroupId] ORDER BY [p0].[Id]) AS [row]
        FROM [Products] AS [p0]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[ProductGroupId] = [t0].[ProductGroupId]

				
			

Lazy Loading

Nothing has changed regarding Lazy Loading. The feature should be used with care.
Loading of all Products for 10 ProductGroups with the following LINQ query leads to 11 SQL statements.

				
					var lookup = Context.Products
                    .ToLookup(p => p.Group.Name);

				
			

One SQL statement for loading all Products and 10 for loading the ProductGroups.

				
					// executed once
SELECT * FROM [Products] AS [p]

// excuted N times
SELECT [g].[Id], [g].[Name]
FROM [ProductGroups] AS [g]
WHERE [g].[Id] = @__p_0

				
			

The issue can be solved using eager loading, i.e., via Include or by the explicit selection of the required properties and entities. The latter approach is more optimal because EF 7 loads only the name instead of the whole ProductGroup.

				
					var lookup = Context.Products
                    .Include(p => p.Group)
                    .ToLookup(p => p.Group.Name);

// Alternative: explicit selection of the ProductGroup-Name
var lookup = Context.Products
                    .Select(p => new
                                 {
                                    Product = p,
                                    p.Group.Name
                                 })
                    .ToLookup(p => p.Name, p => p.Product);
				
			

The Issue Remains

I think it is safe to say that EF 7 is not susceptible to N+1 Queries Problem on its own. Still, the issue will haunt us forever, and the reason has nothing to do with Entity Framework (Core). Any database request made directly or indirectly within a loop will lead to multiple SQL statements and should be avoided. But it is easier said than done. I can’t remember any project which didn’t make requests inside a loop (unintentionally) because they are not so easy to spot, like in the example at the beginning of this article. Here is another example of a query that is executed indirectly within a loop.

				
					var products = Context.Products.ToList();
var prices = new List<Price>();

foreach (var product in products)
{
   var price = GetPrice(product.Id);
   prices.Add(price);
}

// ---------------

private Price GetPrice(int productId)
{
   // oversimplified

   return Context.Prices.FirstOrDefault(p => p.ProductId == productId);
}
				
			

The previous example is still very easy to spot and to fix because the code fragments are close together. Now put these fragments into different C# files and add a few other methods between the foreach-loop and the LINQ query Context.Prices. Depending on the depth of the call stack and the complexity of the code, the issue will be very hard to detect.
Furthermore, there are loops that don’t look like loops, i.e., they don’t have the usual keywords like for, foreach, or while. The following LINQ statement is one of them:

				
					var products = Context.Products.ToList();

var prices = products.Select(product => GetPrice(product.Id))
                     .ToList();
				
			

Other „hidden loops“ to look out for are recursions.

Summary

Although EF 7 runs into N+1 Queries Problem with Lazy Loading only, still, the problem is and will remain in our projects due to loops.

Some recommendations: better code structure, reduced call stack depth, and check the number of executed SQL statements in integration tests (see IDbCommandInterceptor).

Kostenloser
Newsletter

Aktuelle Artikel, Screencasts, Webinare und Interviews unserer Experten für Sie

Verpassen Sie keine Inhalte zu Angular, .NET Core, Blazor, Azure und Kubernetes und melden Sie sich zu unserem kostenlosen monatlichen Dev-Newsletter an.

Diese Artikel könnten Sie interessieren
.NET
Entity Framework Core: User-defined Fields and Tables

Entity Framework Core: User-defined Fields and Tables

The requirement to store additional fields, unknown at development time, in a relational database is not new. Nonetheless, none of the projects I know of are willing to change the database structure at runtime. What if there is a project which needs dynamically created fields and doesn't want or cannot use entity–attribute–value model or switch to No-SQL databases?
20.09.2022
Blazor
[Sneak Preview] Blazor WebAssembly: The Power Of EF Core And SQLite In The Browser – In-Depth

[Sneak Preview] Blazor WebAssembly: The Power Of EF Core And SQLite In The Browser – In-Depth

Rumor has it, this is the dream of many .NET developers: Using SQLite and EF Core in the browser with Blazor WebAssembly. Is this possible? In this article, we will have a look how you can manage your offline data with the help of SQLite and EF Core by persisting the data with standard browser features and APIs.
12.04.2022
Entity Framework Core
Entity Framework Core: Default Comparer For Byte Arrays May Waste Lots Of Memory And CPU

Entity Framework Core: Default Comparer For Byte Arrays May Waste Lots Of Memory And CPU

The default implementation of Entity Framework Core prefers to play it safe (for good reasons) when working with byte arrays. This 'safety' is - in some use cases - unnecessary and costs us a lot of memory and CPU. In this article, we will see that doing less is sufficient for the given property thanks to one of the most overlooked features of Entity Framework.
24.03.2022
.NET CORE
Entity Framework Core 5 Performance: Power Of Table Hints

Entity Framework Core 5 Performance: Power Of Table Hints

A few months ago, I had to implement a Web API for a relatively simple use case, but there was a challenge. The web endpoint must read and update a specific record using Entity Framework Core 5 and be capable of handling thousands of requests per second. Another requirement was the *data integrity*, so a transaction was a must. With high concurrency and Microsoft SQL Server as the database, we can end up in a deadlock if the SQL Server locks the records or rather the pages in an *unfavorable* way. Let's see how *table hints* can help us.
29.07.2021
.NET
Do Not Waste Performance By Not Using Temp Tables With Entity Framework Core

Do Not Waste Performance By Not Using Temp Tables With Entity Framework Core

It has been a while since I released my article about the usage of temp tables in Entity Framework (v6). Meanwhile, Microsoft has released a completely rewritten version of its O/R mapper so my old approach is no longer applicable. But before we learn about a new one, let us think about what we might need temp tables for.
25.06.2020
.NET
Better Entity Framework Core Performance By Reading Execution Plans

Better Entity Framework Core Performance By Reading Execution Plans

Both a LINQ query and an SQL statement are descriptions that state which data should be fetched, but not how.. Sure, when reading LINQ or SQL, we can make assumptions about the performance but not in every case. Some queries are either too fancy or too big to grasp, so our predictions may be way too vague if we can make any at all.
10.06.2020