none
specific cast is not valid in linq query when compare two tables RRS feed

  • Question

  • 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();



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

    Image for debug

                                   
    Wednesday, April 25, 2018 6:32 PM

Answers

  • Hello engahmedbarbary,

    If the type of Currentmeterreading is decimal type the value couldn't be null, when you don't assign value to it the default value is 0.0m. So the condition "x.currentreading is null" need to be removed. As for how to add condition to linq query, you need to use "&&" or "||" operator.

    where y.Field<decimal>("CurrentReading") > x.Field<decimal>("CurrentReading") && x.Field<decimal>("CurrentReading")>0

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 2:48 AM
    Moderator

All replies

  • Probably the columns contain strings, not numbers.

    Try replacing expressions like x.Field<int>("UnitCode") with Convert.ToInt32(x["UnitCode"]). In case of decimals use Convert.ToDecimal.

    Or maybe do the conversions when you perform the query and fill the data tables.



    • Edited by Viorel_MVP Wednesday, April 25, 2018 7:23 PM
    Wednesday, April 25, 2018 7:22 PM
  • thank you for reply

    How to do that below

    Or maybe do the conversions when you perform the query and fill the data tables.

    can you give me example

    Wednesday, April 25, 2018 10:39 PM
  • Hello engahmedbarbary,

    >>error display in linq query "specific cast is not valid"

    Please make sure your column type in database does match your field type in linq. For example, when you write down the below code.

    where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")

    You should ensure the column whose name is "CurrentMeterReading" is decimal type, If not this will throw exception "cast is not valid". Because types of them don't match each other and conversions will bring unexpected error.

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 1:58 AM
    Moderator
  • thank you for reply it solved but if i needed to add to condition to linq query

    but i dont know how to added .

    I need to add

    OR Currentmeterreading is null OR Currentmeterreading >0

    to linq query based on x.currentreading >0 or x.currentreading is null

    Thursday, April 26, 2018 2:29 AM
  • Hello engahmedbarbary,

    If the type of Currentmeterreading is decimal type the value couldn't be null, when you don't assign value to it the default value is 0.0m. So the condition "x.currentreading is null" need to be removed. As for how to add condition to linq query, you need to use "&&" or "||" operator.

    where y.Field<decimal>("CurrentReading") > x.Field<decimal>("CurrentReading") && x.Field<decimal>("CurrentReading")>0

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 2:48 AM
    Moderator