none
Oracle Specified cast is not valid linq RRS feed

  • Question

  • Hi everyone,

    I have an issue that is driving me crazy with Oracle and EF, so I decided to look for help. Thanks in advance to all your answer.

    Here is the issue.

    I have an Oracle database with X tables; TABLE1 is related 1:N with TABLE2, 1:N with TABLE3 and N:1 TABLE4

    TABLE3 is related with TABLE5 N:1

    TABLE1..TABLE5 all fields are varchar2 except the ID witch is Number(10)

    I have a query that goes like this:

    var Table_query = context.TABLE1s.Select (x=>x);

    If I try to do any operation with Table_query (iterate, convert to list or enumerable) it through and error of type

    System.InvalidCastException: Specified cast is not valid

    but if I try this:

    var builtTABLE1 = context.TABLE1s.Select(x => new
                                         {
                                             TABLE4 = x.TABLE4,
                                             TABLE1_ID = x.TABLE1_ID,
                                             TABLE1_DESC = x.TABLE1_DESC,
                                             TABLE1_NAME = x.TABLE1_NAME,
                                             TABLE1_NUMBER = x.TABLE1_NUMBER,
                                             TABLE1_TYPE = x.TABLE1_TYPE,
                                            
                                         });

    I can iterate through the collection is because TABLE2 and TABLE3 are not in the query and they are navigation fields like TABLE4 but they are EntityCollection<T>

    Any help or clue will be more that welcome.

    Thanks.

    Peter

    • Moved by CoolDadTx Tuesday, October 1, 2013 1:48 PM EF related
    Tuesday, October 1, 2013 1:36 PM

Answers

  • Then the error is indicative of perhaps you using an int type in your model whereas Oracle is using a BIGINT column.  You should be able to compare your model to your tables to track it down.  Note that FK references in your model should be verified as well.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Proposed as answer by Fred BaoModerator Wednesday, October 2, 2013 1:31 AM
    • Marked as answer by peterposnag Thursday, October 3, 2013 2:08 AM
    Tuesday, October 1, 2013 2:45 PM

All replies

  • It sounds like one of the columns in one of the tables is causing a problem.  Ideally you should be able to scan the exception details and get to the exact error.   Alternatively you could temporarily remove the nav properties from the tables and then select each table separately to identify which table it is blowing up on.  You can then evaluate the columns. 

    Recently we ran into an issue with ODP.NET and bit columns.  If you're using ODP.NET then bit columns have to be treated as integrals otherwise you'll get a conversion error.  I'm not sure whether this is a scenario you're running into.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Tuesday, October 1, 2013 1:48 PM
  • Thanks for your fast answer,

    The only thing the error says "Specified cast is not valid, Value was either too large or too small for an Int32"

    You gave me a way to move forward.

    I will not check it as answered yet, just in case someone has another approach for this problem.

    Thanks,

    Peter

    Tuesday, October 1, 2013 2:33 PM
  • Then the error is indicative of perhaps you using an int type in your model whereas Oracle is using a BIGINT column.  You should be able to compare your model to your tables to track it down.  Note that FK references in your model should be verified as well.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Proposed as answer by Fred BaoModerator Wednesday, October 2, 2013 1:31 AM
    • Marked as answer by peterposnag Thursday, October 3, 2013 2:08 AM
    Tuesday, October 1, 2013 2:45 PM