Selecting multiple columns in group by and aggregate functions in LINQ

Problem


In general, the LINQ query group by gives ability to return only the column name that was grouped and the aggregate function value.

var catCount = from ch in db.ChartModels
                            join ca in db.CategoryModels
                            on ch.CategoryId equals ca.CategoryId
                            group ch by ca.CategoryName into groupChart
                            where groupChart.Count() > 0
                            select new
                            {
                                CategoryName = groupChart.Key,
                                CategoryCount = groupChart.Count(),
                            };

In the above code snippet, we can only retrieve CategoryName and CategoryCount but what if we want to also get CategoryId column value?

The solution

var catCount = (from ch in db.ChartModels
                            join ca in db.CategoryModels
                            on ch.CategoryId equals ca.CategoryId
                            group ch by new { ca.CategoryName, ca.CategoryId }  into groupChart
                            where groupChart.Count() > 0
                            select new
                            {
                                CategoryName = groupChart.Key.CategoryName,
                                CategoryCount = groupChart.Count(),
                                CategoryId = groupChart.Key.CategoryId
                            })

The solution of the problem is to use dynamic or anonymous type object with categories we want to group with in Group by and then return the columns with the help of .Key like above code snippet.

Hope this will be helpful.

Thanks