Error when when provider reports nullable keys

[stephanebo21]

Sorry to post again my issue in reference of issue #109.

Sorry for my late answer, but I needed time to install a new clean machine to reproduce this case. I have reproduced the issue and can give you details. Before when the solution worked, after updating my EF Model I could encounter this kind of warning:

warning 6002: The table/view 'myView' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

This issue was resolved by the development of a custom tool which modify xml model after the update and everything worked fine.

Now, since I am trying to work with Visual Studio 2015, EF 6.1.3, when I try to add the same view I have this error:

Error 13101: Key part ‘JOB_NAME’ for type ‘JOBS’ is not valid. All parts of the key must be non-nullable.

There is no way to modify xml because my view is not added to the Model Store. I am pretty sure at 100% that this problem is specific to users which use EF and Oracle. All references I found on the internet are from people who have the same development context.

This is a critical risk for us because we have no way to upgrade our application to new versions…

Seems like mySQL users have some issues similar from exactly the same version of Visual Studio and Entity Framework: https://social.msdn.microsoft.com/Forums/es-ES/544bfe56-b6e3-478e-993e-b549f9cce712/error-13101-key-part-nombre-for-type-recursos-is-not-valid-all-parts-of-the-key-must-be?forum=aspnetmvces

PS: I Have no problem with SQL Server views.

Regards Stéphane

[lajones]

@stephanebo21 - so, just to be clear, you are saying this Entity no longer shows up even if you open the .EDMX file in an XML editor and search for JOBS? It might be there just commented out. (If it is just commented out it would not show up at all when you open the file in the EDMX designer, but you might be able to solve the problem by manually editing the XML to declare your own key).

[divega]

@stephanebo21 I have updated this new bug report with the additional information from your comment at https://github.com/aspnet/EntityFramework6/issues/109#issuecomment-273514897. For future reference, it should always be possible to re-open an issue if you are the original poster. You should also be able to edit your own comments to add any missing information.

Regarding the issue itself, the two messages reveal that something has changed in the metadata we get about the view from the database between the two versions:

Warning 6002: The table/view 'myView' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

This warning usually occurs if we do not obtain any metadata about the primary keys of a database object. Then EF6 tools use built-in heuristics to cook up a key, just to get you going: it basically creates a key based on all the non-nullable values.

Error 13101: Key part ‘JOB_NAME’ for type ‘JOBS’ is not valid. All parts of the key must be non-nullable.

This error indicates that we actually succeeded at retrieving primary key information from the view, but the key columns just happen to form an invalid key because they are nullable.

This doesn't explain what changed, but it seems that somehow our ability to obtain primary key information from a view in Oracle has "improved" between the two versions. This could be a change we made in the EF tools (although we don't seem to have changes in this area) but it could also be a change in the design time components (aka the DDEX provider) from Oracle for Visual Studio 2015.

CC @alexkeh in case the last bit rings a bell.

Also, @stephanebo21 could you indicate exactly what is the last version of Visual Studio and the EF Tools in which you saw this working as you expected?

[stephanebo21]

Hi, Thanks for your answer. We are using Entity Framework 6.0.0.0 on Visual Studio 2012. I am pretty sure the problem is in the designer. If I only update Entity Framework 6.0.0 to 6.1.3, still on Visual Studio 2012 it does not work. If I only migrate to Visual Studio 2015, it does not work.

[lajones]

@stephanebo21 please let me know the answer to the question I asked about what you see when you open the file in the XML editor.

[stephanebo21]

@lajones about your question, our entity is no longer in the EDMX file in 6.1.3 version. In 6.0.0 version no problem (Jobs is system view from oracle):

[stephanebo21]

`

      <Property Name="JOB_NAME" Type="varchar2" MaxLength="128" Nullable="false" />
      <Property Name="STATE" Type="varchar2" MaxLength="15" />
      <Property Name="NEXT_RUN_DATE" Type="timestamp with time zone" />
      <Property Name="LAST_START_DATE" Type="timestamp with time zone" />
      <Property Name="REPEAT_INTERVAL" Type="varchar2" MaxLength="4000" />
      <Property Name="AUTO_DROP" Type="varchar2" MaxLength="5" />
      <Property Name="JOB_TYPE" Type="varchar2" MaxLength="16" />
      <Property Name="JOB_ACTION" Type="varchar2" MaxLength="4000" />
      <Property Name="JOB_CREATOR" Type="varchar2" MaxLength="128" />
      <Property Name="COMMENTS" Type="varchar2" MaxLength="240" />
    </EntityType>`

[ajcvickers]

@lajones Discussed with @divega and decided we should change the designer to ignore nullability of key columns and just used them anyway--possibly with a warning. In essence this is the same as the discussion we had recently about alternate keys on EF Core.

We should try to get this into the 6.2. beta.

[lajones]

@stephanebo21 My guess is that the 6.0.0 XML you posted above for the Entity JOBS is after your custom tool had run (because that EntityType does not look like it would have generated the 6002 error). Are you able to post what the EntityType looks like before your custom tool runs so I can try to mimic it as closely as possible?

[stephanebo21]

@lajones You're right. Before running our custom tool we get this error in the designer: Error 75: Key Part: 'JOB_NAME' for type JOBS is not valid. All parts of the key must be non nullable.

Our custom tool simply add the property 'Nullable' to 'False' cause it's missing.

[lajones]

@stephanebo21 OK. Thanks. I'll investigate.

[lajones]

We looked into several ways of fixing this. In the end we updated the designer so that if it encounters a nullable primary key column from the DB it will generate non-nullable properties (both S- and C-side) in the model. Reverse engineering issues a warning that it is doing that and also that any nulls that do exist in those columns may cause problems. We are planning to release this in the 6.2.0-beta1 release.

Fixed by #262.

[stephanebo21]

@lajones Thanks for your work and all your team. Great Product

Good job. Stéphane