Specific cast is not valid in linq query when compare two tables
Problem
Error display in linq query "specific cast is not valid" at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value) at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName)
LinqQuery give error
var query1 = (from x in table1.AsEnumerable()
join y in table2.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")
where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")
select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList();
Details
When make debug the first data table tableReadingExcelsheet retrieve data from excel sheet .
second datatable readingfromInvoiceTablesql retrieve data from wahinvoice table in sql .
I need to get list of rows in excel sheet that have current reading less than
currentreading in wahinvoice table for same UnitCode then display in datagridview .
private void button2_Click(object sender, EventArgs e)
{
DataTable tableReadingExcelsheet = new DataTable();
tableReadingExcelsheet.Columns.AddRange(new DataColumn[] { new DataColumn("UnitCode", typeof(int)), new DataColumn("CurrentMeterReading", typeof(decimal)) });
tableReadingExcelsheet = ShowdataFromExcel();
DataTable readingfromInvoiceTablesql = new DataTable();
readingfromInvoiceTablesql.Columns.AddRange(new DataColumn[] { new DataColumn("Serial", typeof(int)), new DataColumn("UnitCode", typeof(int)), new DataColumn("CurrentMeterReading", typeof(decimal)) });
readingfromInvoiceTablesql = GetCurrentReadingUnitCodesql();
var query1 = (from x in tableReadingExcelsheet.AsEnumerable()
join y in readingfromInvoiceTablesql.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")
where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")
select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList();
dataGridView1.DataSource = query1;
dataGridView1.Refresh();
}
//get data from excel success
public System.Data.DataTable ShowdataFromExcel()
{
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] ";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "[Sheet5$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet5$]"];
return dt;
}
//get data from sql wahinvoice success
public System.Data.DataTable GetCurrentReadingUnitCodesql()
{
sqlquery = @"select Serial,UnitCode, CurrentMeterReading
from( select Serial,UnitCode, CurrentMeterReading, ROW_NUMBER() OVER(PARTITION BY UnitCode ORDER BY Serial desc) as rn
from WAHInvoice) as a
where rn = 1";
System.Data.DataTable tbCurrentReading = DataAccess.ExecuteDataTable(sqlquery);
return tbCurrentReading;
}