Entity Framework Core TPH with Foreign Keys on Derived Types Erroring

Question

I am in the process of trying to convert a mature Entity Framework 6.4.4 project over to Entity Framework Core 8 so (I can be in the future with everyone else) but I am running into the following runtime error:

Here is the what I have for the Model:

public partial class HighlandEntities : DbContext
{
  public virtual DbSet<Target> Targets { get; set; }

  public virtual DbSet<AccountTarget> AccountTargets { get; set; }

  public virtual DbSet<PortfolioTarget> PortfolioTargets { get; set; }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {

    modelBuilder.Entity<Target>(entity =>
    {
      entity.ToTable("Targets").HasDiscriminator<char>("OwnerType")
        .HasValue<PortfolioTarget>('P')
        .HasValue<AccountTarget>('A');

      entity.Property(e => e.TargetID).ValueGeneratedNever();
      entity.Property(e => e.RowVersion)
        .IsRowVersion()
        .IsConcurrencyToken();
    });

  }
}

public abstract partial class Target : EntityBase
{
  [Key]
  public Guid TargetID { get; set; }

  [Column(TypeName = "date")] 
  public DateTime? AsOfDate { get; set; }

  public double TargetPct { get; set; }
}

public partial class AccountTarget : Target
{

  Guid _AccountID;
  [Column("OwnerID")] 
  public virtual Guid AccountID { get; set; }

  Guid _InvestmentID;
  [Column("BalanceOwnerID")]
  public virtual Guid InvestmentID { get; set; }

  [ForeignKey(nameof(AccountID))]
  [InverseProperty("AccountTargets")]
  public virtual Account Account { get; set; }

  [ForeignKey(nameof(InvestmentID))]
  [InverseProperty("AccountTargets")]
  public virtual Investment Investment { get; set; }

}

public partial class PortfolioTarget : Target
{

  Guid _PortfolioID;
  [Column("OwnerID")]
  public virtual Guid PortfolioID { get; set; }

  Guid _InvestmentID;
  [Column("BalanceOwnerID")]
  public virtual Guid InvestmentID { get; set; }

  [ForeignKey(nameof(PortfolioID))]
  [InverseProperty("PortfolioTargets")]
  public virtual Portfolio Portfolio { get; set; }

  [ForeignKey(nameof(InvestmentID))]
  [InverseProperty("PortfolioTargets")]
  public virtual Investment Investment { get; set; }

}

For more context here is the objects in the *.edmx file from the EF 6 version:

How should I change this to get around this error? Thanks.

Answer 1

There are a few issues with your entities.

First, that isn't TPH. TPH has all columns for all sub-classes in a single table using a discriminator. For example you would have a single Target table with a Discriminator (or TargetType etc.) column with values of "Account" vs. "Portfolio". What you have outlined in the schema is actually a TPT model. (Table per Type)

In a TPT if TargetAccount and TargetPortfolio are sub-classes of Target, then each would have a TargetId as their PK with Account-specific details and Portfolio-specific details respectively. When I see things like AccountId and PortfolioId in these tables, with navigation to an Account or Portfolio table then these aren't actually inheritance-based relationships, they are many-to-many joining tables and should be set up simply as a many-to-many joining entity, not an inheritance structure around Target.

I would start by ignoring inheritance all-together at first and look at the data. How do Targets, Portfolios, and Accounts actually relate to one another as the tables are defined? This does look like you might have jumped down an inheritance rabbit hole that is completely unnecessary.

Answer 2

Here's the modified code that gets me past that error. I won't know 100% that it works until I replicate the process with the other TPH entities in the model (there are many) and the model builds. Basically,

1. Remove all mention of the base class from DbContext class.
2. Move its properties to the derived classes.
3. Add any properties that were referenced in the old partial abstract base declaration as abstracts in that one. Remove the partial key word and add the "[NotMapped]" attribute.

    public partial class HighlandEntities : DbContext
    {
      public virtual DbSet<AccountTarget> AccountTargets { get; set; }
      public virtual DbSet<PortfolioTarget> PortfolioTargets { get; set; }
    
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
        modelBuilder.Entity<AccountTarget>(entity =>
        {
          entity.ToTable("Targets").HasDiscriminator<char>("OwnerType")
            .HasValue<AccountTarget>('A');
    
          entity.Property(e => e.TargetID).ValueGeneratedNever();
          entity.Property(e => e.RowVersion)
            .IsRowVersion()
            .IsConcurrencyToken();
        });
  
        modelBuilder.Entity<PortfolioTarget>(entity =>
        {
          entity.ToTable("Targets").HasDiscriminator<char>("OwnerType")
            .HasValue<PortfolioTarget>('P');
    
          entity.Property(e => e.TargetID).ValueGeneratedNever();
          entity.Property(e => e.RowVersion)
            .IsRowVersion()
            .IsConcurrencyToken();
        });

      }
    }

    [NotMapped]
    public abstract class Target : EntityBase
    {
      [Key]
      public abstr Guid TargetID { get; set; }

      [Column(TypeName = "date")]  
      public DateTime? AsOfDate { get; set; }

      public double TargetPct { get; set; }    
    }

    public partial class AccountTarget : Target
    {

      [Key]
      public Guid TargetID { get; set; }

      [Column(TypeName = "date")]
      public override DateTime? AsOfDate { get; set; }
    
      public override double TargetPct { get; set; }

      Guid _AccountID;
      [Column("OwnerID")] 
      public virtual Guid AccountID { get; set; }

      Guid _InvestmentID;
      [Column("BalanceOwnerID")]
      public virtual Guid InvestmentID { get; set; }

      [ForeignKey(nameof(AccountID))]
      [InverseProperty("AccountTargets")]
      public virtual Account Account { get; set; }

      [ForeignKey(nameof(InvestmentID))]
      [InverseProperty("AccountTargets")]
      public virtual Investment Investment { get; set; }

    }

    public partial class PortfolioTarget : Target
    {
      [Key]
      public Guid TargetID { get; set; }

      [Column(TypeName = "date")]
      public override DateTime? AsOfDate { get; set; }

      public override double TargetPct { get; set; }

      Guid _PortfolioID;
      [Column("OwnerID")]
      public virtual Guid PortfolioID { get; set; }

      Guid _InvestmentID;
      [Column("BalanceOwnerID")]
      public virtual Guid InvestmentID { get; set; }

      [ForeignKey(nameof(PortfolioID))]
      [InverseProperty("PortfolioTargets")]
      public virtual Portfolio Portfolio { get; set; }

      [ForeignKey(nameof(InvestmentID))]
      [InverseProperty("PortfolioTargets")]
      public virtual Investment Investment { get; set; }

    }

I'll update this answer if/when I get the whole thing work. If this is truly the only way to do this in EF Core I'm pretty disappointed. My EF6 models use lots of abstract base classes for TPH that contain many fields (common to all derived classes), and those derived classes are owned by numerous different classes that are all using the same foreign key columns, "OwnerID," in their shared tables.

UPDATE TO ANSWER

Although the above got me past the error, the issue resurfaced on another similar class structure in my project where I was not able to remove the abstract base class from the model because it appeared in navigational properties of other classes. When that happens the "by convention" part of the EF Core model building process wants to again shove the foreign keys onto the base class. In the case of this other class that would have resulted in 14 separate key fields in the base class table! I ended up posing the question to the EF Core team on GitHub https://github.com/dotnet/efcore/issues/33340

FINAL UPDATE

I was targeting EF7.0 when I posed the question. Changed to EF8.0 and everything worked.