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.

Gaiser

Did you ever get the model to work with Code-First? Interesting that it works for Db-First/EDMX. I had a question about a similar model and the final word from CodePlex was basically "not supported" and "fundamental EF limitation" (https://mcmap.net/q/668640/-inheritance-and-composite-foreign-keys-one-part-of-the-key-in-base-class-the-other-part-in-derived-class). But if your model works with EDMX it actually seems to be a Code-First limitation, not a general EF limitation. – Caskey

Yes, the database-first model works (thank god). The real model is even a b it more complex because it has composite inheritance discriminators. No problem. Code first is the problem. Did you ever try your model db-first? – Gaiser

No, never tried it. We know that Code-First doesn't support all edmx features but I really didn't expect a difference in this situation. I have btw just asked for confirmation in that old issue on CodePlex (entityframework.codeplex.com/workitem/865, last comment on the page). Not sure if I can expect an answer in a closed item. – Caskey

Maybe they will, would be interesting. I think the key is in the difference between storage model and the conceptual model but I can't fathom why this would matter so much. Anyhow, all this urges me to give some more prio to sanitizing our model (turn it into a more decent 3NF model) because this apparently being a very fundamental limitation may cause our db-first model to break in future versions of EF as well. – Gaiser

I think the problem is that OwnerType should be defined only in the subclasses (should be removed from the Comment class). I could make it work here #38275835 – Wiedmann

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.

Huntingdon

I've finally come to the conclusion that I have to go this path too. The point is that the edmx model allows for changes to be made in the conceptual model while code-first doesn't offer any tooling to make this distinction. With code-first all mapping configurations equally enter the store model and the conceptual model. I'm afraid the EF team is not interested in developing tools to shape the conceptual model only (apart from the store model) in the code-first API. – Gaiser

Good luck in future, MS pulled the plug on edmx support... theregister.co.uk/2014/10/23/… – Unionist