Inner Join and Outer Join In DataTable using LINQ

Introduction

 

In this blog we will discuss about how to implement Inner Join and outer join between two DataTable using Linq.

 

DataTable

 

This class represents to store data in rows and columns. It is present in System.Data namespace. You can add rows, columns programmatically.

 

Using Code 

 

We have to create two DataTables. One is "Emp" which contains two columns called EmpId and EmpName and second table is "EmpGrade" which contains two columns EmpId and Grade. And column EmpId of two tables are referring each other. Here is the code to create DataTables: 

DataTable dt = new DataTable();
DataRow dr = null;
dt.TableName = "Emp";

dt.Columns.Add("EmpId", typeof(int));
dt.Columns.Add("EmpName", typeof(string));

dr = dt.NewRow();
dr["EmpId"] = 1;
dr["EmpName"] = "Manas";
dt.Rows.Add(dr);

DataRow dr1 = null;
dr1 = dt.NewRow();
dr1["EmpId"] = 2;
dr1["EmpName"] = "Prakas";
dt.Rows.Add(dr1);

DataRow dr2 = null;
dr2 = dt.NewRow();
dr2["EmpId"] = 3;
dr2["EmpName"] = "Akas";
dt.Rows.Add(dr2);

DataTable dt2 = new DataTable();

dt2.TableName = "EmpGrade";
dt2.Columns.Add("EmpId", typeof(int));
dt2.Columns.Add("Grade", typeof(int));

DataRow drgrade = null;
drgrade = dt2.NewRow();
drgrade["EmpId"] = 1;
drgrade["Grade"] = 3;
dt2.Rows.Add(drgrade);

DataRow drgrade2 = null;
drgrade2 = dt2.NewRow();
drgrade2["EmpId"] = 3;
drgrade2["Grade"] = 2;
dt2.Rows.Add(drgrade2);

Inner Join 

var JoinResult = (from p in dt.AsEnumerable()
                  join t in dt2.AsEnumerable()
                  on p.Field<string>("EmpId") equals t.Field<string>("EmpId")
                  select new
                  {
                       EmpId = p.Field<int>("EmpId"),
                      EmpName = p.Field<sting>("EmpName"),
                      Grade = t.Field<int>("Grade")
                  }).ToList();

Result

 

EmpId    EmpName    Grade

  1            Manas           3

  3            Akas             2 

 

Outer Join(Left)

var JoinResult = (from p in dt.AsEnumerable()
    join t in dt2.AsEnumerable()
    on p.Field<string>("EmpId") equals t.Field<string>("EmpId") into tempJoin
    from leftJoin in tempJoin.DefaultIfEmpty()
    select new
   {
       EmpId = p.Field<int>("EmpId"),
       EmpName = p.Field<string>("EmpName"),
       Grade = leftJoin == null ? 0 : leftJoin.Field<int>("Grade")
   }).ToList();

Result:

 

EmpId      EmpName      Grade

1                Manas             3

2                Prakas            0 

3                Akas               2

 

Conclusion 

 

In this blog we discussed how to implement Inner join and outer join between two DataTable using Linq.