Entity Framework 6.1 Code First Cascading Delete with TPH for one-to-one relationship on a derived type

Question

I am trying to create 2 one-to-one relationships between derived classes of a common base and an unrelated class, so that when I delete the parent row the child rows in the database get deleted. I have been brooding over this issue for some days now and I have tried every (for me) imaginable combination of relationships in the fluent api. So far without any satisfying result. This is my setup:

public class OtherType
{
 public int ID {get; set;}

 public int? DerivedTypeAID {get; set;}
 public virtual DerivedTypeA DerivedType {get; set;}

 public int? DerivedTypeBID {get; set;}
 public virtual DerivedTypeB DerivedType {get; set;}
}


public abstract class BaseType
{
  public int ID {get; set;}
  public string ClassName {get; set;}
  public virtual OtherType {get; set;}
}


public class DerivedTypeA : BaseType
{
 public string DerivedProperty {get; set;}
}

public class DerivedTypeB : BaseType
{
 public string DerivedProperty {get; set;}
}

public class MyContext : DbContext
{
 public MyContext()
        : base("name=MyContext")
    {
    }

    public DbSet<OtherType> OtherTypes { get; set; }
    public DbSet<BaseType> BaseTypes { get; set; }



    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var m = modelBuilder;

        m.Entity<OtherType>().HasOptional(_ => _.DerivedTypeA)
            .WithMany().HasForeignKey(_ => _.DerivedTypeAID).WillCascadeOnDelete(true);
        m.Entity<OtherType>().HasOptional(_ => _.DerivedTypeB)
            .WithMany().HasForeignKey(_ => _.DerivedTypeBID).WillCascadeOnDelete(true);

        m.Entity<DerivedTypeA>().HasRequired(_ => _.OtherType).WithMany().HasForeignKey(_ => _.ID).WillCascadeOnDelete(true);
        m.Entity<DerivedTypeB>().HasRequired(_ => _.OtherType).WithMany().HasForeignKey(_ => _.ID).WillCascadeOnDelete(true);
    }
}

This fully works except for the cascading delete part. EF creates foreign keys on the parent table OtherType for each referenced DerivedType with DELETE CASCADE. On the child table (TPH => BaseTypes) it creates one foreign key with DELETE RESTRICT. I would expect that the last two lines in my code would create the desired foreign keys with DELETE CASCADE. Since this is the closest I have come to make it work at all (and dont have any of my previous attempts saved) I will leave it at that and hope I have explained everything well enough so that somebody can point me towards the right direction. Thanks!

UPDATE #1

I have now shifted towards using EF TPC hoping to be able to resolve my issue that way. It did not. So here is to another go with a bit more of details and an ERD explaining my issue anew hoping somebody can help me out because I am reaching that certain state where you start laughing hysterically while pulling out your hair. That would be my EF Model:

Thats how I went about creating that with Code First:

public abstract class BaseType
{
    public int BaseTypeId { get; set; }
}

public class DerivedTypeA : BaseType
{
    public virtual OtherType OtherType { get; set; }
}

public class DerivedTypeB : BaseType
{
    public virtual OtherType OtherType { get; set; }
}

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

    public virtual DerivedTypeA DerivedTypeA { get; set; }

    public virtual DerivedTypeB DerivedTypeB { get; set; }
}

public class TPCModel : DbContext
{

    public TPCModel()
        : base("name=TPCModel")
    {

    }

    public DbSet<BaseType> BaseTypes { get; set; }
    public DbSet<OtherType> OtherTypes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var m = modelBuilder;

        m.Entity<BaseType>().Property(_ => _.BaseTypeId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        m.Entity<DerivedTypeA>().Map(x =>
            {
                x.MapInheritedProperties();
                x.ToTable("DerivedTypeA");
            });

        m.Entity<DerivedTypeB>().Map(x =>
            {
                x.MapInheritedProperties();
                x.ToTable("DerivedTypeB");
            });

        m.Entity<DerivedTypeA>().HasRequired(_ => _.OtherType)
            .WithOptional(_ => _.DerivedTypeA).WillCascadeOnDelete(true);

        m.Entity<DerivedTypeB>().HasRequired(_ => _.OtherType)
            .WithOptional(_ => _.DerivedTypeB).WillCascadeOnDelete(true);

    }


}

From that code this database schema was created:

Both DerivedTypes-Tables have their primary key that also is a foreign key referencing the BaseTypeId-Column on BaseTypes.

For creating that Code First I followed directions from here: - http://weblogs.asp.net/manavi/archive/2011/01/03/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines.aspx
- http://msdn.microsoft.com/en-us/data/jj591620#RequiredToOptional

I try to commit records to the database using this code:

using (var ctx = new TPCModel())
{
    Database.SetInitializer(new DropCreateDatabaseAlways<TPCModel>());
     ctx.Database.Initialize(true);

    ctx.OtherTypes.Add(new OtherType()
    {
        DerivedTypeA = new DerivedTypeA() { BaseTypeId=1 },
        DerivedTypeB = new DerivedTypeB() { BaseTypeId=2 }
    });

    ctx.SaveChanges(); // Exception throws here
}

EF throws this Exception Message:

Additional information: The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: Saving or accepting changes failed because more than one entity of type 'EF6.CodeFirst.TPC.Model.SecondConcreteType' have the same primary key value. Ensure that explicitly set primary key values are unique. Ensure that database-generated primary keys are configured correctly in the database and in the Entity Framework model. Use the Entity Designer for Database First/Model First configuration. Use the 'HasDatabaseGeneratedOption" fluent API or 'DatabaseGeneratedAttribute' for Code First configuration.

Now as for how the solution to this unfortunate situation should look like I have a pretty good notion. When using Table-per-Class-Strategy (TPC) one has to handle generation of primary keys him/herself so EF does not get confused when you have two exact same primary keys for what are completely unrelated tables at the database level but which share a common base class at the EF level. The proposed way in the first URL I linked unfortunately does not alleviate this issue since the foreign keys on my DerivedType-Objects will end up being the same here no matter what since they reference the primary key on the OtherTypes-Table which obviously will be the same for a distinct record in that table. Thats the issue.

The solution I assume would involve two additional columns in the OtherTypes table each of those being the target of one of the foreign keys on the DerivedTypes tables. But I absolutely do not know how to implement that in EF. Whatever I tried so far usually ended up with some exception about how you can only have hierarchy independent associations for the most derived types (which in fact are DerivedTypeA and DerivedTypeB) or other validation exceptions complaining about the multiplicity having to be many on one of the relationship ends.

I must point out that the kind of model I created is exactly what I need since I am working within a larger model that uses a recursive marshalling system and AutoMapper to map between the two layers. That said I would like to ask you to find a solution for the proposed model and not come up with a different kind of model or workarounds where I deviate from the zero..one to one mapping.

UPDATE #2

I was bloody fed up with EF6's trouble to create associations from derived classes within an inheritance hierarchy to other unrelated-in-type classes so I went ahead and completely rewrote my data model to exclude any sort of hierarchy (no TPH/TPT/TPC). I was done in about 5 hours complete with all mappings using fluent api and seeding for the whole span of tables. Cascading deletes work exactly as I set them up from everywhere within my model. Still I would not forgo to hear somebody's solution to this issue but I would live on still in case this won't get resolved.

Answer 1

I think your issue is related with the type of the navigation properties in OtherType class.

I don't think you can have strongly typed properties in this scenario.

This has a root cause in the cyclic cascade delete your model implies.

As a secondary workaround, since you already found one, please try the model below that I used in a similar scenario: (with Person = OtherType, PersonDetail = BaseType, HumanBeing = DerivedTypeA , Corporation = DerivedTypeB)

public class Person
{
    public Guid Id { get; set; }

    public string Designation { get; set; }

    public virtual PersonDetail Detail { get; set; }

    public virtual Person AggregatedOn { get; set; }

    protected ICollection<Person> aggregationOf;
    public virtual ICollection<Person> AggregationOf
    {
        get { return aggregationOf ?? (aggregationOf = new HashSet<Person>()); }
        set { aggregationOf = value; }
    }
}

public abstract class PersonDetail
{
    public Guid Id { get; set; }

    public virtual Person Personne { get; set; }
}

public class Corporation : PersonDetail
{
    public string Label { get; set; }
}

public class HumanBeing : PersonDetail
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class ReferentialContext : DbContext
{
    public ReferentialContext()
        : base("ReferentialContext")
    {
    }

    public ReferentialContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
    }

    public DbSet<Person> Personnes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Types().Configure(t => t.ToTable(t.ClrType.Name.ToUpper()));
        modelBuilder.Properties().Configure(p => p.HasColumnName(p.ClrPropertyInfo.Name.ToUpper()));

        modelBuilder.Configurations.Add(new PersonConfiguration());
        modelBuilder.Configurations.Add(new PersonDetailConfiguration());

    }
}

class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        this.HasMany(p => p.AggregationOf)
            .WithOptional(p => p.AggregatedOn);
    }
}

class PersonDetailConfiguration : EntityTypeConfiguration<PersonDetail>
{
    public PersonDetailConfiguration()
    {
        this.Property(p => p.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        this.HasRequired(p => p.Personne)
            .WithRequiredDependent(p => p.Detail);
    }
}

The only difference I see between your model and mine is that I don't "care" about the type of the actual property in my Person (OtherType), as I can always use the OfType linq function to get check if the Persons in my properties are Humans (DerivedTypeA) or Corporations (DerivedTypeB).