Using Foreign Key (FK) as Discriminator for Table-Per-Hierarchy (TPH)

Question

[Q:] Is it possible to use a FK as a discriminator in EF and what workarounds have people come up with?

The scenario

EF Objects

public class List {     public int Id { get; set; }     public string Name { get; set; }     public ICollection<ListItem> Items { get; set; } }  public abstract class ListItem {     public int Id { get; set; }     public List List { get; set; }     public string Text { get; set; } } 

Database

The existing DB that is not used by EF exclusively (i.e. cannot be changed) has fields as follows:

List     Id         int not null      (identity)     Name       varchar  ListItem     Id         int not null      (identity)     ListId     int not null      (FK to List.Id)     Text       varchar 

The desired outcome

I want the Id of List to be the discriminator for the ListItem. i.e. For each entry in list a separate class descended from ListItem is implemented.

For example for List [Id:1]

public class PersonListItem : ListItem {     public int PersonId { get; set; }     public Person Person { get; set; } }  public class ListItemConfiguration : EntityTypeConfiguration<ListItem> {     Map<PersonListItem>(m => m.Requires("ListId").HasValue(1)); } 

Under the above scenario saving changes results in a SQL exception because EF tries to insert into List_Id when creating a new ListItem instance. List_Id is not a field, and I can't map ListId as a property on ListItem as wouldn't be able to be used as a discriminator.

My solution so far...

This Q&A explains why they made the decision not to allow FK as discriminator.

My workaround so far is to add another field to the db to use as a discriminator which is then set to the same value as ListId using an insert trigger (to handle non-EF inserts) and then add the ListId navigational property to ListItem entity.

Does anyone have any suggestions / alternatives?

Answer 1

Most easily the solution lies in introducing an enum that is used as discriminator to represent the type. The post you mentioned clearly describes a FK cannot be used so you need to refactor that to something that can be applied. It requires some maintenance but since you also have to know the types that derive from your listitem, I don't see this as a major problem. Other option you have would be to move away from TPH and use TablePerType. That way you don't actualy have to introduce an enum, but you will incurr a table for each derived listitem.