Part 3: Learning LINQ from SQL using Visual Basic 2008 – Grouping

To execute the LINQ statements provided in this post, I suggest you to go through Part-1 (http://jagchat.spaces.live.com/blog/cns!41050F68F010A662!913.entry), where I demonstrated the same.

 

A simple example on GROUP..BY..operator of LINQ:

The following SQL SELECT

     SELECT Deptno, COUNT(*) FROM emp GROUP BY Deptno

can be coded in LINQ as following:     

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Count()

            Select Deptno, NoOfEmp = Count

Using different Aggregations on GROUP..BY.. operator of LINQ:

The following SQL SELECT

     SELECT Deptno, SUM(sal) FROM emp GROUP BY Deptno

can be coded in LINQ as following:     

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Select

Deptno,

                  SumSal = Group.Sum(Function(p) p.Sal)

You can include more aggregations as following:

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Select

Deptno,

SumSal = Group.Sum(Function(p) p.Sal),

MinSal = Group.Min(Function(p) p.Sal),

MaxSal = Group.Max(Function(p) p.Sal),

AvgSal = Group.Average(Function(p) p.Sal),

NoOfEmp = Group.Count(Function(p) p.Empno)

            Order By Deptno

 

Using LET with GROUP..BY.. operator in LINQ:

The following SQL SELECT (retrieves department wise highest earned employee)

     SELECT Deptno,

                (SELECT ename FROM emp

                      WHERE sal =  (SELECT MAX(sal) FROM emp

                                            WHERE Deptno=a.Deptno))

     FROM EMP a

can be coded in LINQ as following:     

Dim query = From p In GetEmployeeList()

Group p By p.Deptno

Into Group

Let MaxSal = Group.Max(Function(p) p.Sal)

Select

Deptno,

HighestEarnedEmp =

 (Group.Where(Function(p) p.Sal = MaxSal)).First.Ename

 

The above works if and only if there exists only one employee having the highest salary.   This is not practical in most scenarios