Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Left Outer Join - LINQ to Datatable

I'm trying to apply a left outer join using LINQ on two data tables. I'm receiving the exception listed below when I try to debug and view data contained in result variable:

System.ArgumentException: Value cannot be null. Parameter name: row

Code:

private DataTable DataTable1()
{
    DataRow dataRow = null;
    DataTable dt1 = new DataTable();
    dt1.Columns.Add("EmpId");
    dt1.Columns.Add("EmpName");
    dataRow = dt1.NewRow();
    dataRow["EmpId"] = "EMP001";
    dataRow["EmpName"] = "Ajaj Kumar";
    dt1.Rows.Add(dataRow);
    dataRow = dt1.NewRow();
    dataRow["EmpId"] = "EMP002";
    dataRow["EmpName"] = "Sanjay Gupta";
    dt1.Rows.Add(dataRow);
    dataRow = dt1.NewRow();
    dataRow["EmpId"] = "EMP003";
    dataRow["EmpName"] = "Ashish Charan";
    dt1.Rows.Add(dataRow);
    dt1.AcceptChanges();
    return dt1;
}

private DataTable DataTable2()
{
    DataRow dr = null;
    DataTable dt2 = new DataTable();
    dt2.Columns.Add("EmpId");
    dt2.Columns.Add("Salary");
    dr = dt2.NewRow();
    dr["EmpId"] = "EMP001";
    dr["Salary"] = "50000";
    dt2.Rows.Add(dr);
    dr = dt2.NewRow();
    dr["EmpId"] = "EMP002";
    dr["Salary"] = "45000";
    dt2.Rows.Add(dr);
    dt2.AcceptChanges();
    return dt2;
}

private void Form1_Load(object sender, EventArgs e)
{
    var empInfo = DataTable1().AsEnumerable();
    var empSal = DataTable2().AsEnumerable();

    var result = from dtEmpRow in empInfo
            join dtEmpSal in empSal
            on dtEmpRow.Field<string>("EmpId") equals dtEmpSal.Field<string>("EmpId")
            into outer
            from dtEmpSal in outer.DefaultIfEmpty()
            select new
            {
                Id = dtEmpRow.Field<string>("EmpId"),
                Name = dtEmpRow.Field<string>("EmpName"),
                Salary = ((dtEmpRow == null) ? "(no salary)" : dtEmpSal.Field<string>("Salary"))
            };
}
like image 426
Ashish Charan Avatar asked Jun 19 '13 20:06

Ashish Charan


People also ask

How to use left Outer join in LINQ?

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

Is there left join in LINQ?

In LINQ, LEFT JOIN or LEFT OUTER JOIN is used to return all the records or elements from the left side collection and matching the elements from the right side of the collection. In LINQ, to achieve the LEFT Join behavior, it is mandatory to use the "INTO" keyword and "DefaultfEmpty()" method.

How to left join two DataTable in c#?

PrimaryKey = new DataColumn[] {dtblA. Columns["col1"]} DataTable dtblJoined = new DataTable(); dtblJoined. Merge(dtblA, false, MissingSchemaAction. AddWithKey); dtblJoined.


1 Answers

That is because here dtEmpSal is null (default case if sequence is empty):

from dtEmpSal in outer.DefaultIfEmpty() // dtEmpSal is null

When you are trying to call Field<T> extension on DataRow which is null, you get that exception:

dtEmpSal.Field<string>("Salary") // System.ArgumentException

Fix it with ternary operator. You was near, but checked wrong value:

from dtEmpRow in empInfo
join dtEmpSal in empSal
    on dtEmpRow.Field<string>("EmpId") equals dtEmpSal.Field<string>("EmpId")
into outer
from dtEmpSal in outer.DefaultIfEmpty()
select new
{
    Id = dtEmpRow.Field<string>("EmpId"),
    Name = dtEmpRow.Field<string>("EmpName"),
    // here instead of dtEmpRow you should check dtEmpSal
    Salary = (dtEmpSal == null) ? "(no salary)" : dtEmpSal.Field<string>("Salary")
};
like image 91
Sergey Berezovskiy Avatar answered Sep 22 '22 05:09

Sergey Berezovskiy