How to Handle Null using LINQ during Datatable CrossJoin C#
-
Hi Friends I have multiple datatables, and the datatables may or may not have data, depends on the user selection. I have to get Cartesian Product of all the Datatables, and pass the Cartesian Product result as datasource for Gridview. I am using following code which is working fine if all the DataTable has rows. If any of the table is not having row, then I am getting no records in my resultant cartesian product table. Please help in this. var newDatatable = from TowerName in dtTower.AsEnumerable() from CountryName in dtCountry.AsEnumerable() from Level in dtLevel.AsEnumerable() from CityName in dtCity.AsEnumerable() from BandName in dtBand.AsEnumerable() from EType in dtEmploymentType.AsEnumerable() from Skill in dtSkill.AsEnumerable() select new { TowerName, CountryName, Level, CityName, BandName, EType, Skill }; DataTable dtlinq = new DataTable("poclinq"); foreach (var item in newDatatable) { DataRow dr = dtlinq.NewRow(); dr["TowerName"] = item.TowerName.ItemArray[0]; dr["CountryName"] = item.CountryName.ItemArray[0]; dr["Level"] = item.Level.ItemArray[0]; dr["CityName"] = item.CityName.ItemArray[0]; dr["BandName"] = item.BandName.ItemArray[0]; dr["EType"] = item.EType.ItemArray[0]; dr["Skill"] = item.Skill.ItemArray[0]; dtlinq.Rows.Add(dr); }
-
Hi Friends I have multiple datatables, and the datatables may or may not have data, depends on the user selection. I have to get Cartesian Product of all the Datatables, and pass the Cartesian Product result as datasource for Gridview. I am using following code which is working fine if all the DataTable has rows. If any of the table is not having row, then I am getting no records in my resultant cartesian product table. Please help in this. var newDatatable = from TowerName in dtTower.AsEnumerable() from CountryName in dtCountry.AsEnumerable() from Level in dtLevel.AsEnumerable() from CityName in dtCity.AsEnumerable() from BandName in dtBand.AsEnumerable() from EType in dtEmploymentType.AsEnumerable() from Skill in dtSkill.AsEnumerable() select new { TowerName, CountryName, Level, CityName, BandName, EType, Skill }; DataTable dtlinq = new DataTable("poclinq"); foreach (var item in newDatatable) { DataRow dr = dtlinq.NewRow(); dr["TowerName"] = item.TowerName.ItemArray[0]; dr["CountryName"] = item.CountryName.ItemArray[0]; dr["Level"] = item.Level.ItemArray[0]; dr["CityName"] = item.CityName.ItemArray[0]; dr["BandName"] = item.BandName.ItemArray[0]; dr["EType"] = item.EType.ItemArray[0]; dr["Skill"] = item.Skill.ItemArray[0]; dtlinq.Rows.Add(dr); }
priyaahh wrote:
If any of the table is not having row, then I am getting no records in my resultant cartesian product table.
The Cartesian product of any set with the empty set is the empty set. That's fairly basic set theory. You'll need a custom extension method which will return a single empty row if the table is empty:
public static class DataTableExtensions
{
public static IEnumerable<DataRow> AsEnumerableNotEmpty(this DataTable table)
{
if (table == null) throw new ArgumentNullException("table");
if (table.Rows.Count != 0) return table.Rows.Cast<DataRow>();
return new[] { table.NewRow() };
}
}You'll then need to change your LINQ query to call the custom method:
var newDatatable = from TowerName in dtTower.AsEnumerableNotEmpty()
from CountryName in dtCountry.AsEnumerableNotEmpty()
from Level in dtLevel.AsEnumerableNotEmpty()
from CityName in dtCity.AsEnumerableNotEmpty()
from BandName in dtBand.AsEnumerableNotEmpty()
from EType in dtEmploymentType.AsEnumerableNotEmpty()
from Skill in dtSkill.AsEnumerableNotEmpty()
select new { TowerName, CountryName, Level, CityName, BandName, EType, Skill };
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
priyaahh wrote:
If any of the table is not having row, then I am getting no records in my resultant cartesian product table.
The Cartesian product of any set with the empty set is the empty set. That's fairly basic set theory. You'll need a custom extension method which will return a single empty row if the table is empty:
public static class DataTableExtensions
{
public static IEnumerable<DataRow> AsEnumerableNotEmpty(this DataTable table)
{
if (table == null) throw new ArgumentNullException("table");
if (table.Rows.Count != 0) return table.Rows.Cast<DataRow>();
return new[] { table.NewRow() };
}
}You'll then need to change your LINQ query to call the custom method:
var newDatatable = from TowerName in dtTower.AsEnumerableNotEmpty()
from CountryName in dtCountry.AsEnumerableNotEmpty()
from Level in dtLevel.AsEnumerableNotEmpty()
from CityName in dtCity.AsEnumerableNotEmpty()
from BandName in dtBand.AsEnumerableNotEmpty()
from EType in dtEmploymentType.AsEnumerableNotEmpty()
from Skill in dtSkill.AsEnumerableNotEmpty()
select new { TowerName, CountryName, Level, CityName, BandName, EType, Skill };
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
