Linq,Lambda and SQL Group by

SQL & Linq Lambda Group

Group By allows us to group rows together and additionally perform aggregate calculation which works on a group of rows. Let me first give you a simple example of what group by does using one field. I will use "SampleTable" table and group all customers by Class in other words I want to show all unique values that exist in Country Field, that is how group by works. See below example code with results (before and after)

Sql Table example

DataTable example

Group by example with Aggregate Function

You can group by fields in tables but this is rarely used on its own and very often that is combined with aggregate function. Aggregate function gets access to grouped rows. So if you group 3 rows into 1 then you can perform aggregate calculation like count which in this case would give result 3. You can also use other aggregate functions like SUM, MAX, MIN, AVG and several other ones. See below example that is the same as previous ones but now with Count aggregate function.

select class,COUNT(Class) from SampleTable Group by Class

Linq Output:

 var query = from p in dt.AsEnumerable()

                        group p by p.Field<string>("Class") into g

                        select new { ClassType = g.Key.ToString(), CCount = g.Count() };

Lambda Output:

 var query = dt.AsEnumerable().GroupBy(n => n.Field<string>("Class")).
                Select(y => new
                {
                    ClassType = y.Key.ToString(),
                    CCount = y.Count()
                });