Wednesday, May 16, 2012

LINQ to SQL – LEFT OUTER JOIN


LINQ to SQL – LEFT OUTER JOIN


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 practical situations we will have to join more than one table to get the details for reporting.

A LEFT OUTER JOIN is used to fetch all the details from the table which is in the Left side in the Query and the matching details from the table in the right side in the query.

Assume we have the following data in the Employee and Department Tables

Table: Employee


ID
NAME
Phone
Email
DepartmentID
Salery
1
Tom
123-123-1234
tom@abcsoftware.com
1
5000
2
Harry
123-123-1234
harry@abcsoftware.com
2
6000
3
Peter
111-222-3333
peter@abcsoftware.com
3
6500
4
John
111-222-3333
john@abcsoftware.com
3
7500
5
Charles
666-444-2222
charles@abcsoftware.com
NULL
6500



Table: Department
ID
NAME
1
Finance
2
Human Resources
3
IT
4
Sales
5
Marketing

The LEFT  OUTER JOIN SQL Query to join the 2 tables is as follows

SELECT     E.ID, E.Name, E.Phone, D.Name AS Department
FROM         Employee AS E INNER JOIN
                      Department AS D ON E.DepartmentID = D.ID

This query will fetch 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
NULL

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
        join d in dbContext.Departments on e.DepartmentID equals d.ID into dj
        fromin dj.DefaultIfEmpty()
        select new { e.ID, e.Name, e.Phone, Department = (d.Name)});




Search Flipkart Products:
Flipkart.com

1 comment:

Anonymous said...

The SQL query has inner join in it, can you post the correct one ?