GroupBy executes on the client side

The current version of EF Core disregards GroupBy.

Problem

We normally use the LINQ GroupBy method to group records by some common characteristic, probably with the goal of doing aggregations. In most LINQ implementations, this generates a SQL GROUPBY, which is what we want, but, in Entity Framework Core 1.0, this translation is silently ignored, and grouping is instead done on the client-side, after retrieving all records. The problem is, this may bring a lot of records, causing severe performance and memory problems.

Imagine, for example, that you have thousands of products distributed in four colors: blue, red, green, and yellow. You might want to run this query:

var productsGrouped = (from p in context.Products 
groupby p.Color into g 
select new { Color = g.Key, Count = g.Count() })
    .ToDictionary(x => x.Color, x => x.Count);

What will happen is, Entity Framework will bring all the thousands of products into the client application and perform the grouping by color and count the products in memory.

How to fix it…

Unfortunately, as of now, we need to use plain SQL for this, which can be a plain SELECT or a stored procedure call. There is no alternative, I'm afraid; we need to do something like the following:

//get the ADO.NET connection
var con = context.Database.GetDbConnection();
var productsGrouped = new Dictionary<string, int>();
//create an ADO.NET command attached to the connection
using (var cmd = con.CreateCommand())
{
    cmd.CommandText = "SELECT p.Color, COUNT(1)FROM Products p GROUP BY p.Color";
    con.Open();
    using (var reader = cmd.ExecuteReader())
    {
        //while there are records to read
        while (reader.Read())
        {
            //read the first column as a string
            var color = reader.GetString(0);
            //read the second column as an integer
            var count = reader.GetInt32(1);
            productsGrouped[color] = count;
        }
    }
}

The default implementation, if not understood and dealt with in time, can result in severe performance (lots of records traveling from the database to the client application) and memory (lots of instances being created and hydrated) problems, so be warned!