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
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 ListEmployee> 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 };
ListEmployee> empList = new ListEmployee>();
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
