Query Translation: difference between x.Count()==0 and !x.Any()

[sa-es-ir]

I have a performance issue with EF and translated query
With this code

  var result1 = await DbContext.Set<Transaction>()
                .AsNoTracking()
                .GroupBy(x => x.CustomerId)
                .Select(x => new
                {
                    CustomerId = x.Key,
                    avg = x.Count() == 0 ? 0 : EF.Functions.DateDiffDay(x.Min(m => m.TransactionDate), x.Max(m => m.TransactionDate)) / x.Count()
                }).ToListAsync(cancellationToken);

And the translated query is like:

SELECT [t].[CustomerId], CASE
    WHEN COUNT(*) = 0 THEN 0
    ELSE DATEDIFF(day, MIN([t].[TransactionDate]), MAX([t].[TransactionDate])) / COUNT(*)
END AS [avg]
FROM [Transactions] AS [t]
GROUP BY [t].[CustomerId]

Execution Time:

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 325 ms.

But for this code

 var result2 = await DbContext.Set<Transaction>()
                .AsNoTracking()
                .GroupBy(x => x.CustomerId)
                .Select(x => new
                {
                    CustomerId = x.Key,
                    avg = !x.Any() ? 0 : EF.Functions.DateDiffDay(x.Min(m => m.TransactionDate), x.Max(m => m.TransactionDate)) / x.Count()
                }).ToListAsync(cancellationToken);

And the translated query:

SELECT [t].[CustomerId], CASE
    WHEN NOT (EXISTS (
        SELECT 1
        FROM [Transactions] AS [t0]
        WHERE [t].[CustomerId] = [t0].[CustomerId])) THEN 0
    ELSE DATEDIFF(day, MIN([t].[TransactionDate]), MAX([t].[TransactionDate])) / COUNT(*)
END AS [avg]
FROM [Transactions] AS [t]
GROUP BY [t].[CustomerId]

Execution Time:

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 401 ms.

I think both of them should be a same query.

Include provider and version information

EF Core version: 6.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

[fschlaef]

Same issue on 3.x branch, except Any() gives a query that takes over 1 minute to execute, while Count != 0 takes under 200ms in our case.