Why is Group By translating in Order By by Entity Framework?

Question

I am using Entity Framework in .NET 7.

I have 3 entities:

1. Course that contains a ProfessorId among other things
2. Grade that has a CourseId among other things
3. Professor

I want to get all the courses that are assigned to a professor and have at least 1 grade associated with them and filter them in a Dictionary<string, CourseViewModel> where string is the semester.

I have written the following LINQ query:

var professorGradedCourses = _dbContext.Courses
            .Where(course => course.ProfessorId == professorId && course.Grades.Any())
            .Select(course => new CourseViewModel
            {
                Title = course.Title,
                Semester = course.Semester,
            })
            .GroupBy(course => course.Semester)
            .OrderBy(course => course.Key)
            .ToDictionary(group => group.Key, group => group.ToList());

When that executes I get an exception saying it can't be translated.

If I remove the OrderBy and keep only the GroupBy, it works and the translated SQL in Microsoft SQL Server is:

  SELECT [c].[Semester], [c].[Title]
  FROM [Courses] AS [c]
  WHERE [c].[ProfessorId] = @__professorId_0 
    AND EXISTS (SELECT 1
                FROM [Grades] AS [g]
                WHERE [c].[Id] = [g].[CourseId])
  ORDER BY [c].[Semester]

As you can see it adds ORDER BY anyway, even though I have removed it and kept only GroupBy(). Can someone explain why is that? What if I wanted to order by descending would that be possible? Also the weird thing is that if I remove GroupBy() and keep only OrderBy() and replace the ToDictionary with ToList, it works and the exact same query is produced (only now I can't really use the results without further actions).

Answer 1

LINQ GroupBy :

Groups the elements of a sequence.

SQL GROUP BY :

A SELECT statement clause that divides the query result into groups of rows, usually by performing one or more aggregations on each group. The SELECT statement returns one row per group.

They aren't equivalent. The main difference is LINQ GroupBy return a collection by key, when SQL GROUP BY return ONE element (column) by key.

If the projection ask ONE element by key, then EF Core translate LINQ GroupBy to SQL GROUP BY :

// Get the number of course by semester
context
    .Courses
    .GroupBy(c => c.Semester)
    .Select(cs => new { Semester = cs.Key, Count = cs.Count() })
    .ToList();

Translated to :

SELECT [c].[Semester], COUNT(*) AS [Count]
FROM [Courses] AS [c]
GROUP BY [c].[Semester]

But if the projection ask several element, then EF Core translate LINQ GroupBy to SQL ORDER BY and group by itself.

context
    .Courses
    .Select(c => new { c.Id, c.Semester })
    .GroupBy(c => c.Semester)
    .ToDictionary(cs => cs.Key, cs => cs.ToList());

Translated to :

SELECT [c].[Semester], [c].[Id]
FROM [Courses] AS [c]
ORDER BY [c].[Semester]

If the result is :

Semester	Id
2023 S1	1
2023 S1	4
2023 S2	2
...	...

Then EF Core read like :

1. Read first row : Semester is "2023 S1"
   • No group
   • Then create a group and add the row in.
2. Read second row : Semester is "2023 S1"
   • The key is the same that precedent element
   • Then Add the row in the group
3. Read the third row : Semester is "2023 S2"
   • The key is different that precedent element
   • Then create a new group and the row in.
4. And so on...

You understand the interest of sorting.

---

About the error, I don't know that EF Core can't. The query sound legit. Maybe this should not be implemented at this time.

---

About that you try, to convert a sorted grouping enumeration to a dictionary. This is weird because the dictionary isn't sortable. Then this sorts elements and put them in loose.

If Dictionary seem sorted, it's a coincidence, not a feature. In intern, the dictionary sort element by key's has code, that is generally the sorted order... But not every time.

If you want a sorted dictionary, you can use SortedDictyonary. But it can be tricky if you need a custom sort rule, like :

context
    .Courses
    .Select(c => new { c.Id, c.Semester })
    .GroupBy(c => c.Semester)
    .ToImmutableSortedDictionary(cs => cs.Key, cs => cs.ToList(), new ReverseComparer<string>());



public class ReverseComparer<T> : IComparer<T>
{
    private IComparer<T> _comparer = Comparer<T>.Default;

    public int Compare(T? x, T? y)
    {
        return _comparer.Compare(x, y) * -1;
    }
}

Answer 2

The exception you are encountering is most likely due to the fact that the OrderBy clause cannot be translated into SQL by Entity Framework. The OrderBy clause is executed in memory after the data has been retrieved from the database, which is why it works when you remove it and keep only the GroupBy clause.

However, if you want to order the dictionary by descending, you can simply call the Reverse method on the ToDictionary result:

var professorGradedCourses = _dbContext.Courses
.Where(course => course.ProfessorId == professorId && course.Grades.Any())
.Select(course => new CourseViewModel
{
    Title = course.Title,
    Semester = course.Semester,
})
.GroupBy(course => course.Semester)
.OrderByDescending(course => course.Key)
.ToDictionary(group => group.Key,
group => group.ToList())
.Reverse();

This way, the dictionary will be sorted in descending order based on the semester.

Give this a try and let me know how it works for you.

EDIT:

Converting the IEnumerable back to a Dictionary should work like this:

var professorGradedCourses = _dbContext.Courses
.Where(course => course.ProfessorId == professorId && course.Grades.Any())
.Select(course => new CourseViewModel
{
    Title = course.Title,
    Semester = course.Semester,
})
.GroupBy(course => course.Semester)
.OrderByDescending(course => course.Key)
.ToDictionary(group => group.Key,
group => group.ToList())
.Reverse()
.ToDictionary(pair => pair.Key,
pair => pair.Value);