Entity Framework: Creating entities from Oracle View

Question

I've been having this issue for over a week, and I know there are lots of questions about this, but I haven't seen one using Oracle. I'm using EF Database First, I need to create an entity from a Oracle View and because of the "You need to define a primary key" restriction, EF won't let me.

Messages I've gotten:

Key part 'COLUMN_NAME' for type 'VIEW_NAME' is not valid. All parts of the key must be non-nullable.
Warning Error 6002: The table/view 'VIEW_NAME' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Work arounds I've tried:

Modify de .edmx, and add the primary key myself. RESULT: The framework will say "That column is nullable, we can't accept that"

Modify my view using the following syntax:

CREATE OR REPLACE FORCE VIEW SCHEMA.MY_VIEW
(COLUMN, FIELD1, FIELD2, FIELD3)
AS 
SELECT 
     NVL(ROW_NUMBER() OVER(ORDER BY FIELD1), 0) AS COLUMN
     , FIELD1
     , FIELD2
     , FIELD3

WITH READ ONLY;

ALTER VIEW SCHEMA.MY_VIEW
 ADD CONSTRAINT MY_VIEW_PK
  PRIMARY KEY
  (CODIGO_MONEDA)
  RELY
  DISABLE;

I've tried many version of the query above, adding NVL to the others field, creating a primary key. I just can't get it to work.

Stephane Stephane · Accepted Answer · 2016-10-18T08:44:21

unfortunetly I think there is no solution. I have complained for that from the beginning of this year, EF Team has recognized that this is a bug but they cutted in the middle of the year all bug fixes for EF6 to develop EF Core. See this link: http://entityframework.codeplex.com/workitem/2888

In fact, the problem is in the designer. I presume you are using Visual Studio 2015 ; that's why we are still using Visual Studio 2012 with EF 6.1.3.

We cannot migrate to Visual Studio 2015 without a fix.

I am very suprised that not so much people complained about that. It's just a killer point for .NET projects with Oracle databases.

Regards

Entity Framework: Creating entities from Oracle View

1 Answers