Multiple child compositions to the same type w/ Entity Framework & Table Per Hierarchy (TPH) Inheritance

Question

I am modeling an inheritance hierarchy as shown below:

I want to model the BaseClass hierarchy as "Table Per Hierarchy" (TPH). Any of the derived types of BaseClass can have children in the ChildClass table. To be clear, this is a true Parent / child relationship in that any given row in ChildClass is owned by one and one one instance of DerivedClass One, Two, or Three. In the ChildClass table I want to use a single column "ParentId" to hold the Id of this Parent.

Here's the code for the project: https://github.com/wadebaird/EFParentChildInheritExample

When I use "Add-Migration" and "Script-Migration" to generate the database schema it creates this code for me:

CREATE TABLE[Children] (
    [Id] int NOT NULL IDENTITY,
    [ParentId] int NOT NULL,
    [ParentClassId] int NULL,
    [PropertyX] nvarchar(max) NULL,
    [DerivedClassOneId] int NULL,
    [DerivedClassTwoId] int NULL,
    CONSTRAINT[PK_Children] PRIMARY KEY ([Id], [ParentId]),
    CONSTRAINT[FK_Children_Base_DerivedClassOneId] FOREIGN KEY([DerivedClassOneId]) REFERENCES[Base]([Id]),
    CONSTRAINT[FK_Children_Base_DerivedClassTwoId] FOREIGN KEY([DerivedClassTwoId]) REFERENCES[Base]([Id]),
    CONSTRAINT[FK_Children_Base_ParentClassId] FOREIGN KEY([ParentClassId]) REFERENCES[Base]([Id])
);

I would like it to create code that would result in this:

CREATE TABLE[Children] (
    [Id] int NOT NULL IDENTITY,
    [ParentId] int NOT NULL,
    [PropertyX] nvarchar(max) NULL,
    CONSTRAINT[PK_Children] PRIMARY KEY ([Id], [ParentId]),
    CONSTRAINT[FK_Children_Base_ParentId] FOREIGN KEY([ParentId]) REFERENCES[Base]([Id])
);

I have tried solutions such as this that I found in other stack questions:

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

    modelBuilder.Entity<DerivedClassOne>()
        .HasMany(e => e.Children)
        .WithOne(e => (DerivedClassOne)e.ParentClass)
        .HasForeignKey(e => e.ParentId)
        .HasPrincipalKey(e => e.Id);

    modelBuilder.Entity<DerivedClassTwo>()
        .HasMany(e => e.Children)
        .WithOne(e => (DerivedClassTwo)e.ParentClass)
        .HasForeignKey(e => e.ParentId)
        .HasPrincipalKey(e => e.Id);

    modelBuilder.Entity<DerivedClassThree>()
        .HasOne(e => e.Child)
        .WithOne(e => (DerivedClassThree)e.ParentClass)
        .HasForeignKey<ChildClass>(e => e.ParentId)
        .HasPrincipalKey<DerivedClassThree>(e => e.Id);
}

but I get these errors:

Cannot create a relationship between 'ChildClass.ParentClass' and 'DerivedClassThree.Child' because a relationship already exists between 'DerivedClassTwo.Children' and 'ChildClass.ParentClass'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'DerivedClassThree.Child' first in 'OnModelCreating'.

Any help on how I would model this so the migration builder would generate my desired output? I've looked at several other posts such as these, which tell me this is not currently possible, but I wanted to check before I gave up.

The only solution I found for this is to duplicate the child hierarchy:

public class ChildClassOne : ChildClass
{
    public DerivedClassOne Parent { get; set; }
}

public class ChildClassTwo : ChildClass
{
    public DerivedClassTwo Parent { get; set; }
}

public class ChildClassThree : ChildClass
{
    public DerivedClassThree Parent { get; set; }
}

And then the OnModelCreating works as so:

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

    modelBuilder.Entity<DerivedClassOne>()
        .HasMany(e => e.Children)
        .WithOne(e => e.Parent)
        .HasForeignKey(e => e.ParentId)
        .HasPrincipalKey(e => e.Id);

    modelBuilder.Entity<DerivedClassTwo>()
        .HasMany(e => e.Children)
        .WithOne(e => e.Parent)
        .HasForeignKey(e => e.ParentId)
        .HasPrincipalKey(e => e.Id);

    modelBuilder.Entity<DerivedClassThree>()
        .HasOne(e => e.Child)
        .WithOne(e => e.Parent)
        .HasForeignKey<ChildClassThree>(e => e.ParentId)
        .HasPrincipalKey<DerivedClassThree>(e => e.Id);
}

But I really don't like this as it obviously adds complexity to the code inheritance hierarchy just to make the data model work.

Multiple child compositions to the same type w/ Entity Framework & Table Per Hierarchy (TPH) Inheritance

0 Answers0