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.
