I'm trying to import tables and views from an old database using ODAC 12c Release 4 and Visual Studio 2015 with EF 6 into an .edmx model.
I can import most tables and views just fine, but some of them contains errors and I can't figure out how to fix these. Specifically there are two types of errors I'm having trouble with:
- Foreign keys with wrong data type. Usually a NUMBER column connected to a NUMBER(9,0). These will be translated to decimal and int32, causing errors.
- Views without primary keys.
Previously I have used ODAC 11 with EF 5 where I could solve these errors in the following way:
- TheĀ import would add the problematic tables to the diagram and point out the error. To fix it all I had to do was to change the datatypes in the model.
- To get a primary key I added ROW_NUMBER() AS ID as a column and set it as a primary key with a disabled constraint CONSTRAINT ID_PK PRIMARY KEY (ID) DISABLE. This would let me import the view but I'd still get an error about primary keys being nullable so I created a script that would add Nullable=False for all primary keys. After this everything would work fine.
Trying to import tables and views with these problems using the new software is much more problematic. Instead of importing first and pointing out the errors afterwards, I'm not allowed to import at all. This is where I get stuck for the two problems:
- Trying to add tables with this problem will fail without explanation. Nothing will be added to neither diagram, model nor model.store. Adding one table at a time lets me add either end of the problematic foreign key connection but trying to add the other table will just give this error then do nothing:
The model was generated with warnings or errors. Model.edmx. Please see the Error List for more details. These issues must be fixed before running your application.
But the error list will be empty. I can't even see what table is causing the problem to help me fix the issue in the database. - Before adding row number as a primary key the view will be added to model.store but commented out with this error:
Error 6013: The table/view '[ViewName]' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.
After adding row number as a primary key I get this error instead:
Error 13101: Key part 'ID' for type '[ViewName]' is not valid. All parts of the key must be non-nullable.
The view won't be imported at all, giving me no way to fix the problem since Oracle doesn't allow Nullable=False on views. So I can't fix it before importing but I can't import it without fixing it first...
So how am I supposed to deal with these problems using ODAC 12c Release 4 and EF 6?
Having to go back to ODAC 11 and Visual Studio 2012 with EF 5 every time I want to import tables and views from the database is getting annoying. Been banging my head against these problems for almost a year now and getting nowhere, please help!
Searching for others with the same problem gives only a few hits and no answers.