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"))
};
}
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.
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.
PrimaryKey = new DataColumn[] {dtblA. Columns["col1"]} DataTable dtblJoined = new DataTable(); dtblJoined. Merge(dtblA, false, MissingSchemaAction. AddWithKey); dtblJoined.
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")
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With