In this articles, we are going to learn LINQ GroupBy operator in C#. The GroupBy operator is same as that of group by clause in SQL. GroupBy operator takes a flat sequence of items, organize that sequence into groups (IGrouping<TKey,TSource>) based on specific key and returns group of sequences. In short, GroupBy operator returns a group of elements from the given collection based on some key value. Each group is represented by (IGrouping<TKey,TSource>) object.
Related Articles
- Introduction to LINQ Standard Query Operators Vs SQL
- LINQ Ordering (Sorting) Operators: OrderBy and OrderByDescending in C#
- LINQ Ordering (Sorting) Operators: ThenBy, ThenByDescending and Reverse in C#
Using GroupBy Method
To understand this, I have created a Employee class with 4 public properties and one static method which will return a list of all employees as shown below
class Employee { public int Id; public string Name; public int DeptId; public string Gender; public static List<Employee> GetAllEmployees() { Employee emp1 = new Employee { Id = 1, Name = "Rahul", Gender = "Male", DeptId = 102 }; Employee emp2 = new Employee { Id = 2, Name = "Vijay", Gender = "Male", DeptId = 101 }; Employee emp3 = new Employee { Id = 3, Name = "Pooja", Gender = "Female", DeptId = 102 }; Employee emp4 = new Employee { Id = 4, Name = "Mithun", Gender = "Male", DeptId = 103 }; Employee emp5 = new Employee { Id = 5, Name = "Mary", Gender = "Female", DeptId = 101 }; Employee emp6 = new Employee { Id = 6, Name = "Mathew", Gender = "Male", DeptId = 101 }; Employee emp7 = new Employee { Id = 7, Name = "John", Gender = "Male", DeptId = 103 }; Employee emp8 = new Employee { Id = 8, Name = "Sachin", Gender = "Male", DeptId = 102 }; List<Employee> empList = new List<Employee>(); empList.Add(emp1); empList.Add(emp2); empList.Add(emp3); empList.Add(emp4); empList.Add(emp5); empList.Add(emp6); empList.Add(emp7); empList.Add(emp8); return empList; } }
The following example creates a groups of employees who have same department id. Employees of the same department id will be in the same collection and each grouped collection will have a key and inner collection, where the key will be the department id and the inner collection will include employees whose department id is matched with a key.
static void Main(string[] args) { var groupByResult = Employee.GetAllEmployees().GroupBy(x => x.DeptId); foreach (var group in groupByResult) { //Each group has a key Console.WriteLine("Dept Id Group: " + group.Key); //Each group has a inner collection foreach (var e in group) { Console.WriteLine("Id: " + e.Id + " Name: " + e.Name + " Gender: " + e.Gender + " DeptId: " + e.DeptId); } Console.WriteLine(); } Console.ReadKey(); }
Below is the output.
Dept Id Group: 102 Id: 1 Name: Rahul Gender: Male Deptld: 102 Id: 3 Name: Pooja Gender: Female Deptld: 102 Id: 8 Name: Sachin Gender: Male Deptld: 102 Dept Id Group: 101 Id: 2 Name: Vijay Gender: Male Deptld: 101 Id: 5 Name: Mary Gender: Female Deptld: 101 Id: 6 Name: Mathew Gender: Male Deptld: 101 Dept Id Group: 103 Id: 4 Name: Mithun Gender: Male Deptld: 103 Id: 7 Name: John Gender: Male Deptld: 103
Same thing can be achieved by using groupby SQL syntax.
static void Main(string[] args) { var groupByResult = from e in Employee.GetAllEmployees() group e by e.DeptId; foreach (var group in groupByResult) { //Each group has a key Console.WriteLine("DeptId Group: " + group.Key); //Each group has a inner collection foreach (var e in group) { Console.WriteLine("Id: " + e.Id + " Name: " + e.Name + " Gender: " + e.Gender + " DeptId: " + e.DeptId); } Console.WriteLine(); } Console.ReadKey(); }
Using GroupBy With Aggregate Function
We can use aggregate function like Count, Min, Max, Sum, Average with GroupBy as shown below.
static void Main(string[] args) { var groupByResult = Employee.GetAllEmployees().GroupBy(x => x.DeptId); foreach (var group in groupByResult) { Console.WriteLine("Dept Id: " + group.Key + " Employees Count: " + group.Count()); Console.WriteLine("Dept Id: " + group.Key + " Female Employees: " + group.Count(x => x.Gender == "Female")); Console.WriteLine("Dept Id: " + group.Key + " Male Employees: " + group.Count(x => x.Gender == "Male")); Console.WriteLine("Dept Id: " + group.Key + " Min of Dept Id: " + group.Min(x => x.Id)); Console.WriteLine("Dept Id: " + group.Key + " Max of Dept Id: " + group.Max(x => x.Id)); Console.WriteLine(); } Console.ReadKey(); }
Below is the output.
Dept Id : 102 Employees Count: 3 Dept Id : 102 Female Employees: 1 Dept Id : 102 Male Employees: 2 Dept Id : 102 Min of Dept Id: 1 Dept Id : 102 Max of Dept Id: 8 Dept Id : 101 Employees Count: 3 Dept Id : 101 Female Employees: 1 Dept Id : 101 Male Employees: 2 Dept Id : 101 Min of Dept Id: 2 Dept Id : 101 Max of Dept Id: 6 Dept Id : 103 Employees Count: 2 Dept Id : 103 Female Employees: 0 Dept Id : 103 Male Employees: 2 Dept Id : 103 Min of Dept Id: 4 Dept Id : 103 Min of Dept Id: 7
Using GroupBy With OrderBy To Sort Key Value
You can see in above example where Dept Id (which is key) is not sorted in any order. We can use OrderBy with GroupBy to sort Key value in ascending order. First, we need to project into new group then sort key using orderby keyword and to select create anonymous type.
static void Main(string[] args) { var groupByResult = from e in Employee.GetAllEmployees() group e by e.DeptId into newGroup // projecting into new group orderby newGroup.Key // ordering by key i.e. DeptId select new // creating anonymous type { Key = newGroup.Key, EMP = newGroup }; foreach (var group in groupByResult) { Console.WriteLine("Dept: " + group.Key + " Count: " + group.EMP.Count()); foreach (var e in group.EMP) { Console.WriteLine("Name: " + e.Name); } Console.WriteLine(); } Console.ReadKey(); }
Below is the output.
Dept: 101 Count: 3 Name: Vijay Name: Mary Name: Mathew Dept: 102 Count: 3 Name: Rahul Name: Pooja Name: Sachin Dept: 103 Count: 2 Name: Mithun Name: John
As you can see from above output Dept Id is sorted in ascending order but the Name field is not sorted. Now, we will sort the Name field as well.
static void Main(string[] args) { var groupByResult = from e in Employee.GetAllEmployees() group e by e.DeptId into newGroup // projecting into new group orderby newGroup.Key // ordering by key i.e. DeptId select new // creating anonymous type { Key = newGroup.Key, EMP = newGroup.OrderBy(x => x.Name) // sort Name using order by }; foreach (var group in groupByResult) { Console.WriteLine("Dept: " + group.Key + " Count: " + group.EMP.Count()); foreach (var e in group.EMP) { Console.WriteLine("Name: " + e.Name); } Console.WriteLine(); } Console.ReadKey(); }
Below is the output. You can see the Name field is sorted in ascending order.
Dept: 101 Count: 3 Name: Mathew Name: Mary Name: Vijay Dept: 102 Count: 3 Name: Pooja Name: Sachin Name: Rahul Dept: 103 Count: 2 Name: John Name: Mithun
Using GroupBy With Multiple Keys
In above example, we have used only a single key that is DeptId for grouping purpose. Now we will be using three keys (DeptId, Gender and Name) to group the collection. Here is the code. If you want to learn OrderBy and ThenBy, use above link in Related Articles.
var groupByResult = Employee.GetAllEmployees() .GroupBy(x => new { x.DeptId, x.Gender }) // group by using DeptId and Gender .OrderBy(g => g.Key.DeptId).ThenBy(g => g.Key.Gender) // first order by on DeptId and ThenBy on Gender .Select(g => new // creating anonymous type { Dept = g.Key.DeptId, Gender = g.Key.Gender, EMP = g.OrderBy(x => x.Name) // again sorting Name is ascending order }); foreach (var group in groupByResult) { Console.WriteLine("Dept Id: " + group.Dept + " Gender: " + group.Gender + " Count: " + group.EMP.Count()); Console.WriteLine("-----------------------------------"); foreach (var g in group.EMP) { Console.WriteLine(g.Name + "\t" + g.Gender + "\t" + g.DeptId); } Console.WriteLine(); Console.WriteLine(); } Console.ReadKey();
Below is the output. As you can see first DeptId is sorted then Gender is sorted then Name is sorted in ascending order.
Dept Id: 101 Gender: Male Count: 2 ----------------------------------- Mathew Male 101 Vijay Male 101 Dept Id: 102 Gender: Female Count: 1 ----------------------------------- Pooja Female 102 Dept Id: 102 Gender: Male Count: 2 ----------------------------------- Rahul Male 102 Sachin Male 102 Dept Id: 103 Gender: Male Count: 2 ----------------------------------- John Male 103 Mithun Male 103
Same thing can be achieved using sql like syntax.
var groupByResult = from emp in Employee.GetAllEmployees() group emp by new { emp.DeptId, emp.Gender } into newGroup orderby newGroup.Key.DeptId, newGroup.Key.Gender select new { Dept = newGroup.Key.DeptId, Gender = newGroup.Key.Gender, EMP = newGroup.OrderBy(x => x.Name) }; foreach (var group in groupByResult) { Console.WriteLine("Dept Id: " + group.Dept + " Gender: " + group.Gender + " Count: " + group.EMP.Count()); Console.WriteLine("----------------------"); foreach (var g in group.EMP) { Console.WriteLine(g.Name + "\t" + g.Gender + "\t" + g.DeptId); } Console.WriteLine(); Console.WriteLine(); } Console.ReadKey();
Grouping Based on First Character of string
Example 1
Here, we will group Name field based on first character of the Name.
static void Main(string[] args) { var groupByResult = from e in Employee.GetAllEmployees() group e by e.Name[0] into newGroup // here First Letter of the Name is Key select new { Name = newGroup.Key, EMP = newGroup }; foreach (var group in groupByResult) { Console.WriteLine("Name that start with the character '{0}':", group.Name); foreach (var w in group.EMP) { Console.WriteLine(w.Name); } Console.WriteLine(); } Console.ReadKey(); }
Output is shown below.
Name that start with the character 'R' Rahul Name that start with the character 'V' Vijay Name that start with the character 'P' Pooja Name that start with the character 'M' Mithun Mary Mathew Name that start with the character 'J' John Name that start with the character 'S' Sachin
Example 2
static void Main(string[] args) { string[] words = { "blueberry", "chimpanzee", "abacus", "banana", "apple", "cheese", "elephant", "umbrella", "anteater" }; var groupByResult = from w in words group w by w[0] into newGroup where (newGroup.Key == 'a' || newGroup.Key == 'e' || newGroup.Key == 'i' || newGroup.Key == 'o' || newGroup.Key == 'u') select newGroup; foreach (var group in groupByResult) { Console.WriteLine("Groups that start with a vowel: " + group.Key); foreach (var w in group) { Console.WriteLine(w); } Console.WriteLine(); } Console.ReadKey(); }
Output is shown below.
Groups that start with a vowel: a abacus apple anteater Groups that start with a vowel: e elephant Groups that start with a vowel: u umbrella
good explanation
ReplyDelete