How do I fix problems when importing an Oracle database into an Entity Framework model using ODAC 12c Release 4 and EF 6?

Question

I'm trying to import tables and views from an Oracle 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:

1. Foreign keys with wrong data type. Usually a NUMBER foreign key column connected to a NUMBER(9,0) column. These will be translated to decimal and int32, causing errors.
2. Views without primary keys.

Previously I have used ODAC 11 with EF 5 where I could solve these errors in the following way:

1. 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.
2. 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:

1. 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.

2. 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...

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.

Searching for others with the same problem only gives a few hits and no answers.

---

EDIT: I found a work around for the problem with views.

I created a Row_Number table with Row_Number as the only column and joined the view with that. Since the Row_Number from the table is a primary key it will be marked as Nullable=False in the resulting view and can be imported.

Some code to help anyone else with the same problem:

CREATE TABLE "ROW_NUMBER" 
   (                  "ROW_NUMBER" NUMBER(9,0) NOT NULL ENABLE, 
                       CONSTRAINT "ROW_NUMBER_PK" PRIMARY KEY ("ROW_NUMBER"));

---

CREATE OR REPLACE PROCEDURE CREATE_ROW_NUMBER IS
LOOP_ROW_NUMBER NUMBER := 1;
BEGIN  
  LOOP
    INSERT INTO ROW_NUMBER(ROW_NUMBER)
    VALUES (LOOP_ROW_NUMBER);
    LOOP_ROW_NUMBER := LOOP_ROW_NUMBER + 1;
    IF LOOP_ROW_NUMBER > 1000000 THEN
    EXIT;
    END IF;
  END LOOP;
  COMMIT;
END CREATE_ROW_NUMBER;

---

begin
  CREATE_ROW_NUMBER;
end;

---

CREATE OR REPLACE FORCE VIEW New_View (“ID”, [COLUMNS]) AS 
  SELECT Row_Number.Row_Number, [COLUMNS]
  FROM Row_Number INNER JOIN (
  SELECT ROWNUM Row_Number, [COLUMNS]
FROM(
                      SELECT * FROM Table1
                      UNION 
                      SELECT * FROM Table2
) Original_View ON Row_Number.Row_Number = Original_View.Row_Number;

Waldgrave

Found a workaround for views without primary keys. Added to post. – Waldgrave

Adding this join slowed down my Where <Column> LIKE '%searchTerm%' nearly tenfold. – Obaza

But queries with ID=... are working the same as without join. – Obaza

Answer 1

Nice solution. works perfectly. Here I want to share 3 additions.

1. a ")" is missing
2. you can use an already existing view as original view
3. add "_1" in the alias view name

Query:

CREATE OR REPLACE FORCE VIEW New_View (“ID”, [COLUMNS]) AS 
    SELECT Row_Number.Row_Number, [COLUMNS]
    FROM Row_Number INNER JOIN (
        SELECT ROWNUM Row_Number, [COLUMNS]
        FROM (SELECT [COLUMNS] FROM Original_View)
        ) Original_View_1 
    ON Row_Number.Row_Number = Original_View_1.Row_Number; 

Fill

Please comment on the solution you're referring to. This is not an answer by itself. – Tonettetoney

@Tonettetoney He is probably referring to the solution given by OP in the question – Nevil