Specific cast is not valid in linq query when compare two tables

[ahmedsa]

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;
        }

 

[jmcilhinney]

Repeating what I posted elsewhere, have you examined both of those DataTables to make sure that every record has an 'int' value in the UnitCode column and a 'decimal' value in the CurrentMeterReading column? My first guess would be that at least one row contains NULL in one of those columns, although I'm not sure that I'd have expected that specific error message in that case. The other possibility is that one of the columns doesn't contain the specified data type at all, e.g. UnitCode contains 'string' values rather than 'int'.

Looking at your screenshot, which is discernable here, I would say that the possibilities include that one of the columns contains at least one 'string' value, or maybe CurrentMeterReading contains 'int' values in the second table, or maybe there's a NULL that we can't see.