title: GroupBy in EF Core 3.1 / EF Core 5.0 not working, even for the simplest example
body: <p>I'm updating an EF6.x project to EF Core 3.1. Decided to go back to basics and follow the example of how to set up relationships from scratch again.</p>
  <p>According to the official Microsoft documentation, <a href="https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key" rel="noreferrer">EF Core Relationship Examples</a>, I translated the examples into a console app below:</p>
  <pre class="lang-cs prettyprint-override"><code>using Microsoft.EntityFrameworkCore;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  
  namespace BlogPostsExample
  {
      class Program
      {
          async static Task Main(string[] args)
          {
              // SQL Running in a Docker container - update as required
              var conString = &quot;data source=localhost,14330;initial catalog=BlogsDb;persist security info=True;user id=sa;password=&lt;Your super secure SA password&gt;;MultipleActiveResultSets=True;App=EntityFramework;&quot;;
  
              var ctx = new MyContext(conString);
  
              await ctx.Database.EnsureCreatedAsync();
  
              var result = await ctx.Posts.GroupBy(p =&gt; p.Blog).ToArrayAsync();
  
          }
      }
  
      class MyContext : DbContext
      {
          private readonly string _connectionString;
  
          public MyContext(string connectionString)
          {
              _connectionString = connectionString;
          }
          protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
          {
              base.OnConfiguring(optionsBuilder);
              if (!optionsBuilder.IsConfigured)
              {
                  optionsBuilder
                  .UseSqlServer(_connectionString);
              }
          }
          public DbSet&lt;Blog&gt; Blogs { get; set; }
          public DbSet&lt;Post&gt; Posts { get; set; }
  
          protected override void OnModelCreating(ModelBuilder modelBuilder)
          {
  
              modelBuilder.Entity&lt;Post&gt;()
              .HasOne(p =&gt; p.Blog)
              .WithMany(b =&gt; b.Posts)
              .HasForeignKey(p =&gt; p.BlogId) //Tried with and without these keys defined.
              .HasPrincipalKey(b =&gt; b.BlogId);
          }
  
      }
      public class Blog
      {
          public int BlogId { get; set; }
          public string Url { get; set; }
  
          public List&lt;Post&gt; Posts { get; set; }
      }
  
      public class Post
      {
          public int PostId { get; set; }
          public string Title { get; set; }
          public string Content { get; set; }
  
          public int BlogId { get; set; }
          public Blog Blog { get; set; }
      }
  }
  
  </code></pre>
  <p>There is no data in the DB. EF Core fails to convert</p>
  <pre class="lang-cs prettyprint-override"><code>ctx.Posts.GroupBy(p =&gt; p.Blog)  
  </code></pre>
  <p>to a store query. This appears to me to be the simplest example of a GroupBy you could try.</p>
  <p>When you run this code you get the following exception:</p>
  <pre class="lang-cs prettyprint-override"><code>System.InvalidOperationException: 'The LINQ expression 'DbSet&lt;Post&gt;
      .Join(
          outer: DbSet&lt;Blog&gt;, 
          inner: p =&gt; EF.Property&lt;Nullable&lt;int&gt;&gt;(p, &quot;BlogId&quot;), 
          outerKeySelector: b =&gt; EF.Property&lt;Nullable&lt;int&gt;&gt;(b, &quot;BlogId&quot;), 
          innerKeySelector: (o, i) =&gt; new TransparentIdentifier&lt;Post, Blog&gt;(
              Outer = o, 
              Inner = i
          ))
      .GroupBy(
          source: p =&gt; p.Inner, 
          keySelector: p =&gt; p.Outer)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
  </code></pre>
  <p>The only way to get this to work is to add something like AsEnumerable() before the <code>GroupBy</code>.</p>
  <p>This is clearly not great from a performance point of view, it turns the group by operation into a client side operation where you really want to be doing grouping on the server side.</p>
  <p>Have I missed something glaringly obvious? I struggle to believe that EF Core can't do the simplest group by that EF Framework has been doing since day 1. This seems like a fundamental requirement of any data driven app? (Or any app with a modicum of data!)</p>
  <p><strong>Update:</strong>
  <a href="https://i.stack.imgur.com/QH6rN.png" rel="noreferrer"><img src="https://i.stack.imgur.com/QH6rN.png" alt="enter image description here" /></a></p>
  <p>Adding a property, such as the Primary Key for the Blog in question makes no difference.</p>
  <p>Update 2:</p>
  <p>If you follow <a href="https://blog.jetbrains.com/dotnet/2020/11/25/getting-started-with-entity-framework-core-5/" rel="noreferrer">this JetBrains article</a>, you can do this:</p>
  <pre><code>var ctx = new EntertainmentDbContext(conString);
  await ctx.Database.EnsureCreatedAsync();
  
  var dataTask = ctx
                  .Ratings
                  .GroupBy(x =&gt; x.Source)
                  .Select(x =&gt; new {Source = x.Key, Count = x.Count()})
                  .OrderByDescending(x =&gt; x.Count)
                  .ToListAsync();
  
  var data = await dataTask;
  </code></pre>
  <p>But <strong>NOT</strong> this:</p>
  <pre><code>var ctx = new EntertainmentDbContext(conString);
  await ctx.Database.EnsureCreatedAsync();
  
  var dataTask = ctx
                  .Ratings
                  .GroupBy(x =&gt; x.Source)
                  // .Select(x =&gt; new {Source = x.Key, Count = x.Count()})
                  // .OrderByDescending(x =&gt; x.Count)
                  .ToListAsync();
  
  var data = await dataTask;
  </code></pre>
  <p>It only works with with an aggregating function, eg Count as above.</p>
  <p>Something similar in SQL works</p>
  <pre><code>SELECT COUNT(R.Id), R.Source
  FROM 
      [EntertainmentDb].[dbo].[Ratings] R
  GROUP BY R.Source
  </code></pre>
  <p>But, removing the aggregating function, COUNT does not, you receive messages similar to:</p>
  <p><code>Column 'EntertainmentDb.dbo.Ratings.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</code></p>
  <p>So it looks like I am trying to ask EF Core a question that I cannot ask in TSQL</p>