Inner Join and Outer Join In DataTable using LINQ

In this Article I am explain how to Inner join and Outer join in Data tables using LINQ.

For it I have created two tables Employee and EmployeeGrade.

DataTable dtEmployee = new DataTable();

DataRow drEmployee = null;

dtEmployee.TableName = "Employee";

dtEmployee.Columns.Add("in_emp_id", typeof(int));

dtEmployee.Columns.Add("vc_emp_name", typeof(string));

drEmployee = dtEmployee.NewRow();

drEmployee["in_emp_id"] = 1;

drEmployee["vc_emp_name"] = "Jitendra Gangwar";

dtEmployee.Rows.Add(drEmployee);

DataRow drEmployee1 = dtEmployee.NewRow();

drEmployee1["in_emp_id"] = 2;

drEmployee1["vc_emp_name"] = "Sandeep Tiwari";

dtEmployee.Rows.Add(drEmployee1);

DataRow drEmployee2 = dtEmployee.NewRow(); ;

drEmployee2["in_emp_id"] = 3;

drEmployee2["vc_emp_name"] = "Shivendra Tiwari";

dtEmployee.Rows.Add(drEmployee2);

DataTable dtEmpGrade = new DataTable();

dtEmpGrade.TableName = "EmployeeGrade";

dtEmpGrade.Columns.Add("in_emp_id", typeof(int));

dtEmpGrade.Columns.Add("in_grade", typeof(int));

DataRow drgrade = dtEmpGrade.NewRow();

drgrade["in_emp_id"] = 1;

drgrade["in_grade"] = 1;

dtEmpGrade.Rows.Add(drgrade);

DataRow drgrade2 = dtEmpGrade.NewRow();

drgrade2["in_emp_id"] = 3;

drgrade2["in_grade"] = 2;

dtEmpGrade.Rows.Add(drgrade2);

Inner Join 

var InnerJoinResult = (from emp in dtEmployee.AsEnumerable()

                      join empgrade in dtEmpGrade.AsEnumerable()

                      on emp.Field<int>("in_emp_id") equals empgrade.Field<int>("in_emp_id")

                      select new

                      {

                        EmpId = emp.Field<int>("in_emp_id"),

                        EmpName = emp.Field<string>("vc_emp_name"),

                        Grade = empgrade.Field<int>("in_grade")

                      }).ToList();

OutPut:

Outer Join

var LeftOuterJoinResult = (from p in dtEmployee.AsEnumerable()

                           join t in dtEmpGrade.AsEnumerable()

                           on p.Field<int>("in_emp_id") equals t.Field<int>("in_emp_id") into                                 tempJoin from leftJoin in tempJoin.DefaultIfEmpty()

                           select new

                           {

                             EmpId = p.Field<int>("in_emp_id"),

                             EmpName = p.Field<string>("vc_emp_name"),

                             Grade = leftJoin == null ? 0 : leftJoin.Field<int>("in_grade")

                           }).ToList();

OutPut:-