EntityFramework core group by gives me an order by

Question

Here is my linq to sql query:

var data = 
    from p in dbcontext.person
    group p by p.city.ToLower() into g
    select new StatsViewModel { city = g.Key, citizen_count = g.Count() };

And here is the real sql query i get in sql server:

SELECT [p0].[id_person], [p0]....
      FROM [person] AS [p0]
      ORDER BY LOWER([p0].[city])

This is an order by, not a group by...

Answer 1

This is a known issue with the GroupBy translation of the current EF Core, tracked by the Relational: Support translating GroupBy() to SQL #2341 and committed to be fixed in the next EF Core 2.1 release (according to the EF Core Roadmap). So until then, there is nothing you can do.

But don't be fooled by the generated SQL. EF Core uses a combination of so called Cliend and Server Evaluation, which in this particular case means the GroupBy will be executed in memory after retrieving the result of the SQL query you see, so the actual result will be correct. The "only" problem could be the performance.