Entity Framework Core does not select the count of child rows

Question

When trying to retrieve the count of child rows, EF 6 behaves correctly: just one nice SQL query. EF Core on the other hand can somewhat order by the count of child rows but does not select it in the generated SQL query. It just iterate through each result and executes a Count(*) SQL query.

Here are my model classes:

public class Parent
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }

    public int ParentId { get; set; }
    public Parent Parent { get; set; }
}

Here is the the query (used for both EF Core and EF 6):

var query = context.Parents
                   .Take(4)
                   .Select(p => new
                                 {
                                     Parent = p,
                                     ChildrenCount = p.Children.Count(),
                                 });

var results = query.OrderBy(x => x.ChildrenCount).ToList();

EF 6 generates this SQL :

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Name] AS [Name], 
    [Project1].[C1] AS [C1]
FROM 
    (SELECT TOP (4) 
         [c].[Id] AS [Id], 
         [c].[Name] AS [Name], 
         (SELECT COUNT(1) AS [A1]
          FROM [dbo].[Children] AS [Extent2]
          WHERE [c].[Id] = [Extent2].[ParentId]) AS [C1]
     FROM [dbo].[Parents] AS [c])  AS [Project1]
ORDER BY [Project1].[C1] ASC

EF Core generates :

exec sp_executesql N'SELECT [t].[Id], [t].[Name]
FROM (
SELECT TOP(@__p_0) [p].[Id], [p].[Name]
FROM [Parents] AS [p]
) AS [t]
ORDER BY (
SELECT COUNT(*)
FROM [Children] AS [c]
WHERE [t].[Id] = [c].[ParentId]
)',N'@__p_0 int',@__p_0=4

And

exec sp_executesql N'SELECT COUNT(*)
FROM [Children] AS [c1]
WHERE @_outer_Id = [c1].[ParentId]',N'@_outer_Id int',@_outer_Id=3

And

exec sp_executesql N'SELECT COUNT(*)
FROM [Children] AS [c1]
WHERE @_outer_Id = [c1].[ParentId]',N'@_outer_Id int',@_outer_Id=1

And

exec sp_executesql N'SELECT COUNT(*)
FROM [Children] AS [c1]
WHERE @_outer_Id = [c1].[ParentId]',N'@_outer_Id int',@_outer_Id=2

If I had 1000 rows, it would've generated 1001 SQL queries. How to fix that?

I'm using the latest version of EF Core (2.0.2) with SQL Server.

"How to fix that?" You can't. If you are lucky, it will be fixed in the next EF Core 2.1 version. Or switch back to EF6 if you can. — Ivan Stoev

Akli Akli · Accepted Answer · 2018-04-25T16:10:41

I found a workaround by using a join:

            var query = context.Parents
                                .Join(context.Parents
                                .Select(p => new
                                {
                                    Id = p.Id,
                                    ChildrenCount = p.Children.Count(),
                                }),
                                l => l.Id,
                                r => r.Id,
                                (l, r) => new { l, r.ChildrenCount })
                                .OrderBy(x => x.l.Name)
                                .Take(4);

            var results = query.ToList();

Which generates a single query :

exec sp_executesql N'SELECT TOP(@__p_0) [l].[Id], [l].[Name], [t].[Id], [t].[ChildrenCount]
FROM [Parents] AS [l]
INNER JOIN (
SELECT [p].[Id], (
    SELECT COUNT(*)
    FROM [Children] AS [c]
    WHERE [p].[Id] = [c].[ParentId]
) AS [ChildrenCount]
FROM [Parents] AS [p]
) AS [t] ON [l].[Id] = [t].[Id]
ORDER BY [l].[Name]',N'@__p_0 int',@__p_0=4

It's less efficient than in ef6 but at least it does it in one query.

Entity Framework Core does not select the count of child rows

1 Answers