EF 4.3 Code First: Table per Type (TPT) with Composite Primary Key and Foreign Key

Question

So I'm trying to use Code First with Fluent to map a base class with one derived type where the tables schema is a Table-per-Type arrangement. Also, the derived type has a many-to-one relationship with another type that also has a composite foreign key. (The keys on these tables is unchangeable and the names match up exactly.)

Here is an example of what I'm trying to achieve in CSharp:

public class BaseType
{
    public int Id;
    public int TenantId;
    public int Name;
}

public class DerivedType : BaseType
{
    public int Active;
    public int OtherTypeId;
    public OtherType NavigationProperty; 
}

Here is the configuration for this in the configuration classes:

public BaseTypeConfiguration()
{
     ToTable("BaseTypes", "dbo");

     HasKey(f => new { f.Id, f.TenantId});

     Property(f => f.Id)
         .HasColumnName("BaseTypeId")
         .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}

public DerivedTypeConfiguration()
{
    ToTable("DerivedTypes", "dbo");

    //OtherType has many DerivedTypes
    HasRequired(dt=> dt.OtherTypeNavigation)
        .WithMany(ot=> ot.DerivedTypes)
        .HasForeignKey(dt=> new { dt.OtherTypeId, dt.TenantId});
}

From what I can tell my mapping is set up correctly (as in, I followed many tutorials and examples that had this exact situation but with a single column identifier)

When I try to query these entities the exception I get is: The foreign key component 'TenantId' is not a declared property on type 'DerivedType'.

And when I try to explicitly declare these properties on the type using the new keyword I get an exception saying that duplicate properties exist.

Answer Response from EF Team

This is part of a more fundamental limitation where EF doesn't support having a property defined in a base type and then using it as a foreign key in a derived type. Unfortunately this is a limitation that would be very hard to remove from our code base. Given that we haven't seen a lot of requests for it, it's not something we are planning to address at this stage so we are closing this issue.

Lisabethlisan

I assume those fields are properties in real life? – Technicolor

I believe it's an unsupported mapping scenario (at least I never found a solution): #10962190 – Cabernet

I definitely think it's not possible at the moment but 'unsupported'? This is a perfectly valid configuration, I've never found anything in the documentation that would indicate that this shouldn't work so to me it seems more like a bug. – Lisabethlisan

@Lisabethlisan You have found the exact same problem that I have found today. This is my question and this is the answer that has worked well so far though is not what I expected. I'm still on the quest of something better. – Besides

EF Issue made, hopefully they will look into it. entityframework.codeplex.com/workitem/865 – Lisabethlisan

Answer 1

I think this is what you are looking for:

[Table("BaseType")]
public class BaseType
{
    [Key, DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
    public int Id {get;set;}
    [Key]
    public int TenantId { get; set; }
    public int Name { get; set; }
}

[Table("Derived1")]
public class DerivedType : BaseType
{
    public int Active { get; set; }
    public int OtherTypeId { get; set; }
    public virtual OtherType NavigationProperty {get;set;}
}

[ComplexType]
public class OtherType
{
    public string MyProperty { get; set; }

}


public class EFCodeFirstContext : DbContext
{
    public DbSet<BaseType> BaseTypes { get; set; }
    public DbSet<DerivedType> DerivedTypes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BaseType>().HasKey(p => new { p.Id, p.TenantId });
        base.OnModelCreating(modelBuilder);
    }
}

Breeches

The 'Other Type' in my case cannot be a complex type. It's in a different table, is an entity itself and has relationships of its own. – Lisabethlisan

Answer 2

This is unsupported as of right now, according to their support. I had created a case for a similar stack question here and the author updated with the case results.

https://mcmap.net/q/668640/-inheritance-and-composite-foreign-keys-one-part-of-the-key-in-base-class-the-other-part-in-derived-class

Lisabethlisan