Entity Framework Core 2.1 Performance – Beware Of N+1 Queries (Revisited)

In the previous post we have identified some Entity Framework (EF) LINQ queries that are affected by so called N+1 queries problem. In the meantime a new version (2.1-RC1) of Entity Framework has been released so we check the SQL statement generation yet another time.

In this article:

pg
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.
Samples: Github-Repo Positive thing(s) first… In the previous version the selection of a filtered collection was affected by the problem – with and without ToList() but not anymore
				
					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();
				
			

Adding ToList() leads to 2 SQL statements instead of N+1 where N is the number of selected product groups.

1 query for fetching of the product groups:

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

And 1 query for fetching of the products:

				
					SELECT
    [g.Products].[Id], [g.Products].[GroupId], [g.Products].[Name], [t].[Id]
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]
WHERE
    CHARINDEX(N'1', [g.Products].[Name]) > 0
ORDER BY
    [t].[Id]
				
			

Alas, the usage of FirstOrDefault() is still producing N+1 queries

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

and at the moment GroupBy() is not as powerful as in EF 6 so the following query fetches the whole table instead of the first product for each product group.

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

The corresponding SQL statement is:

				
					SELECT
    [p].[Id], [p].[GroupId], [p].[Name]
FROM
    [Products] AS [p]
ORDER BY
    [p].[GroupId]
				
			

There is a lot of work to do but we are getting there… until then keep using your favorite profiling tool.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

EN Newsletter Anmeldung (#7)
Related Articles
.NET
KP-round
.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
.NET
KP-round
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-round
.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