Can I mix Table per Hierarchy and Table per Type in Entity Framework?

Question

Say I have 2 tables: Message and SuperMessage

and 3 entities: Message (base (not abstract)), Comment (inherits from Message) and SuperMessage (inherits from Message)

Message has a non-nullable MessageType field which is used as a discriminator.

• MessageType = 1 means it is a Message
• MessageType = 2 means it is a Comment
• MessageType = 3 AND and join to the SuperMessage means it is a SuperMessage

The problem is that I cannot specify a condition on MessageType of the SuperMessage's Mapping details section because it cannot see the MessageType field and I cannot ignore it.

How can I make these work alongside eachother?

UPDATE Build Error:

Error 3014: Problem in mapping fragments:The foreign key 'Foreign key constraint 'FK_SuperMessage_inherits_Message' from table SuperMessage (ID) to table Message (MessageId):' is not being enforced in the model. An Association or inheritance relationship needs to be created to enforce this constraint.

Supinate

If you simply ignore MessageType for SuperMessage and allow the TPT inheritance to kick in, does it work or do you get an error? – Declinate

@Declinate yes I have tried it and I get the error (which I have just added above) – Supinate

Playing around with a simple example myself. I'll let you know what I find. – Declinate

You should be able to see MessageType if you add the Message table in the mapping window. Have you done that? That said, I don't know the answer, because I've never tried this. – Two

I don't think this woud be possible, inheritance strategies cannot be mixed within one inheritance hierarchy. Each inherited tree of entity types must stick to one inheritance strategy. Unfortunately this not even possible with Code-First. – Kissiah

With "database-first" or "database+model at the same time, mappings second" and entity-framework-4 this does work. How about testing my solution for yourself. – Fendley

Answer 1

I reproduced this and got the same error as you. As far as I can tell, it appears that combining these two types of inheritance for a single base table is just not possible. I'd love to be proved wrong though. ;-)

Declinate

Heard from several people that this particular scenario is not possible. So I think you are right - unfortunately ;) – Supinate

This answer seems to suggest otherwise :-) https://mcmap.net/q/1472122/-entity-framework-mix-table-per-type-and-table-per-hierarchy – Decemvirate

Answer 2

Have you tried adding an intermediate abstract entity type, i.e.:

abstract     MessageBase      --> Message table
non-abstract Message          --> Message table when MessageType == 1
non-abstract Comment          --> Message table when MessageType == 2
abstract     SuperMessageBase --> Message table when MessageType == 3
non-abstract SuperMessage     --> SuperMessage table

Also check out a similar scenario I solved: EF: Can I mix TPH and TPT when abstract base and a few concrete types are in TPH-table and other types have their own table?

Fendley