System.InvalidCastException with Comparing Two Datatables

Question

I have been having an issue with comparing two data tables in C# using LINQ. The purpose of the application is to take data from 3 different Oracle databases and transfer said data to a MySQL database. Inside of the application I am pulling both tables and comparing them to find the lines that are missing in the MySQL table and add them.

The table from the MySQL database utilizes three primary keys because the data I am grabbing from can contain duplicates. The same data is sent to each of the three Oracle databases but they are on different environment servers. I.e.(production, staging, etc...) I am able to throw all three into one data table by adding the environment column so that they rows are not duplicated but when I compare and find the missing lines and try to copy them to a new data table I always get the invalid cast exception.

I am grabbing the first table from the Oracle database then I make a clone of the table and set the DataTypes of the cloned table.

    o1Clone.Columns["CASEID"].DataType = typeof(Int64);
    o1Clone.Columns["CASENAME"].DataType = typeof(string);
    o1Clone.Columns["CREATEDDATE"].DataType = typeof(DateTime);
    o1Clone.Columns["STUDYMODEID"].DataType = typeof(int);
    o1Clone.Columns["ENVIRONMENT"].DataType = typeof(string);

After adding the DataTypes I import all three different datatables into one and compare them against the MySQL database.

var matched = from table1 in oClone.AsEnumerable()
                          join table2 in mClone.AsEnumerable() on table1.Field<Int64>("CASEID") equals table2.Field<Int64>("CASEID")
                          where table1.Field<string>("CASENAME") == table2.Field<string>("CASENAME") || table1.Field<DateTime>("CREATEDDATE") == table2.Field<DateTime>("CREATEDDATE") || table1.Field<int>("STUDYMODEID") == table2.Field<int>("STUDYMODEID") || table1.Field<string>("ENVIRONMENT") == table2.Field<string>("ENVIRONMENT")
                          select table1;
            var missing = from table1 in oClone.AsEnumerable()
                          where !matched.Contains(table1)
                          select table1;
            DataTable mm = missing.CopyToDataTable();

Here is a picture of the error:

I don't see why I am getting the error because I am making sure the DataTypes match so any help with this would be much appreciated. Also if someone has a better way of doing this as opposed to using LINQ feel free to post.

Are any of those database fields nullable? If so, your .Net types need to match. — Seano666, Mar 25 '16 at 18:35
Out of my element here, but it appears that one of these is throwing: `table1.Field("CASEID")`. I would verify that then start googling from there, e.g., [a maybe related SO post](http://stackoverflow.com/questions/35227563/oracle-dataaccess-datarow-fielddecimal-invalidcastexception). Also, if the underlying datatype is not compatible with Int64 then I bet `o1Clone.Columns["CASEID"].DataType = typeof(Int64)` is not doing what you want. You are saying, "This is a Int64 DataType" even though it may be wholly incompatible with and non-convertible to Int64. — Quantic, Mar 25 '16 at 19:08

score 0 · Answer 1 · answered Mar 30 '16 at 20:26

After a lot of trial and error I was able to find a solution that works.

var matched = from table1 in oClone.AsEnumerable()
              join table2 in mClone.AsEnumerable() on table1.Field<decimal>("CASEID") equals table2.Field<Int64>("CASEID")
              where table1.Field<decimal>("CASEID") == table2.Field<Int64>("CASEID") || 
              table1.Field<string>("CASENAME") == table2.Field<string>("CASENAME") || 
              table1.Field<DateTime>("CREATEDDATE") == table2.Field<DateTime>("CREATEDDATE") || 
              table1.Field<int>("STUDYMODEID") == table2.Field<int>("STUDYMODEID") || 
              table1.Field<string>("ENVIRONMENT") == table2.Field<string>("ENVIRONMENT")
              select table1;
var missing = from table1 in oClone.AsEnumerable()
              where !matched.Contains(table1)
              select table1;

System.InvalidCastException with Comparing Two Datatables

1 Answers1