Sunday, 26 February 2017

LINQ Grouping Operator: GroupBy in C#

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

  1. Introduction to LINQ Standard Query Operators Vs SQL
  2. LINQ Ordering (Sorting) Operators: OrderBy and OrderByDescending in C#
  3. 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
Share:

1 comment:

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON