LINQ : Left Outer, Inner Join on DataTable

Apply Inner joins and Outer joins using LINQ on Datatables.
Using Extension method.
Handling null values in LINQ expn while applying joins.




Following code snippet shows how can we apply left outer and inner join on 2
DataTable in C# using LINQ.




//Exteion method for  DataRowCollectionis added is optional though you can use foreach instead.

public static class DRCollectionExtention
{
    public static void AddRows(this DataRowCollection DRC,Object[][] Collc)
    {
        foreach (object[] values in Collc)
            DRC.Add(values);
    }
}

Copy paste following code in your new blank ASPX page's PageLoad().
Copy paste above 'DRCollectionExtention' class in any file but in same namespace as the page class is.
Please go through the comments in the code snippet.

        //primary key table
        DataTable Table1Primary = new DataTable();
        Table1Primary.Columns.Add(new DataColumn("EmpID", Type.GetType("System.Int32")));
        Table1Primary.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));

        //foreign key table
        DataTable Table2Secondary = new DataTable();
        Table2Secondary.Columns.Add(new DataColumn("EmpID", Type.GetType("System.Int32")));
        Table2Secondary.Columns.Add(new DataColumn("Address", Type.GetType("System.String")));
        DataRow dr = Table1Primary.NewRow();
        dr["EmpID"] = 1;
        dr["Name"] = "V1";
        Table1Primary.Rows.Add(dr);
        dr = Table1Primary.NewRow();
        dr["EmpID"] = 2;
        dr["Name"] = "V2";
        Table1Primary.Rows.Add(dr);
        dr = Table1Primary.NewRow();
        dr["EmpID"] = 3;
        dr["Name"] = "V3";
        Table1Primary.Rows.Add(dr);

        dr = Table2Secondary.NewRow();
        dr["EmpID"] = 1;
        dr["Address"] = "pune";
        Table2Secondary.Rows.Add(dr);
        dr = Table2Secondary.NewRow();
        dr["EmpID"] = 2;
        dr["Address"] = "Mumbai";
        Table2Secondary.Rows.Add(dr);

      
        //Copy Struture
        DataTable DTLeftOuterJoin = Table1Primary.Clone();
        DataTable DTInnerJoin = Table1Primary.Clone();

        //Get columns from child[secondary table] table
        var dt2Columns = Table2Secondary.Columns.OfType().Select(dc =>
                        new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
        //Make sure we do not add duplicated column to table, so select column only if does not exist in
        //primary[1st table]
        var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
                              where DTLeftOuterJoin.Columns.Contains(dc.ColumnName) == false
                              select dc;

        //Add columns to 1st primary table
        DTLeftOuterJoin.Columns.AddRange(dt2FinalColumns.ToArray());//Add 3rd column, i.e missing required column in table
      
        //similarly clone table for inner join too
        DTInnerJoin = DTLeftOuterJoin.Clone();

        //Left Outer Join Code
        ///from Table1Primary and Table2Secondary into 'joined'
        ///now, from joined that actually hold resultset like inner join
        ///Get itemarray from 'row2' if row from joined is not null
        ///else get item from itemarray from newly created row that will give default value
        ///for datarow itemarrays item. In general for sequence
        ///Add/concatenate this items to row1's itemArray
        var LOJrowData = from row1 in Table1Primary.AsEnumerable()
                         join row2 in Table2Secondary.AsEnumerable()
                         on row1.Field("EmpID") equals row2.Field("EmpID") into joined
                         from leftjoin in joined.DefaultIfEmpty()
                         select row1.ItemArray.Concat((leftjoin == null) ?
                                                       Table2Secondary.NewRow().ItemArray.Where(r2 => r2 == null) :
                                                       leftjoin.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)
                                                     ).ToArray();
        //Use extension mentod to add rows to datatrow collection :)
        DTLeftOuterJoin.Rows.AddRows(LOJrowData.ToArray());

        //Inner Join code
        ///from Table1Primary and Table2Secondary
        ///Get item from itemArray of row2 where item in row2 does not exist in row1 [avoid duplicates]
        ///Now, in case of Datacolumns like Date etc, if there are 2 different columns in row1 then this might not work
        ///Have not tried it, but it is clear.. right!
        var IJrowData = from row1 in Table1Primary.AsEnumerable()
                        join row2 in Table2Secondary.AsEnumerable()
                        on row1.Field("EmpID") equals row2.Field("EmpID")
                        select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();

        //Use extension mentod to add rows to datatrow collection :)
        DTInnerJoin.Rows.AddRows(IJrowData.ToArray());


        //Display on page using GridView
        System.Web.UI.HtmlControls.HtmlGenericControl div1 = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
        div1.ID = "div1";
        System.Web.UI.HtmlControls.HtmlGenericControl div2 = new System.Web.UI.HtmlControls.HtmlGenericControl("div");
        div2.ID = "div2";

        GridView GVLOJ = new GridView();
        GVLOJ.DataSource = DTLeftOuterJoin;
        GVLOJ.ID = "GVLOJ";
        GVLOJ.DataBind();

        GridView GVIJ = new GridView();
        GVIJ.DataSource = DTInnerJoin;
        GVIJ.ID = "GVIJ";
        GVIJ.DataBind();
      
        div1.Controls.Add(new LiteralControl("Left Outer Join"));
        div1.Controls.Add(GVLOJ);
        form1.Controls.Add(div1);

        form1.Controls.Add(new LiteralControl("
"));

        div2.Controls.Add(new LiteralControl("Inner Join"));
        div2.Controls.Add(GVIJ);
        form1.Controls.Add(div2);