Why there is no GroupBy clause in internal SQL of Entity Framework linq query?

Question

In documentation of Entity Framework: https://www.entityframeworktutorial.net/querying-entity-graph-in-entity-framework.aspx

in section regarding GroupBy we can read that following code:

using (var ctx = new SchoolDBEntities())
{    
    var students = from s in ctx.Students 
                group s by s.StandardId into studentsByStandard
                select studentsByStandard;

    foreach (var groupItem in students)
    {
        Console.WriteLine(groupItem.Key);

        foreach (var stud in groupItem)
        {
            Console.WriteLine(stud.StudentId);
        }
    }
}

executes internally following SQL:

SELECT 
[Project2].[C1] AS [C1], 
[Project2].[StandardId] AS [StandardId], 
[Project2].[C2] AS [C2], 
[Project2].[StudentID] AS [StudentID], 
[Project2].[StudentName] AS [StudentName], 
[Project2].[StandardId1] AS [StandardId1]
FROM ( SELECT 
    [Distinct1].[StandardId] AS [StandardId], 
    1 AS [C1], 
    [Extent2].[StudentID] AS [StudentID], 
    [Extent2].[StudentName] AS [StudentName], 
    [Extent2].[StandardId] AS [StandardId1], 
    CASE WHEN ([Extent2].[StudentID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT DISTINCT 
        [Extent1].[StandardId] AS [StandardId]
        FROM [dbo].[Student] AS [Extent1] ) AS [Distinct1]
    LEFT OUTER JOIN [dbo].[Student] AS [Extent2] ON ([Distinct1].[StandardId] = [Extent2].                                                [StandardId]) OR (([Distinct1].[StandardId] IS NULL) AND ([Extent2].[StandardId] IS NULL))
)  AS [Project2]
ORDER BY [Project2].[StandardId] ASC, [Project2].[C2] ASC
go

Why there is no GroupBy clause in SQL? If there is no GroupBy clause needed, can’t we just use simple Select with OrderBy and without Joins? Can anyone explain the above query?

user_861716 · Answer

The bottom line is: because SQL can't return nested result sets.

Every SQL SELECT statement returns a flat list of values. LINQ is capable of returning object graphs, i.e. objects with nested objects. That's exactly what LINQ's GroupBy does.

In SQL, a GROUP BY statement only returns the grouping columns and aggregate results:

SELECT StandardId, COUNT(*)
FROM Students
GROUP BY StandardId;

The rest of the student columns is gone.

A LINQ GroupBy statement returns something like

StandardId
            StudentId StudentName
1
            21        "Student1"
            15        "Student2"
2
            48        "Student3"
            91        "Student4"
            17        "Student5"

Therefore, a SQL GROUP BY statement can never be the source for a LINQ GroupBy.

Entity Framework (6) knows this and it generates a SQL statement that pulls all required data from the database, adds some parts that make grouping easier, and it creates the groupings client-side.

Why there is no GroupBy clause in internal SQL of Entity Framework linq query?

1 Answers