Understand Group by With Linq C# | GroupBy With Sum and Count,Inner Join

Understand Group by With Linq C# | GroupBy With Sum and Count,Inner Join

LINQ is actually a shortened form of Language Integrate Query. LINQ defines features that can be used to retrieve data from any type of data source. This is why LINQ is most important because data itself is the basic foundation of any program and using LINQ, data can be easily retrieve from different types of Data Sources.

In this post, I will explain to you, group by clause in LINQ to SQL. As we know that grouping is a very powerful feature that converts a collection in groups where each group has a key associated with it if you are a beginner then don’t worry this post if for you.

I have created two table Employee and Department. You can see the below image.

Employee Table Sql Script:

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NULL,
    [Address] [nvarchar](500) NULL,
    [Salary] [decimal](18, 2) NULL,
    [DepId] [int] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]

Department Table Sql Script:

CREATE TABLE [dbo].[Department](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

 

How to Use Group by in Linq?

Let’s we want total salary distribution department wise
Sql Query:-

SELECT DepId,SUM(salary) AS TotalSalrytodepartment FROM Employee
GROUP BY DepId

Linq Query:-

DemoDataBaseEntities db = new DemoDataBaseEntities();
            var result = from s in db.Employees
                         group s by s.DepId into res
                         select new
                         {
                             NAME = res.Key,
                             NumberofEmployee = res.Sum(p => p.Salary)
                         };

 

Group by With Inner Join
Let’s i want the number of employees in each department with the department name.

Sql Query:-

select d.DepartmentName ,count(s.DepId) as NumberofEmployee from Employee  s
inner join Department d on s.DepId=d.Id
group by d.DepartmentName



Linq Query:-

DemoDataBaseEntities db = new DemoDataBaseEntities();
            var result = from s in db.Employees
                         join d in db.Departments on s.DepId equals d.Id
                         group d by d.DepartmentName into res
                         select new
                         {
                             NAME = res.Key,
                             NumberofEmployee = res.Count()
                         };

 

C# Linq Group By on Multiple Columns

Let’s take an example, your company has multiple across the world and we want to calculate the total salary expense city-wise w.r.t each department.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication
{

    public partial class Employees
    {
        public string Name { get; set; }
        public string City { get; set; }
        public decimal Salary { get; set; }
        public string Department { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<Employees> employess = new List<Employees>();
            employess.Add(new Employees { Name = "John", City = "London", Salary = 2000, Department = "Data Analyst" });
            employess.Add(new Employees { Name = "Mark", City = "Paris", Salary = 2000, Department = "Business Intelligence Manager" });
            employess.Add(new Employees { Name = "Josh", City = "Sydney", Salary = 2000, Department = "Data Engineering" });
            employess.Add(new Employees { Name = "Kath", City = "Paris", Salary = 2000, Department = "Data Analyst" });
            employess.Add(new Employees { Name = "Karlin", City = "Sydney", Salary = 2000, Department = "Business Intelligence Manager" });
            employess.Add(new Employees { Name = "Kovii", City = "London", Salary = 2000, Department = "Data Analyst" });
            employess.Add(new Employees { Name = "Jack", City = "Tokyo", Salary = 2000, Department = "Business Intelligence Manager" });
            employess.Add(new Employees { Name = "Smith", City = "Paris", Salary = 2000, Department = "Data Engineering" });
            var result = from c in employess
                         group c by new
                         {
                             c.City,
                             c.Department
                         } into gcs
                         select new
                         {
                             City = gcs.Key.City,
                             Department = gcs.Key.Department,
                             TotalSalaryCityWise = gcs.Sum(a => a.Salary)
                         };

        }
    }
}

 

Result:

LINQ with Groupby and Count:

Let’s we want to calculate the number of employees in each city.
 var result = from s in employess
                         group s by s.City into res
                         select new
                         {
                             City = res.Key,
                             NoofEmployee = res.Count()
                         };

Result:

Linq Sum with Group by :

Let’s we want to calculate the total expense of the company in each city.
 var result = from s in employess
                         group s by s.City into res
                         select new
                         {
                             City = res.Key,
                             SumSalary = res.Sum(p => p.Salary)
                         };

Result:

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Donate for Corona Victim

Corona Virus Relief Fund

Your Header Sidebar area is currently empty. Hurry up and add some widgets.