EF Core Group By is working on sqlite but not SQL Server

In case I have the following EF Core statement

var totalCallsOnHealthRelatedIssue = await _context.CallReasons
    .Where(cr => cr.Call.IsDeleted != true 
            && (cr.Call.CallStartDateTime >= filterStartDate 
                && cr.Call.CallStartDateTime <= filterEndDate) 
                && cr.Reason.ReasonTypeId == 2)
    .GroupBy(cr => cr.CallId)
    .CountAsync(); // Reason: Any type of health issue is counted ReasonType: Health Issue == 2

It is working okay on SQLite but when I change database server to SQL Sever 2019 it does not work.

Error: Column 'CallReasons.ReasonId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here is Detail Produced Error

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (152ms) [Parameters=[@__filterStartDate_0='2020-08-01T00:00:00', @__filterEndDate_1='2020-08-25T23:59:59'], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM (
          SELECT [c].[CallId], [c].[ReasonId]
          FROM [CallReasons] AS [c]
          INNER JOIN [Calls] AS [c0] ON [c].[CallId] = [c0].[Id]
          INNER JOIN [Reasons] AS [r] ON [c].[ReasonId] = [r].[Id]
          WHERE (([c0].[IsDeleted] <> CAST(1 AS bit)) AND (([c0].[CallStartDateTime] >= @__filterStartDate_0) AND ([c0].[CallStartDateTime] <= @__filterEndDate_1))) AND ([r].[ReasonTypeId] = 2)
          GROUP BY [c].[CallId]
      ) AS [t]
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'Infrastructure.Data.ApplicationDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Column 'CallReasons.ReasonId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location where exception was thrown ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
      ClientConnectionId:294c90c0-ce11-439c-811b-858be9623680
      Error Number:8120,State:1,Class:16
Microsoft.Data.SqlClient.SqlException (0x80131904): Column 'CallReasons.ReasonId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:294c90c0-ce11-439c-811b-858be9623680
Error Number:8120,State:1,Class:16
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action API.Controllers.ReportsController.GetMTHotlineReport (API) in 1260.4704ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'API.Controllers.ReportsController.GetMTHotlineReport (API)'
fail: System.Exception[0]
      Column 'CallReasons.ReasonId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 1774.7343ms 500 application/json

This is one of many issues with GroupBy in EF-core 3. A similar issue is posted on github.

The usual advice is do grouping client-side, i.e. after selecting the required data followed by AsEnumerable(). But in this case you only want to count the groups. It would be overkill to pull all data into the client and then do the count.

So you need a work-around. One that I can find is to help EF and explicitly select the grouping column:

var totalCallsOnHealthRelatedIssue = await _context.CallReasons
    .Where(cr => !cr.Call.IsDeleted 
              && (cr.Call.CallStartDateTime >= filterStartDate 
              && cr.Call.CallStartDateTime <= filterEndDate) 
              && cr.Reason.ReasonTypeId == 2)
    .Select(cr => cr.CallId)
    .GroupBy(id => id)
    .CountAsync();

Not pleasant, at all.

But this is a typical example of tunnel vision, focusing to much on working around GroupBy issues. In your case you can simply count distinct ids:

var totalCallsOnHealthRelatedIssue = await _context.CallReasons
    .Where(cr => !cr.Call.IsDeleted 
              && (cr.Call.CallStartDateTime >= filterStartDate 
              && cr.Call.CallStartDateTime <= filterEndDate) 
              && cr.Reason.ReasonTypeId == 2)
    .Select(cr => cr.CallId)
    .Distinct()
    .CountAsync();