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

Using debug (Microsoft.Extensions.Logging) I can confirm your .Net Core EF 2.0.1 group by linq is not translate to SQL. A warning is raised:

The LINQ expression 'GroupBy([p].City.ToLower(), [p])' could not be translated and will be evaluated locally.

dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Optimized query model: 
      'from IGrouping<string, Person> g in 
          (from Person p in DbSet<Person>
          select [p]).GroupBy([p].City.ToLower(), [p])
      select new <>f__AnonymousType0<string, int>(
          [g].Key, 

              (from Person <generated>_1 in [g]
              select [<generated>_1]).Count()
      )'

A warning is raised:

warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'GroupBy([p].City.ToLower(), [p])' could not
      be translated and will be evaluated locally.

Results are composed locally:

dbug: Microsoft.EntityFrameworkCore.Query[10107]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      (QueryContext queryContext) => IEnumerable<<>f__AnonymousType0<string, int>> _InterceptExceptions(
          source: IEnumerable<<>f__AnonymousType0<string, int>> _Select(
              source: IEnumerable<IGrouping<string, Person>> _GroupBy(
                  source: IEnumerable<Person> _ShapedQuery(
                      queryContext: queryContext, 
                      shaperCommandContext: SelectExpression: 
                          SELECT "p0"."ID", "p0"."City", "p0"."Name"
                          FROM "People" AS "p0"
                          ORDER BY lower("p0"."City"), 
                      shaper: UnbufferedEntityShaper<Person>), 
                  keySelector: (Person p) => string p.City.ToLower(), 
                  elementSelector: (Person p) => p), 
              selector: (IGrouping<string, Person> g) => new <>f__AnonymousType0<string, int>(
                  g.Key, 
                  int Count(g)
              )), 
          contextType: ConsoleApplication3.MyContext, 
          logger: DiagnosticsLogger<Query>, 
          queryContext: queryContext)

This is the code to reproduce it:

I post here the source code to help people who wants to know how to trace it:

Model and dbcontext

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;

namespace ConsoleApplication3
{
    public class Person
    {

        public int ID { get; set; }
        public string Name{ get; set; }
        public string City { get; set; }

    }

    public class MyContext : DbContext
    {

        public static readonly LoggerFactory MyLoggerFactory
            = new LoggerFactory(new[] {new ConsoleLoggerProvider((_, __) => true, true)});

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=beer.db"); //Available here
            optionsBuilder.UseLoggerFactory(MyLoggerFactory);
            base.OnConfiguring(optionsBuilder);
        }

        public DbSet<Person> People { get; set; }
    }

}

Inserting data and making the Query

        var ctx =new  MyContext();

        var p1=new Person();
        p1.City = "L'Escala";
        p1.Name = "Dani";
        ctx.People.Add(p1);

        var p2=new Person();
        p2.City = "L'Escala";
        p2.Name = "Dolors";
        ctx.People.Add(p2);

        var p3=new Person();
        p3.City = "Albons";
        p3.Name = "Joan";
        ctx.People.Add(p3);

        ctx.SaveChanges();

        var data1 = 
            from p in ctx.People
            group p by p.City.ToLower() into g
            select new { city = g.Key, citizen_count = g.Count() };

        data1.ToList();

        foreach (var v in data1)
        {
            Console.WriteLine($"{v.city} - {v.citizen_count}");
        }        

Results:

Results are ok:

albons - 1
l'escala - 2

Solution:

It looks group by will be supported on next 2.1, evaluate if you can live with this performance issue this time.

Answer 2

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.