1

Closed

Designer: Can not reverse engineer EDMX when FKs in different order than PKs (composite keys)

description

Hello there guys,
I've been using EF5 for a long long time. Now was time to upgrade to EF6.
I Installed VS2013 and tried to generate the model from the same database i was using in EF5.
It gives like 100 errors in the foreignkeys.

Error 13101: The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'USOLICITACAOCODSOL' on entity 'DDOCUMENTO' does not match the type of property 'CODGRUPOEMPRESA' on entity 'USOLICITACAO' in the referential constraint 'DDOCUMENTO_USOLICITACAO_FK'. c:\users\jpereira\documents\visual studio 2012\Projects\WpfApplication18\WpfApplication18\Nubes.edmx

I posted the question here:

http://stackoverflow.com/questions/19300239/entity-framework-6-error-13101-database-first

If you recieved some complains abount the same problem plz let me know.
If the error is mine plz let me know also.

Thanks and keep the great work

file attachments

Closed Nov 7, 2013 at 12:56 AM by lukew
Verified that this is fixed in the runtime. Presently, the way to get a build of the designer that has this fix is to checkout the EF repo and run your own local build of the designer. Instructions are available here: https://entityframework.codeplex.com/wikipage?title=Getting%20and%20Building%20EF%20Tools

comments

moozzyk wrote Oct 14, 2013 at 4:17 PM

Would you mind attaching the edmx file?

skiptpawa wrote Oct 14, 2013 at 10:01 PM

I can't send you the original edmx created sucessufly from ef5 because of my NDA.
I will talk to my boss and submit.
EF6 edmx is only giving errors in that kind of foreign keys for no reason.. E-R is correct on DB.

I will post here tomorrow the EF5 edmx if my boss allow it :P

thanks 4 your time.

moozzyk wrote Oct 14, 2013 at 11:11 PM

@skiptpawa - alternatively you can provide me with a description/DDL of how to setup the database. I assume it should possible to repro with just two tables and some kind of relation between them. It seems that the relationship is the key to repro this issue. Once I am able to setup the database I should be able to repro and investigate.
Two more questions:
  • are the errors you are seeing errors or warnings?
  • if you look at the EF5 edmx as xml - do you see similar errors in comments?

skiptpawa wrote Oct 15, 2013 at 8:24 AM

Hello,

The error i'm seeing are erros, as you can see in the atachment file.
In the EF5 everything looks rather fine... no unusual coments there.

I'll reproduce an example database sou you can check it out for yourself.

later on i'll put here the sample database, i'm kin'da super busy right now.

Thanks 4 your time

moozzyk wrote Oct 17, 2013 at 4:00 PM

This happens for composite foreign keys if order of foreign key columns is different from the order of key columns in the principal table. Sample tables that could be used to repro this:

CREATE TABLE [dbo].[Table1] (
[Id]       INT           NOT NULL,
[IdString] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([IdString] ASC, [Id] ASC)
);

CREATE TABLE [dbo].[Table3]
(
[TableId] INT NOT NULL PRIMARY KEY,
[IdString] NVARCHAR (50) NULL,
[Id]       INT           NULL, 
CONSTRAINT [FK_Table3_ToTable] FOREIGN KEY (IdString, Id) REFERENCES [Table1](IdString, Id),
)

moozzyk wrote Oct 17, 2013 at 4:40 PM

Attaching the edmx file we create in VS2012 for this case - looks like the bug is in the designer where, when building the model, we don't set the foreign keys for principal and dependent entities in the same order in the ReferentialConstraint element.

skiptpawa wrote Oct 17, 2013 at 8:09 PM

Hello there!

Omg, I can't believe i didn't noticed that... That is exactly my problem!
What do you suggest? Database fix or wait for the bug fix?

Have you guys have an ETA for the final release?

Thanks a lot!
Keep the good work!

moozzyk wrote Oct 17, 2013 at 9:06 PM

@skiptpawa - I think that changing the order of columns in the table would require dropping and recreating the table. I don't know how feasible it is for you to do something like that if the table contains data. As for the final release - VS 2013 shipped actually today and unfortunately has this bug.

skiptpawa wrote Oct 18, 2013 at 9:50 AM

Hum... Indeed remaking the database can be a huge pain... i found almost 100 problems like those.
Any ideas for a date to update and fix this?

moozzyk wrote Oct 18, 2013 at 11:14 PM

The bug is in the runtime in validation where we use the order of dependent properties defined on the constraint to look up key properties of the principal type without routing them through the principal properties defined on the constraint to get them in the right order. Therefore in cases where types happen to match we won't raise an error and will create a correct model.

@skiptpawa - I know that this is not a great workaround but you could use VS2012 (probably express edition should be enough) or EdmGen to create the model and attach it to your project.

moozzyk wrote Oct 21, 2013 at 9:51 PM

Fixed with changeset 6ded4b5
DisorderlyConduct - fixing bug https://entityframework.codeplex.com/workitem/1735: Designer: Can not reverse engineer EDMX when FKs are in different order than PKs (composite keys)

When validating the model we assumed that the order of primary key columns of the principal entity is the same as the order of the foreign key columns of the dependent entity. This is true for models built using Code First but does not have to be true for database first models where the order of columns is already defined in the database. Fixing validation to look the PK properties up by name based on the values specified in ReferentialConstraint for the prinicpal entity.

ajcvickers wrote Oct 30, 2013 at 9:41 PM

This has been merged into the 6.0.2 branch and is now ready to verify in that branch.

skiptpawa wrote Nov 13, 2013 at 10:34 PM

Hello there again,

I've tested today in a dll version of EF6 6.0.20911 and the problem continues... can you guys plz recheck?

moozzyk wrote Nov 14, 2013 at 2:59 AM

The fix is in the runtime assembly but it's not about what is being added to your project - it's about the EntityFramework.dll that is internally used by the designer. The easiest (and probably most hacky) way to get the designer fixed is to overwrite EntityFramework.dll in Program Files\Microsoft Visual Studio 12.0\Common7\IDE with one that has the fix. Make sure to use the one from nightly build otherwise it will not be signed and the things will break.

skiptpawa wrote Nov 16, 2013 at 11:32 PM

Hello there,

I tried what you suggested. It does not work. When replacing the original dlls for the ones of nightly build there's an output error.
Unable to generate the model because of the following exception: 'System.TypeLoadException: Could not load type 'System.Data.Entity.Core.Mapping.StorageEntityContainerMapping' from assembly 'EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.DbDatabaseMappingBuilder.Build(SimpleMappingContext mappingContext)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(EdmxHelper edmxHelper)'.
Loading metadata from the database took 00:00:01.2282643.
Generating the model took 00:00:16.0509197.
Added the connection string to the App.Config file.
Writing the .edmx file took 00:00:00.0009139.
I think i'll wait for a stable version...

Thanks annyway!

moozzyk wrote Nov 17, 2013 at 1:10 AM

Are you sure the version of the assembly you copied was 6.0.0.0 and not 6.1.0.0?

skiptpawa wrote Dec 17, 2013 at 9:24 AM

Hello there,
my project stopped database developing for a while, so, i'm sorry I've forgotten to answer the question. I tried today with the latest version of the EF6 and the problem still persists.
When the keys are in different order from the the others in the foreign key table, it gives error, plz try and fix this, my database is k'inda big, i cant reorder the fields, it is almost impossible.

moozzyk wrote Dec 17, 2013 at 4:15 PM

@skiptpawa - You need to install the latest version of EFTools - you can find the link to the msi in this blog post: http://blogs.msdn.com/b/adonet/archive/2013/12/13/ef6-0-2-rtm-available.aspx

skiptpawa wrote Dec 17, 2013 at 11:36 PM

hello there,
and first of all thanks. _That did the trick- Problem solved!_. Thanks for the nice work.
I hope some day i could make part of a nice team like you guys!
Keep the good work.

Cya.

pankyog wrote Mar 7 at 1:56 PM

Thanks a lot for posting this solution. Installing EFTools resolved the issue.

I started having this problem after migrating from .Net4/EF4 to .Net4.5.1/EF6.0.2.