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

Question

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

There may be a difference in the conversion to SQLite and SQL Server. What column should be unique ? since you use distinct. You can try `.GroupBy(cr => cr.Property).CountAsync()` — Preben Huybrechts, Aug 04 '20 at 05:05
Exactly, I did using .GroupBy(cr=>cr.CallId). this is working fine on Sqlite but not sql server — Sras, Aug 04 '20 at 06:35
So I removed all migration file of sqlite then I add package provider for sql server and create new migration.of course I changed connection string.. Then I run project as normal ... But when the querying having groupby ... It is occurring this error... I have no idea why — Sras, Aug 04 '20 at 16:27
Internal Server Error - refreshing the page will make the exception disappear Error. this is front end remark done by me. please see the exception.... this is the iissue " 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." — Sras, Aug 05 '20 at 01:25
I tested mysql database. this helped me https://stackoverflow.com/questions/23921117/disable-only-full-group-by... but how can I solve with sql server or better way to solve from ef core or code — Sras, Aug 05 '20 at 01:37
have you ever expereienced this or my ef core statement is not proper? — Sras, Aug 05 '20 at 09:03

Gert Arnold · Answer 1 · 2020-08-12T09:21:52.267

3

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();

edited Aug 12 '20 at 09:21

answered Aug 05 '20 at 20:01

Gert Arnold

105,341
31
202
291

This works now!. Can you give me more explaination? am my EF statement wrong? – Sras Aug 06 '20 at 03:46
No, it's not wrong. It would work in EF6 (not core), it's just that EF core is limited. I'm surprised it works in SQLite. – Gert Arnold Aug 06 '20 at 09:44
Yes this works with SQLite but not sql sever. Such a big surprises – Sras Aug 06 '20 at 12:36
OK, Sqllite has [different rules for GroupBy syntax](https://www.sqlitetutorial.net/sqlite-group-by/), see "SQLite GROUP BY and INNER JOIN clause". – Gert Arnold Aug 06 '20 at 12:45
But is group by EF core really bad for getting data. Something supposed to work but not work. Really frustrating – Sras Aug 06 '20 at 14:02
Yeah. It's what it is. We can only accept it and try to find work-arounds until they fix things. – Gert Arnold Aug 06 '20 at 15:25
Well, it's even simpler.See the addition. – Gert Arnold Aug 12 '20 at 09:23

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

1 Answers1