c# - Modelling polymorphic associations database-first vs code-first

Question

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.

Answer 1

I personally stick with Database first when using EF on any schema that is this level of complexity. I have had issues with complex schemas in regards to code first. Maybe the newer versions are a little better, but worrying how to try and code complex relationships seems less straight forward then allowing the engine to generate it for you. Also when a relationship gets this complex I tend to avoid trying to generate it with EF and try and use stored procedures for easier troubleshooting of performance bottlenecks that can arise.