Entity Framework won't resolve PK-FK relationship with composite primary key?

Question

I'm trying to set up an EDM on an existing SQL Server infrastructure, and came across a problem.

The EDM will not resolve a PK-FK relationship to a composite foreign key.

My DB table structure looks something like this (names changed to protect the innocent):

• I have a PERSONS table containing an INT column called PerID (PK)
• I have an OFFICE table containing an INT column called OffID (PK)
• I am tying these tables together using a table called OFFICEPERSONS, creating a many-to-many relationship between PERSONS and OFFICE. This table has two INT columns, PerID and OffID, which together form a composite primary key.
• I have a table called OFFICELOCATION that contains two INT columns, LocID and OffID. These two columns comprise a composite primary key. Additionally, OffID is also a FK to the OFFICE table.
• Finally, I have a table called OFFICEPERSONSLOCATION. This table has three INT columns: PerID, OffID, and LocID. All three columns comprise a composite primary key. LocID and OffID provide a FK relationship to OFFICELOCATION, and OffID and PerID provide a FK relationship to OFFICEPERSONS.

With me so far? Hopefully, I haven't lost you yet. When all is said and done, my structure looks like this:

This structure works great in SQL Server. In EDM? Not so much. It will NOT allow me to construct the relation between OFFICEPERSONSLOCATION and OFFICEPERSONS. I get the following error:

Error 6037: Foreign key constraint 'FK_OFFICEPERSONSLOCATION_OFFICEPERSONS' has been omitted from the storage model. Column 'OffID' of table 'Model.Store.OFFICEPERSONSLOCATION' is a foreign key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible.

Huh? Data inconsistency?!? How?

How do I get my entity framework to recognize this?

Answer 1

I agree that it is the entity framework's problem, and the problem is stupid. Even if you have the UPDATE CASCADE to "no action", it is not like you could create an inconsistency, but no, it claims that you can somehow.

In any case, in this situation, if you are willing to use surrogate keys instead of composite keys, you can get around this, because the only place to change the ID reference is in the main table.

In this case, OffID could be "inconsistent", but by using ID's in the OFFICEPERSONS and OFFICELOCATIONS tables (and therefore reference in OFFICEPERSONSLOCATION), you are forced to have the OffId managed in its primary table.