EF6 - TPH foreign key mapping in derived classes using base class property

Question

I am using Entity Framework 6.0.2 with an existing database in which tags are stored in a single table that looks like this:

• Id: int, primary key
• TagType: string, determine the type of tag, either "usertag" or "movietag"
• ItemId: int, contains the Id of the item to which is referred (either a User Id or a Movie Id)

The following classes describe this situation:

public class User
{
    public int Id { get; set; }
}

public class Movie
{
    public int Id { get; set; }
}

public abstract class Tag
{
    public int Id { get; set; }
    public int ItemId { get; set; }
}

public class UserTag : Tag
{
    public virtual User User { get; set; }
}

public class MovieTag : Tag
{
    public virtual Movie Movie { get; set; }
}

As you can see my derived classes have navigation properties, which are backed by the value of the ItemId property in the base class. My mapping is as follows:

public class Context : DbContext
{
    public DbSet<Tag> Tags { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tag>()
            .Map<UserTag>(m => m.Requires("TagType").HasValue("usertag"))
            .Map<MovieTag>(m => m.Requires("TagType").HasValue("movietag"));

        modelBuilder.Entity<UserTag>()
            .HasRequired(m => m.User).WithMany().HasForeignKey(m => m.ItemId);

        modelBuilder.Entity<MovieTag>()
            .HasRequired(m => m.Movie).WithMany().HasForeignKey(m => m.ItemId);
    }
}

Now when I try to use this mapping using the following code, I get an exception:

using System.Data.Entity;

class Program
{
    static void Main()
    {
        using (var db = new Context())
        {
            db.Database.Delete();
            db.Database.Initialize(false);
        }
    }
}

The exception that is thrown is:

Unhandled Exception: System.InvalidOperationException: The foreign key component 'ItemId' is not a declared property on type 'UserTag'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property

Yes the ItemId property is not declared on the type UserTag, but it is inherited from the base Tag class. To me it seems that this mapping should be possible. Is this a bug or a restriction in Entity Framework 6?

Answer 1

It is a restriction. EF is quite tightly bound to the way how relational database works. What you are trying to do in terms of the database is to put two foreign key constraints on single ItemId column. The foreign constraint in database is not conditional so the record will always use both constraints no matter of the tag type. That is not what you want because such definition will always require both user and movie with specific Id to exist for every single tag.

Think about it in different way. If it works the way how you are trying to define it there would be no reason why to have User and Movie navigation properties in child entities - it would be enough to have single navigation property in parent. The fact that you have to define them in child entities because they are different for each of them also means you need to have two different foreign keys.

You need to have separate UserId and MovieId in their specific tags.