0

I am trying to use "table per hierarchy" to model a variety of account types in my database using EF Core (2.1) and I am having a problem where I am getting additional foreign key columns added for the derived types. I have seen this question/answer but I don't think it is the same as my situation.

Here are my account classes:

public abstract class Base
{
    [Key]
    public int Id { get; set; }
}

public class Standard : Base
{
    public int ParentId { get; set; }

    [ForeignKey(nameof(ParentId))]
    public Elevated Parent { get; set; }
}

public abstract class Elevated : Base
{
    public ICollection<Base> Accounts { get; set; } = new List<Base>();
}

public class Plus : Elevated
{
}

public class Ultimate : Elevated
{
}

And I have configured TPH like this in my DbContext constructor:

builder.Entity<Base>()
    .HasDiscriminator<string>("Type")
        .HasValue<Ultimate>("ultimate")
        .HasValue<Plus>("plus")
        .HasValue<Standard>("standard");

and I seed some basic data like this:

builder.Entity<Ultimate>().HasData(new Ultimate() { Id = 1 });
builder.Entity<Plus>().HasData(new Plus() { Id = 2 });
builder.Entity<Standard>().HasData(new Standard() { Id = 3, ParentId = 1 });

when I then run migrations I get this table:

my table

with this data:

my data

As you can see I have a ElevatedId column that I do not want. If I remove the Accounts collection from the Elevated class I do not get that column; but I need/want that navigation property.

Is there anything I should be doing differently?

Ben
  • 5,525
  • 8
  • 42
  • 66

1 Answers1

1

In your model any Base can be added to the Accounts of an Elevated, and so every Base needs and ElevatedId Foreign Key property. Only a Standard can have an Elevated Parent.

And a Standard can have an Elevated Parent and belong to the Accounts of a different Elevated.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Ah! So is the answer to change the type of `Accounts` from `Base` to `Standard`? Sorry I'm away from my computer until tomorrow now but I can't not know until then! – Ben Feb 04 '19 at 18:34
  • Yes that was the answer! Thanks @davidbrowne – Ben Feb 05 '19 at 10:16