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.

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.

Related Articles

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…
Pawel Gerr
entity framework core
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…
Pawel Gerr
entity framework core
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. What do I mean by…
Pawel Gerr
entity framework core
The performance issue "Cartesian Explosion" made its comeback in Entity Framework Core 3
In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced…
Pawel Gerr