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!