Entity Framework Database First - Composite Foreign Keys

Question

I have a database that contains a couple of composite foreign keys. For example, here is the generation script for the foreign key:

ALTER TABLE [dbo].[WorkingRosters]  WITH NOCHECK ADD  CONSTRAINT
[FK_WorkingRoster_ShiftLeaveCode] FOREIGN KEY([OrganizationID], [ShiftLeaveCode])
REFERENCES [dbo].[ShiftLeaveCodes] ([OrganizationID], [Code])
GO

I am attempting to use Entity Framework 5 Database-First to generate a model from this database. However, the associations for the composite foreign keys are not being generated with all the tables and other simple foreign keys.

How can I either:

1. manually create these composite foreign keys in the xml behind the edmx (painful)
2. have entity framework properly generate these foreign keys so that I have have the mappings

Thanks!

Thoroughpaced

Specifically how are you generating your entities? Using the edmx editor? Are you using a custom code generation strategy? – Salutatory

Is OrganizationID and Code composite primary key of you ShiftLeaveCodes table? If not (for example if it is just unique index) it will not work. – Misspell

Entities are being generated from the database, using 'Update model from database' in the context menu. – Thoroughpaced

The OrganizationID and Code are just a unique index, not primary. Why is it that they need to be the primary index? – Thoroughpaced

I've updated the database so that the OrganizationID and Code columns make up a composite primary key. In doing so, I am still not able to get EF to auto-generate these associations and navigation properties between the tables. – Thoroughpaced

Answer 1

Entity Framework Database First doesn't behave with unique keys as you need. If you want them to be navigation properties, you should set them as foreign keys.

Now, you have to choice: 1) you just need NavPrs, 2) You also need the junction table as an entity.

1) To achieve this, just set those foreign keys as a composite primary key.

2) To achive this, leave them as foreign keys and declare an identity type ID column for your junction table and set it as the primary key.

This should work for you

Rubel

Answer 2

I had the same issue in EF6, where "Update Model From Database" in Visual Studio did not recognize a composite foreign key which referenced columns in another table that had a unique index on them but which weren't a composite primary key of the referenced table.

After making the referenced columns a composite PK in the referenced table and redoing the "Update Model From Database", the foreign key was recognized as navigation property.

I.e. making the referenced columns a PK solved the issue for me in EF6.

Trisect