Is „N+1 Queries“ Still A Performance Issue In Entity Framework Core 3?

In a previous post we saw that EF 2.1 is highly susceptible to the N+1 queries problem. After the release of a new version of Entity Framework Core (EF) the first question coming to mind is: "Is it still a big issue in EF 3.1?" And if the answer is no, is there anything else we have to be aware of?

In diesem Artikel:

Is „N+1 Queries“ Still A Performance Issue In Entity Framework Core 3?
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

Examples on Github:

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 relationship whith the related data is on the many-side.

An example of a LINQ query that may lead to N+1 Queries with some O/R mappers:

				
					Context.ProductGroups
          .Select(g => new
          {
            ProductGroup = g,
            Products = g.Products
          })
          .ToList();
				
			

In this example an O/R mapper may execute 1 SQL statement to load N ProductGroups and after that N SQL statements to load Products for each ProductGroup separately. Hence the name: N+1 Queries.

Let’s revisit the previous blog post’s queries to see whether EF 3.1 still suffers from the N+1 Queries Problem as earlier versions of EF did.

Fetching a collection navigational property

Fetching a collection navigational property in EF 2.1 leads to 2 queries, one for ProductGroups and the other for the Products.

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

With EF 3.1 we get just 1 SQL statement that fetches both, the ProductGroups and Products.

				
					SELECT
    [p].[Id], [p].[Name], [p].[RowVersion],
    [t].[Id], [t].[GroupId], [t].[Name], [t].[RowVersion]
FROM [ProductGroups] AS [p]
LEFT JOIN 
(
    SELECT [p0].[Id], [p0].[GroupId], [p0].[Name], [p0].[RowVersion]
    FROM [Products] AS [p0]
    WHERE CHARINDEX(N'1', [p0].[Name]) > 0
) AS [t] ON [p].[Id] = [t].[GroupId]
WHERE CHARINDEX(N'Group', [p].[Name]) > 0
ORDER BY [p].[Id], [t].[Id]
				
			

This example proves that neither EF 2.1 nor 3.1 causes the N+1 Queries Problem, yet there are some differences:

  • EF 2.1 requires ToList() when fetching Products, EF 3.1 does not
  • EF 2.1 executes 2 SQL statements, EF 3.1 just 1

Using "FirstOrDefault" inside "Select"

When selecting the first Product for each ProductGroup using FirstOrDefault() EF 2.1 executes 1 SQL statement for loading N ProductGroups and N SQL 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();
				
			

As in the previous section, EF 3.1 executes just 1 SQL statement for loading all requested data.

				
					SELECT
    [p].[Id], [p].[Name], [p].[RowVersion],
    [t0].[Id], [t0].[GroupId], [t0].[Name], [t0].[RowVersion]
FROM [ProductGroups] AS [p]
LEFT JOIN
(
    SELECT [t].[Id], [t].[GroupId], [t].[Name], [t].[RowVersion]
    FROM
    (
        SELECT
            [p0].[Id], [p0].[GroupId], [p0].[Name], [p0].[RowVersion],
            ROW_NUMBER() OVER(PARTITION BY [p0].[GroupId] ORDER BY [p0].[Id]) AS [row]
        FROM [Products] AS [p0]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [p].[Id] = [t0].[GroupId]
WHERE CHARINDEX(N'Group', [p].[Name]) > 0
				
			

With respect to the N+1 Queries Problem, the new behavior of the SQL statement generator is an improvement. We can now use FirstOrDefault without producing dozens or even hundreds of SQL statements.

Usage of „FirstOrDefault“ along with „GroupBy“

The usage of FirstOrDefault along with GroupBy in EF 2.1 works, kind of… The LINQ query below leads to client-side evaluation, which again leads to a massive performance loss due to the fact that the whole table Products is fetched into memory.

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

As you may have read in my blog post Entity Framework Core 3.0 – „Hidden“ GROUP BY Capabilities (Part 1)GroupBy has very limited support in EF 3.0/3.1. With the query above we get an InvalidOperationException.

Lazy Loading

Nothing has changed concerning Lazy Loading. Whether EF 2.1 or EF 3.1, we still have to be careful when using this feature.
Here is an example: for 100 Products and 10 ProductGroups the following LINQ query executes 11 SQL statements: 1 for loading the Products and 10 for loading the ProductGroups.

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

				
			

Summary

Personally, I like the behavior of the SQL generator in EF 3.1 better than in EF 2.1 because it gives me more control over SQL statement generation. Previously, if a LINQ query is not translatable to SQL, EF switched to client-side evaluation which could lead to bad performance if it was overlooked. Now, we get an exception and have to do something about it.
Let‘ get back to my opening questions:

  • Is N+1 Queries Problem still a big issue in EF 3.1? – In short no. Sure, Lazy Loading may lead to more executed SQL statements than expected but that is the price we have to pay in order to use this feature.
  • Did the new behavior introduced new issues we have to be aware of? – Unfortunately yes, this new behavior inevitably causes the so-called Cartesian Explosion Problem
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
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
.NET
Unnecessary Fuzzy Searches May Hurt Your Entity Framework Core Performance

Unnecessary Fuzzy Searches May Hurt Your Entity Framework Core Performance

After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one.
24.03.2020