Entity Framework Core Performance: Beware of N+1 Queries
Entity Framework Core: Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 1 - Code First)

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.

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.

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

The comments to this entry are closed.