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

Question

0.00/5 (No votes)

See more:

, +

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

C#

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 .

C#

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

[^]

What I have tried:

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

Posted 25-Apr-18 8:43am

ahmed_sa

Updated 25-Apr-18 11:22am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-04-25T09:00:00

Looking at your screen-shot, the CurrentMeterReading field is an integer in one table, and a floating-point number in another.

The Field<T> extension method does not attempt to change the type of the source field. It simply tries to unbox it to the requested type. If the source type doesn't precisely match the requested type, it will fail with an InvalidCastException.

NB: The code you have which initializes the columns of the DataTable is not necessary. The tables you create are then immediately thrown away and replaced with the tables loaded from the database and Excel file.

You will need to debug your code to work out what the actual field types are. Alternatively, use Convert.ToDecimal to try to change the type:

C#

where Convert.ToDecimal(y["CurrentMeterReading"] > Convert.ToDecimal(x["CurrentMeterReading"])
select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = Convert.ToDecimal(x["CurrentMeterReading"]) })

Maciej Los · Answer 2 · 2018-04-25T11:24:00

Using IMEX=1 in connection string - admittedly - avoids crashes during the process of retrieving data for mixed data columns, but it causes OledDb provider to treat data as a text. More details, you'll find here: Microsoft ACE OLEDB 12.0 Connection Strings - ConnectionStrings.com[^]

I'd remove that part from connection string and try your code again. Note: do not forget to change every single column in Excel file from General to specific data type.

Another way to work-around it is to cast/convert data in SELECT statement. See: Type Conversion Functions - Access[^]:

SQL

SELECT CDbl(CurrentMeterReading) AS CurrentMeterReading, CDbl(CurrentMeterReading) AS CurrentMeterReading
FROM [Sheet1$];

Good luck!

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0