How To Use Grouping Data Operations With Database Using LINQ

In this article, I will demonstrate how we can use GroupBy and ToLookup operator of Language-Integrated Query (LINQ). GroupBy and ToLookup operators are part of grouping data in Language-Integrated Query (LINQ). Grouping refers to the operation of putting data into groups so that the elements in each group share a common attribute. GroupBy & ToLookup return a collection that has a key and an inner collection based on a key field value. The execution of GroupBy is deferred whereas that of ToLookup is immediate. A LINQ query syntax can be end with the GroupBy or Select clause.

Introduction

In this article, I will demonstrate how we can use GroupBy and ToLookup operators of Language-Integrated Query (LINQ). GroupBy and ToLookup operators are the part of grouping data in LINQ. Grouping refers to the operations of putting data into groups so that the elements in each group share a common attribute. GroupBy & ToLookup return a collection that has a key and an inner collection based on a key field value. The execution of GroupBy is deferred whereas that of ToLookup is immediate. A LINQ query syntax can end with the GroupBy or Select clause. A LINQ query syntax can end with the GroupBy or Select clause.

GroupBy

Groups elements that share a common attribute. Each group is represented by an IGrouping<TKey,TElement> object.

ToLookup

Inserts elements into a Lookup<TKey,TElement> (a one-to-many dictionary) based on a key selector function.

Step 1

Open SQL Server 2014, create a table Employee, and insert some records.

  1. CREATE TABLE [dbo].[Employee](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Position] [nvarchar](50) NULL,  
  5.     [Office] [nvarchar](50) NULL,  
  6.     [Salary] [money] NULL,  
  7.     [Department] [nvarchar](50) NULL,  
  8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [ID] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  

Step 2

Open Visual Studio 2015 and create a new console application with a meaningful name.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item.

Screenshot for adding Entity Framework 1

LINQ 

After clicking on the new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

Screenshot for adding Entity Framework 2

LINQ 

After you click on "Add a window", the wizard will open. Choose EF Designer from database and click next.

Screenshot for adding entity framework 3

LINQ 

After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding entity framework 4

LINQ 

Connection will be added. If you wish save connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding entity framework 5

LINQ 

After clicking on NEXT another window will appear choose database table name as shown in the below screenshot then click on Finish.

Screenshot for adding entity framework 6

LINQ 

Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding entity framework 7

LINQ 

Following class will be added,
  1. namespace GroupingData_Demo  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public partial class Employee  
  7.     {  
  8.         public int ID { get; set; }  
  9.         public string Name { get; set; }  
  10.         public string Position { get; set; }  
  11.         public string Office { get; set; }  
  12.         public Nullable<decimal> Salary { get; set; }  
  13.         public string Department { get; set; }  
  14.     }  
  15. }  

Step 4

Write a program to call database class

Example 1 - GroupBy

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace GroupingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  14.   
  15.                 var result = employeeList.GroupBy(e => e.Department);  
  16.   
  17.                 foreach(var group in result)  
  18.                 {  
  19.                     Console.WriteLine("Department Name:{0}\nNumber of Employee:{1}",group.Key,group.Count());  
  20.                     Console.WriteLine();  
  21.                 }  
  22.   
  23.                 Console.Read();  
  24.             }  
  25.         }  
  26.     }  
  27. }  
Output

LINQ 
 
Example 2 - GroupBy 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace GroupingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  14.   
  15.                 var result = employeeList.GroupBy(e => e.Department);  
  16.   
  17.                 foreach(var group in result)  
  18.                 {  
  19.                     Console.WriteLine();  
  20.                     Console.WriteLine("Department Name-->" + group.Key);  
  21.                     Console.WriteLine();  
  22.   
  23.                     foreach (Employee emp in group)  
  24.                     {  
  25.                         Console.WriteLine("Employee Name:\t" + emp.Name);  
  26.   
  27.                     }  
  28.                 }  
  29.   
  30.                 Console.Read();  
  31.             }  
  32.         }  
  33.     }  
  34. }  
Output

LINQ 
 
Example 3 - GroupBy
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace GroupingData_Demo  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.             using (DBModel db=new DBModel())  
  12.             {  
  13.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  14.   
  15.                 var result = employeeList.GroupBy(e => e.Department);  
  16.   
  17.                 foreach(var group in result)  
  18.                 {  
  19.                     Console.WriteLine("Department Name:{0}\nTotal Salary of Deaprtment:{1}",group.Key,group.Sum(x=>x.Salary));  
  20.                     Console.WriteLine();  
  21.                 }  
  22.   
  23.                 Console.Read();  
  24.             }  
  25.         }  
  26.     }  
  27. }  
Output

LINQ 

Example 1 - ToLookup
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace GroupingData_Demo  
  6. {  
  7.     class ToLookup  
  8.     {  
  9.         static void Main()  
  10.         {  
  11.             using (DBModel db = new DBModel())  
  12.             {  
  13.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  14.   
  15.                 var result = employeeList.ToLookup(e => e.Department);  
  16.   
  17.                 foreach (var group in result)  
  18.                 {  
  19.                     Console.WriteLine("Department Name-->" + group.Key);  
  20.                     Console.WriteLine();  
  21.                 }  
  22.             }  
  23.         }  
  24.     }  
  25. }  
Output

LINQ 
 
Example 2 - ToLookup
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace GroupingData_Demo  
  6. {  
  7.     class ToLookup  
  8.     {  
  9.         static void Main()  
  10.         {  
  11.             using (DBModel db = new DBModel())  
  12.             {  
  13.                 IEnumerable<Employee> employeeList = db.Employees.ToList();  
  14.   
  15.                 var result = employeeList.ToLookup(e => e.Department);  
  16.   
  17.                 foreach (var group in result)  
  18.                 {  
  19.                     Console.WriteLine();  
  20.                     Console.WriteLine("Department Name-->" + group.Key);  
  21.                     Console.WriteLine();  
  22.   
  23.                     foreach (Employee emp in group)  
  24.                     {  
  25.                         Console.WriteLine("Employee Name:\t" + emp.Name);  
  26.   
  27.                     }  
  28.                 }  
  29.   
  30.                 Console.ReadLine();  
  31.             }  
  32.         }  
  33.     }  
  34. }  
Output

LINQ