LINQ to SQL Inner Join

Input tables

---

Department table

Employees table

Expenses table

Employee Expenses table

LINQ Query :

Below LINQ query will get the data from tblEmployees table and tblDepartments table by inner join.

MyDBDataContext sqlObj = new MyDBDataContext();

var employees = from emps in sqlObj.tblEmployees

                join depts in sqlObj.tblDepartments on emps.DepartmentID equals depts.DepartmentID

                select new

                {

                    emps.EmployeeID,

                    emps.EmployeeName,

                    emps.Salary,

                    depts.DepartmentName

                };

gvemployees.DataSource = employees;

gvemployees.DataBind();

Output :

LINQ Query for inner join with multiple table :

Below LINQ query will get data from multiple tables by using inner join.

MyDBDataContext sqlObj = new MyDBDataContext();

var employees = from emps in sqlObj.tblEmployees

                join depts in sqlObj.tblDepartments on emps.DepartmentID equals depts.DepartmentID

                join empexp in sqlObj.tblEmployeeExpenses on emps.EmployeeID equals empexp.EmployeeID

                join exp in sqlObj.tblExpenses on empexp.ExpenseID equals exp.ExpenseID

                select new

                {

                    emps.EmployeeID,

                    emps.EmployeeName,

                    emps.Salary,

                    depts.DepartmentName,

                    exp.Expense,

                    ExpenseAmount = empexp.Amount,

                    ExpenseDate = empexp.ExpenseDate.ToShortDateString()

                };

gvemployees.DataSource = employees;

gvemployees.DataBind();

OUTPUT

Cross Join
A cross join is also known as a Cartesian Join. This join does not require any condition in the join but LINQ does not allow using the "join" keyword without any condition. Using two from clauses we can do a cross join.

1.  var crossJoin = from e in Context.EmployeeMasters  
2.     from d in Context.DepartmentMasters  
3.     select new  
4.     {  
5.         EmployeeCode = e.Code,  
6.         EmployeeName = e.Name,  
7.         DepartmentName = d.Name  
8.     };

Full outer join
A full outer join is a logical union of a left outer join and a right outer join. LINQ does not support full outer joins directly, the same as right outer joins.

1. var leftOuterJoin = from e in Context.EmployeeMasters  
2.     join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept  
3.     from department in dept.DefaultIfEmpty()  
4.     select new  
5.     {  
6.         EmployeeCode = e.Code,  
7.         EmployeeName = e.Name,  
8.         DepartmentName = department.Name  
9.     };  
10.     var rightOuterJoin = from d in Context.DepartmentMasters  
11.     join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
12.     from employee in emp.DefaultIfEmpty()  
13.     select new  
14.     {  
15.         EmployeeCode = employee.Code,  
16.         EmployeeName = employee.Name,  
17.         DepartmentName = d.Name  
18.     };  
19.     leftOuterJoin = leftOuterJoin.Union(rightOuterJoin); 

Right outer join

A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.

1. var rightOuterJoin = from d in Context.DepartmentMasters  
2.     join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
3.     from employee in emp.DefaultIfEmpty()  
4.     select new  
5.     {  
6.         EmployeeCode = employee.Code,  
7.         EmployeeName = employee.Name,  
8.         DepartmentName = d.Name  
9.     };

Left outer join
A Left Outer join returns all records from the left table and the matching record from the right table. If there are no matching records in the right table then it returns null. If we want to do a Left Outer join in LINQ then we must use the keyword "into" and method "DefaultIfEmpty".

    

1.  var leftOuterJoin = from e in Context.EmployeeMasters  
2.     join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept  
3.     from department in dept.DefaultIfEmpty()  
4.     select new  
5.     {  
6.         EmployeeCode = e.Code,  
7.         EmployeeName = e.Name,  
8.         DepartmentName = department.Name  
9.     };