Incorrect SQL generated for Count over Group By (EFCore 2.1)

[mpetito]

EFCore 2.1 generates incorrect sql if you perform a Count operation on a queryable which uses the new GroupBy translation. Given a query of the form queryable.GroupBy(...).Count(), EF performs a count within the groups instead of a count of the groups.

Produced SQL which is incorrect:

SELECT COUNT(*)
FROM [Payments] AS [p]
GROUP BY [p].[AccountId]

Expected SQL similar to:

SELECT COUNT(*)
FROM
(SELECT 1 x
 FROM [Payments] AS [p]
 GROUP BY [p].[AccountId]) t

It may seem unusual to construct an EF query in this way. However, this issue surfaced when using GroupBy in a query for a paged list and I believe this to be a relatively common case. In my project the paging algorithm has no knowledge of the underlying query. The query is executed first with Count to get the total count of records, and again with Take/Skip to get the current page of records.

Steps to reproduce

I've created a small project to reproduce the issue: https://github.com/mpetito/efcore-2.1-groupby

Given the following seed data:

db.Payments.AddRange(
    new Payment {AccountId = 1, Value = 10},
    new Payment {AccountId = 2, Value = 11},
    new Payment {AccountId = 2, Value = 12},
    new Payment {AccountId = 3, Value = 13},
    new Payment {AccountId = 3, Value = 14},
    new Payment {AccountId = 3, Value = 15});

This query gives the incorrect count of groups:

var queryableCount = await db.Payments.GroupBy(p => p.AccountId, (accountId, group) => new {Account = accountId, Total = group.Sum(g => g.Value)}).CountAsync();
Console.WriteLine($"Count of queryable payments grouped by AccountId: {queryableCount}");

Count of queryable payments grouped by AccountId: 1

The correct count is 3 when grouping by AccountId. The count of 1 is arrived at because the first group itself has a count of 1, and EF must be taking the first result row for the count (discarding any additional result rows).

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 / SQL Server 2016

[smitpatel]

GroupBy should cause pushdown when applying some result operators. Need to update method PrepareSelectExpressionForAggregate

Expected SQL

SELECT COUNT(*)
FROM (
    SELECT [p].[AccountId] AS [Account], Sum([p].[Value] AS [Total]
    FROM [Payments] AS [p]
    GROUP BY [p].[AccountId]) AS [t]

[smitpatel]

@mpetito - As a work-around you can just use distinct instead of group by like this
var query = db.Payments.Select(p => p.AccountId).Distinct().Count()
It is not really good solution if you are reusing the queryable to do counting and paging/grouping but at least gives correct results.

[ajcvickers]

@maumar This issue is approved for patch and the release\2.1 branch is now open for merging. Please ensure:

• The appropriate changes get into both the release and dev branches
• Quirking is included for the release branch only

[HarutyunI]

This problem still exists in 2.2, but there is another workaround, maybe helpful for others
var query = db.Payments.GroupBy(p => p.AccountId).Select(p => p.Key).Count()

[marcobarbero]

@HarutyunI thanks for your workaround, it works correctly on 2.2.