Entity Framework Core Performance – Beware Of N+1 Queries

After working with Entity Framework 6 (EF 6) for several years, a software developer can predict the SQL statements being generated by EF just by looking at the LINQ queries. With Entity Framework Core (EF Core) the SQL statement generation has changed - in some cases for the better, in others for the worse.

In diesem Artikel:

pg
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

In this blog post we will check a few LINQ queries and see which of them are executing N+1 SQL statements where N is the number of selected records.

Given is a DbContext with 2 entities Product and ProductGroup. (Repo with sample code: github.com/PawelGerr/Presentation-EntityFrameworkCore)

				
					public class DemoDbContext : DbContext
{
   public DbSet<Product> Products { get; set; }
   public DbSet<ProductGroup> ProductGroups { get; set; }
}

public class Product
{
   public Guid Id { get; set; }
   public string Name { get; set; }

   public Guid GroupId { get; set; }
   public ProductGroup Group { get; set; }
}

public class ProductGroup
{
   public Guid Id { get; set; }
   public string Name { get; set; }

   public ICollection<Product> Products { get; set; }
}
				
			

Let’s print out all product groups having the word "Group" in their names with corresponding products via Include() first and using Select() second.

				
					// Using Include()
var groups = Context.ProductGroups
               .Include(g => g.Products)
               .Where(g => g.Name.Contains("Group"))
               .ToList();

Print(groups);

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

Print(groups);
				
			

In both cases 2 SQL statements are executed by EF Core: 1 for the product groups and 1 for the products. On the contrary, EF  6 executes just 1 statement. This may imply that the performance of EF 6 is better than the of EF Core, but in practice it is worse because the queries are getting huge and produce more load on the database.

				
					-- Fetching product groups
SELECT [g].[Id], [g].[Name]
FROM [ProductGroups] AS [g]
WHERE CHARINDEX(N'Group', [g].[Name]) > 0
ORDER BY [g].[Id]

-- Fetching products
SELECT [g.Products].[Id], [g.Products].[GroupId], [g.Products].[Name]
FROM [Products] AS [g.Products]
INNER JOIN
(
    SELECT [g0].[Id]
    FROM [ProductGroups] AS [g0]
    WHERE CHARINDEX(N'Group', [g0].[Name]) > 0
) AS [t] ON [ g.Products].[GroupId] = [t].[Id]
ORDER BY [t].[Id]

				
			

Now we don’t take all products but only those with the term "1"  in their names and print them out twice(!). 

				
					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();

Print(groups); // 1st iteration over product groups
Print(groups); // 2nd iteration over product groups
				
			

The result is disappointing. Having 5 product groups matching the condition we get 11 SQL statement executions: 1 query for fetching 5 product groups and (2 * 5 = 10) for fetching the products. Let’s put a ToList() at the end of the products query.

				
					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();
				
			

Now we have 6 (=1+5)  queries being sent to the database, it is getting better but still not satisfying.

				
					-- 1 query for fetching product groups
SELECT [g].[Id], [g].[Name]
FROM [ProductGroups] AS [g]
WHERE CHARINDEX(N'Group', [g].[Name]) > 0

-- 5 queries for fetching products (i.e. 1 query per fetched product group)
SELECT [p].[Id], [p].[GroupId], [p].[Name]
FROM [Products] AS [p]
WHERE (CHARINDEX(N''1'', [p].[Name]) > 0) AND
      (@_outer_Id = [p].[GroupId])
				
			

Obviously, EF Core has some difficulties translating queries if Select() contains a filtered collection. Let’s select just the first product.

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

Print(groups);
				
			

We still getting 6 queries meaning that the “problem” doesn’t lie in the cardinality of the response type (Product vs ICollection<Product>) but in collections in general.

Solutions

We can reduce the number of queries by not using the navigational property Products but doing the “JOIN” by ourselves, for example via GroupJoin.

				
					var productsQuery = Context.Products.Where(i => i.Name.Contains("1"));

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

Print(groups);
				
			

The previous LINQ query produces just 1 query.

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

An alternative solution is to fetch the data separately and doing lookups in .NET.

				
					var groupsQuery = Context.ProductGroups
                  .Where(g => g.Name.Contains("Group"));

var productsByGroupId = groupsQuery
                           .SelectMany(g => g.Products.Where(i => i.Name.Contains("1")))
                           .ToLookup(p => p.GroupId);

var groups = groupsQuery
         .Select(g => new
         {
            ProductGroup = g,
            Products = productsByGroupId[g.Id]
         })
         .ToList();
				
			

The generated SQL statements are easier to handle by the database but there are 2 of them. 

				
					-- For product groups
SELECT [g].[Id], [g].[Name]
FROM [ProductGroups] AS [g]
WHERE CHARINDEX(N'Group', [g].[Name]) > 0

-- For products
SELECT [g.Products].[Id], [g.Products].[GroupId], [g.Products].[Name]
FROM [ProductGroups] AS [g]
INNER JOIN [Products] AS [g.Products]
    ON [g].[Id] = [g.Products].[GroupId]
WHERE (CHARINDEX(N'Group', [g].[Name]) > 0) AND
      (CHARINDEX(N'1', [g.Products].[Name]) > 0)
				
			

Depending on the database model, amount of data, indexes, number of collections and columns being fetched the one or the other solution may perform better.

Closing Words

The query generation of EF Core is not optimal yet but the Entity Framework Team is currently working on the “N+1 queries” problem so we will re-check all queries with EF Core 2.1 very soon.

In general, whether it is EF 6, EF Core or other O/R mapper it is recommended to use a database profiling tool, so we get good understanding of the technology we use yet again.

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.

Newsletter Anmeldung
Diese Artikel könnten Sie interessieren
Database Access with Sessions
.NET
kp_300x300

Data Access in .NET Native AOT with Sessions

.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023
Old computer with native code
.NET
kp_300x300

Native AOT with ASP.NET Core – Overview

Originally introduced in .NET 7, Native AOT can be used with ASP.NET Core in the upcoming .NET 8 release. In this post, we look at the benefits and drawbacks from a general perspective and perform measurements to quantify the improvements on different platforms.
02.11.2023
.NET
kp_300x300

Optimize ASP.NET Core memory with DATAS

.NET 8 introduces a new Garbage Collector feature called DATAS for Server GC mode - let's make some benchmarks and check how it fits into the big picture.
09.10.2023
.NET CORE
pg

Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

With the version 4.3.1 of Microsoft.CodeAnalysis.* Roslyn provides a new high-level API - the method "ForAttributeWithMetadataName". Although it is just 1 method, still, it addresses one of the biggest performance issue with Source Generators.
16.05.2023
AI
favicon

Integrating AI Power into Your .NET Applications with the Semantic Kernel Toolkit – an Early View

With the rise of powerful AI models and services, questions come up on how to integrate those into our applications and make reasonable use of them. While other languages like Python already have popular and feature-rich libraries like LangChain, we are missing these in .NET and C#. But there is a new kid on the block that might change this situation. Welcome Semantic Kernel by Microsoft!
03.05.2023
.NET
sg

.NET 7 Performance: Regular Expressions – Part 2

There is this popular quote by Jamie Zawinski: Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."

In this second article of our short performance series, we want to look at the latter one of those problems.
25.04.2023