Oracle Specified cast is not valid linq

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

Answer 1

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

Answer 2

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

Answer 3

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