New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error when when provider reports nullable keys #243

Closed
stephanebo21 opened this Issue Apr 11, 2017 · 12 comments

Comments

Projects
None yet
4 participants
@stephanebo21

stephanebo21 commented Apr 11, 2017

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

This comment has been minimized.

Member

lajones commented 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

This comment has been minimized.

Member

divega commented 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:

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

This comment has been minimized.

stephanebo21 commented 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

This comment has been minimized.

Member

lajones commented 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

This comment has been minimized.

stephanebo21 commented 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

This comment has been minimized.

stephanebo21 commented 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

This comment has been minimized.

Member

ajcvickers commented 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.

@ajcvickers ajcvickers added this to the 6.2.0 milestone Apr 14, 2017

@ajcvickers ajcvickers added the type-bug label Apr 14, 2017

@lajones

This comment has been minimized.

Member

lajones commented Apr 26, 2017

@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

This comment has been minimized.

stephanebo21 commented 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

This comment has been minimized.

Member

lajones commented Apr 27, 2017

@stephanebo21 OK. Thanks. I'll investigate.

@lajones

This comment has been minimized.

Member

lajones commented 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.

@lajones lajones closed this May 2, 2017

@lajones lajones added the closed-fixed label May 2, 2017

@stephanebo21

This comment has been minimized.

stephanebo21 commented May 3, 2017

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

Good job.
Stéphane

@divega divega changed the title from EF Tools 6.1.3 Update Model Issue to EF Tools Update Model Issue when provider reports nullable keys May 15, 2017

@divega divega changed the title from EF Tools Update Model Issue when provider reports nullable keys to EF Tools Update Model issue when provider reports nullable keys May 23, 2017

@divega divega modified the milestones: 6.2.0, 6.2.0 tooling Nov 3, 2017

@divega divega changed the title from EF Tools Update Model issue when provider reports nullable keys to Error when when provider reports nullable keys Apr 24, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment