Composite DB keys with Entity Framework 4.0

Question

The re-design for a large database at our company makes extensive use of composite primary keys on the database.

Forgetting performance impacts, will this cause any difficulties when working with this db in Entity Framework 4.0? The database structure is unlikely to change and I'm not looking for "philosophical" debate but what are the practical impacts?

According to Jeremy Miller, "Composite key make any kind of Object/Relational mapping and persistance in general harder." but he doesn't really say why. Is this relavent to how Entity Framework 4.0 handles keys?

Answer 1

I have had problems with EF4 and composite keys. It doesn't support columns being used as components in more than one key in a join table.

See my previous question Mapping composite foreign keys in a many-many relationship in Entity Framework for more details. The nuts of it is that when you have a join table (describing a many-many relationship) where both of the relationships use a common key, you'll get an error like

Error 3021: Problem in mapping fragments...: Each of the following columns in table PageView is mapped to multiple conceptual side properties: PageView.Version is mapped to (PageView_Association.View.Version, PageView_Association.Page.Version)

The only way around it was to duplicate the column which defeats the purpose of having it there at all.

Good luck!

Answer 2

No, EF4 supports composite keys just fine.

The problem is a table with a surrogate key and composite keys. You can only set a single key on each model; that key can have multiple fields, but you can only have one from the designer standpoint. Not sure about manually editing xml or code only mapping.

You can set a field as an Identity and not a key if you need a composite and surrogate key on the same table. The Identity ( Id ) field won't be used by the ObjectContext or ObjectStateTracker but will increment and be queryable just fine though.