Entity Framework EF Core exception "Unable to translate the given 'GroupBy' pattern"

Question

I use EFCore 5.0.12

I have the following Entity:

public class Course {
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
    public string Description { get; set; }
    public CourseLevel Level { get; set; }
    [Column(TypeName = "decimal(7,2)")]
    public decimal FullPrice { get; set; }
}

Now I try to do a group-query like:

var query3 =
   from c in context.Courses
   group c by c.Level
   into g
   select g;
foreach (var group in query3) {
   Console.WriteLine($"{group.Key} ({group.Count()})");

   foreach (var c in group) {
       Console.WriteLine($"\t{c.Title}");
   }
}

This generates the Exception

Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side.

Why can this not be translated to sql?

Answer 1

A database simply can't produce a result that is produced by a plain LINQ GroupBy.
Both, database and LINQ use a different form of grouping.
Opposed to LINQ grouping, databases have stricter rules: grouping must always be used with an aggregation function like Count(), Sum(), Avg(), Min() or Max().

Example
Given is the following table

Users
-------------------------
| age | name  | lastname |
-------------------------
| 27  | Nancy | Doe
| 27  | Nancy | Apple
| 65  | Bob   | Uncle
| 27  | Bob   | Doe
| 35  | Bob   | Marley
| 27  | Jim   | Beam
--------------------------

A common LINQ query would be:

"Get all Users records grouped by age".

Users.GroupBy(user => user.Age);

Result is a set of tupels where the key is the age and the value a collection of User instances where User.Age is equaL (key, {...}):

(27, {User, User, User, User})
(65, {User})
(35, {User})

The above result shows the limitations of database grouping: it is not possible to make a database return such a result. A database can't return lists or arrays - it only returns rows and columns. Therefore, such a query can't be translated to SQL or executed by a database, hence EF throws an exception to notify the client about this inconvenience.
The solution to this problem would be to explicitly execute the grouping on the client-side, outside the database context, or to fix the expression to meet the aforementioned rules for valid database grouping.

A valid SQL query would be:

"Get the number of users that are of age '27' grouped by name."

SELECT Count(age), name FROM Users WHERE age=27 GROUP BY name

Result is a set of tuples, where the key is the count of users with the same age and the value the name of those users (age_count, name):

(2, Nancy)
(1, Bob)
(1, Jim)

If the LINQ query uses an aggregation function too, the query can be translated to SQL without any problems. To produce the above result of the SQL query we can write:

dbContext.Users
  .GroupBy(
    user => user.Name, 
    (name, users) => new { Count = users.Count(user => user.Age == 27), name });

Or

from user in dbContext.Users
group user by user.Name
into groups 
select new { Count = groups.Count(user => user.Age == 27), groups.Key };