LINQ to SQL - Sub Queries

LINQ to SQL - Sub Queries

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

In some situations have to use sub queries to get details from a different table, in SQL Server we can use the IN clause to achieve this.

Let us try to get the details of the Employees who belong to the IT department, assuming that the tables contain the following data.

ID	NAME	PHONE	Department
1	Tom	123-123-1234	Finance
2	Harry	123-123-1234	Human Resources
3	Peter	111-222-3333	IT
4	John	111-222-3333	IT
5	Charles	666-444-2222	Sales

The SQL Query to get the sub query data is as follows

SELECT     ID, Name, Phone

FROM         Employee

WHERE     (DepartmentID IN

                          (SELECT     ID

                            FROM          Department

                            WHERE      (Name = 'IT')))

This query will fetch the following data

ID	NAME	PHONE
3	Peter	111-222-3333
4	John	111-222-3333

Now let us see how the same can be achieved using a LINQ query.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

var emp = from e in dbContext.Employees

where dbContext.Departments.Any(d => d.ID == e.DepartmentID && d.Name.Equals("IT"))

          select new { e.ID, e.Name, e.Phone};

Related Posts

What is LINQ?

LINQ to SQL

LINQ to SQL Sample

LINQ to SQL Select Query with Specific columns

LINQ to SQL Select query with Filter (WHERE Clause)
LINQ to SQL Select Query with Alias Name