GroupBy in EF Core 3.1 / EF Core 5.0 not working, even for the simplest example

Question

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.

According to the official Microsoft documentation, EF Core Relationship Examples, I translated the examples into a console app below:

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 = "data source=localhost,14330;initial catalog=BlogsDb;persist security info=True;user id=sa;password=<Your super secure SA password>;MultipleActiveResultSets=True;App=EntityFramework;";

            var ctx = new MyContext(conString);

            await ctx.Database.EnsureCreatedAsync();

            var result = await ctx.Posts.GroupBy(p => 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<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .HasForeignKey(p => p.BlogId) //Tried with and without these keys defined.
            .HasPrincipalKey(b => b.BlogId);
        }

    }
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> 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; }
    }
}

There is no data in the DB. EF Core fails to convert

ctx.Posts.GroupBy(p => p.Blog)  

to a store query. This appears to me to be the simplest example of a GroupBy you could try.

When you run this code you get the following exception:

System.InvalidOperationException: 'The LINQ expression 'DbSet<Post>
    .Join(
        outer: DbSet<Blog>, 
        inner: p => EF.Property<Nullable<int>>(p, "BlogId"), 
        outerKeySelector: b => EF.Property<Nullable<int>>(b, "BlogId"), 
        innerKeySelector: (o, i) => new TransparentIdentifier<Post, Blog>(
            Outer = o, 
            Inner = i
        ))
    .GroupBy(
        source: p => p.Inner, 
        keySelector: p => 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.'

The only way to get this to work is to add something like AsEnumerable() before the GroupBy.

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.

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!)

Update:

Adding a property, such as the Primary Key for the Blog in question makes no difference.

Update 2:

If you follow this JetBrains article, you can do this:

var ctx = new EntertainmentDbContext(conString);
await ctx.Database.EnsureCreatedAsync();

var dataTask = ctx
                .Ratings
                .GroupBy(x => x.Source)
                .Select(x => new {Source = x.Key, Count = x.Count()})
                .OrderByDescending(x => x.Count)
                .ToListAsync();

var data = await dataTask;

But NOT this:

var ctx = new EntertainmentDbContext(conString);
await ctx.Database.EnsureCreatedAsync();

var dataTask = ctx
                .Ratings
                .GroupBy(x => x.Source)
                // .Select(x => new {Source = x.Key, Count = x.Count()})
                // .OrderByDescending(x => x.Count)
                .ToListAsync();

var data = await dataTask;

It only works with with an aggregating function, eg Count as above.

Something similar in SQL works

SELECT COUNT(R.Id), R.Source
FROM 
    [EntertainmentDb].[dbo].[Ratings] R
GROUP BY R.Source

But, removing the aggregating function, COUNT does not, you receive messages similar to:

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.

So it looks like I am trying to ask EF Core a question that I cannot ask in TSQL

Answer 1

I think this may be an inappropriate use of GroupBy since you're not actually grouping by something new or aggregating data, instead use the existing relationship and select the Blogs and include posts.

Note: Untested Code

var blogs = ctx.Blogs.Include(x => x.Posts);
// Optional filters.
var blogsWithPosts = ctx.Blogs
    .Include(x => x.Posts)
    .Where(x => x.Posts.Any())  // Only blogs with posts
    .Where(x => x.Posts.Any(y => y.Title == "A Specific Title")) // Only blogs with posts that have the title "A Specific Title"

If you need to include only a subset of a blogs posts, you can do that too.

var blogsAndMathingPosts = ctx.Blogs
    .Where(x => x.Posts.Any(y => y.Title == "A Specific Title")) // Only blogs that have at least one post with "A Specific Title"
    .Select(x => new Blog() {
        BlogId = x.BlogId,
        Url = x.Url,
        Posts = ctx.Posts.Where(y => y.BlogId == x.BlogId && y.Title == "A Specific Title").ToList()
    );

Answer 2

Just do exactly what exception message says! You'll need to change 'var' to explicit as well.

I had this and had same exception message as you got:

var GroupByM2S =
            dbContext.CatL1s
           .GroupBy(x => x.UserId);   

I changed to this. Tested and works fine.

IEnumerable<IGrouping<int, CatL1>> MsGrpByAsEnumerExplicit =              
            (dbContext.CatL1s).AsEnumerable()
           .GroupBy(x => x.UserId);

So basically change 'var' as I have. *The int here, IGrouping<int,...> is data type of your Grouping Key Prop/Col Then surround dbContext.EntityName with pars and then .AsEnumerable().GroupBy(...)

IEnumerable<IGrouping<dataTypeOfGpByKey, EntityName>> GrpByIEnumAsEnumExplicit =
        ( //<--Open Par dbCtx.EntityName
    .Join(
            outer: DbSet<Blog>,
            inner: p => EF.Property<Nullable<int>>(p, "BlogId"),
            outerKeySelector: b => EF.Property<Nullable<int>>(b, "BlogId"),
            innerKeySelector: (o, i) => new TransparentIdentifier<Post, Blog>(
                Outer = o,
                Inner = i
            )).AsEnumerable() //<-- Put here
                    .GroupBy(
                    source: p => p.Inner,
                    keySelector: p => p.Outer)
                    ...

Give that a shot for anyone with same exception message.