We have a database in which one table contains records that can be child to several other tables. It has a "soft" foreign key consisting of the owner's Id and a table name. This (anti) pattern is know as "polymorphic associations". We know it's not the best database design ever and we will change it in due time, but not in the near future. Let me show a simplified example:
Both Event
, Person
, and Product
have records in Comment. As you see, there are no hard FK constraints.
In Entity Framework it is possible to support this model by sublassing Comment
into EventComment
etc. and let Event
have an EventComments
collection, etc.:
The subclasses and the associations are added manually after generating the basic model from the database. OwnerCode
is the discriminator in this TPH model. Please note that Event
, Person
, and Product
are completely different entities. It does not make sense to have a common base class for them.
This is database-first. Our real-life model works like this, no problem.
OK. Now we want to move to code-first. So I started out reverse-engineering the database into a code first model (EF Power Tools) and went on creating the subclasses and mapping the associations and inheritance. Tried to connect to the model in Linqpad. That's when the trouble started.
When trying to execute a query with this model it throws an InvalidOperationExeception
The foreign key component 'OwnerId' is not a declared property on type 'EventComment'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.
This happens when I have bidirectional associations and OwnerId
is mapped as a property in Comment
. The mapping in my EventMap
class (EntityTypeConfiguration<Event>
) looks like this:
this.HasMany(x => x.Comments).WithRequired(c => c.Event)
.HasForeignKey(c => c.OwnerId);
So I tried to map the association without OwnerId
in the model:
this.HasMany(x => x.Comments).WithRequired().Map(m => m.MapKey("OwnerId"));
This throws a MetaDataException
Schema specified is not valid. Errors: (10,6) : error 0019: Each property name in a type must be unique. Property name 'OwnerId' was already defined. (11,6) : error 0019: Each property name in a type must be unique. Property name 'OwnerId' was already defined.
If I remove two of the three entity-comment associations it is OK, but of course that's not a cure.
Some further details:
- It is possible to create a working DbContext model ("code second") from the edmx by adding a DbContext generator item. (this would be a work-around for the time being).
- When I export the working code-first model (with one association) to edmx (
EdmxWriter
) the association appears to be in the storage model, whereas in the original edmx they are part of the conceptual model.
So, how can I create this model code-first? I think the key is how to instruct code-first to map the associations in the conceptual model, not the storage model.