The source code for all of the following LINQ queries can be downloaded here:
A simple sub-query representation using LINQ (with aggregation in sub-query):
The following SQL SELECT
SELECT ename FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp)
can be coded in LINQ as following:
Dim query = From p In GetEmployeeList() _
Where (p.Sal) = (From q In GetEmployeeList() _
Select q.Sal).Max() _
Select p.Ename
A simple sub-query representation using LINQ (without aggregation in sub-query):
The following SQL SELECT
SELECT dname FROM dept
WHERE deptno = (SELECT deptno FROM emp
WHERE empno = 1002)
can be coded in LINQ as following:
Dim d As String = (From q In GetEmployeeList() _
Where q.Empno = 1002 _
Select q.Deptno).First.ToString
Dim query = From p In GetDeptList() _
Where (p.Deptno) = d _
Select p.Dname
or directly like the following:
Dim query = From p In GetDeptList() _
Where (p.Deptno) = (From q In GetEmployeeList() _
Where q.Empno = 1002 _
Select q.Deptno).First.ToString _
Select p.Dname
A nested sub-query representation using LINQ:
The following SQL SELECT
SELECT ename FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp
WHERE sal < (SELECT MAX(sal) FROM emp))
can be coded in LINQ as following:
Dim query = From p In GetEmployeeList() _
Where (p.Sal) = (From r In GetEmployeeList() _
Where r.Sal < (From q In GetEmployeeList() _
Select q.Sal).Max() _
Select r.Sal).Max() _
Select p.Ename
Parallel sub-query representation using LINQ:
The following SQL SELECT
SELECT ename, sal FROM emp
WHERE sal >= (SELECT losal FROM salgrade
WHERE grade = 1)
AND sal <= (SELECT hisal FROM salgrade
WHERE grade = 1)
can be coded in LINQ as following:
Dim query = From p In GetEmployeeList() _
Where (p.Sal) >= (From q In GetSalGradeList() _
Where q.Grade = 1 _
Select q.Losal).First.ToString _
And (p.Sal) <= (From r In GetSalGradeList() _
Where r.Grade = 1 _
Select r.Hisal).First.ToString _
Select p.Ename, p.Sal
Representing sub-query as part of HAVING clause using LINQ:
The following SQL SELECT
SELECT deptno, MAX(sal) FROM emp
GROUP BY deptno
HAVING MAX(sal) > (SELECT AVG(sal) + 2000 FROM emp)
can be coded in LINQ as following:
Dim query = From p In GetEmployeeList() _
Group p By p.Deptno _
Into Group _
Where Group.Max(Function(p) p.Sal) > (From q In GetEmployeeList() _
Select q.Sal).Average() + 2000 _
Select _
Deptno, _
MaxSal = Group.Max(Function(p) p.Sal)
Jagadish Pulakhandam's 