C# .NET - Specific cast is not valid in linq query when compare two tables

Asked By ahmed aziz on 25-Apr-18 04:20 PM

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;
        }
Robbe Morris replied to ahmed aziz on 29-Apr-18 07:22 PM
This means at least one record with at least one column holds data that cannot be cast to the .net data type you are expecting.  Perhaps a string when you are expecting a number of some sort or a string when your code expects a date or datetime.