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

user_2042829 0
Viewed 404

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?

entity-framework-core c#
2 Answers

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 };
user_31417920

You cannot directly loop over an IGrouping (if you are building an IQueryable of course), the documentation explains that:

Furthermore, IGrouping implements IEnumerable<TElement>, which means you can compose over it using any LINQ operator after the grouping. Since no database structure can represent an IGrouping, GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases.

user_55873930
Related