Repository navigation
Error when when provider reports nullable keys #243
Description
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
Activity
lajones commentedon Apr 11, 2017
@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 commentedon Apr 11, 2017
@stephanebo21 I have updated this new bug report with the additional information from your comment at #109 (comment). 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:
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.
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 commentedon Apr 12, 2017
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 commentedon Apr 12, 2017
@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 commentedon Apr 13, 2017
@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 commentedon Apr 13, 2017
<EntityType Name="JOBS"> <Key> <PropertyRef Name="JOB_NAME" /> </Key> <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 commentedon Apr 14, 2017
@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 commentedon Apr 26, 2017
@stephanebo21 My guess is that the 6.0.0 XML you posted above for the Entity
JOBSis after your custom tool had run (because that EntityType does not look like it would have generated the6002error). 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 commentedon Apr 27, 2017
@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 commentedon Apr 27, 2017
@stephanebo21 OK. Thanks. I'll investigate.
lajones commentedon May 2, 2017
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 commentedon May 3, 2017
@lajones Thanks for your work and all your team.
Great Product
Good job.
Stéphane
[-]EF Tools 6.1.3 Update Model Issue[/-][+]EF Tools Update Model Issue when provider reports nullable keys[/+][-]EF Tools Update Model Issue when provider reports nullable keys[/-][+]EF Tools Update Model issue when provider reports nullable keys[/+][-]EF Tools Update Model issue when provider reports nullable keys[/-][+]Error when when provider reports nullable keys[/+]