Creating subtype of a subtype with Table-per-Type (TPT) approach using ef core

user_9091117 0
Viewed 153

I am using the table per type strategy with a class named Assets as my super-type and another class PropertyAsset inheriting it. However, there are two classes inheriting the PropertyAsset class, named InternalProperty & ExternalProperty.

Asset Table

public class Asset : Entity, IMustHaveCompany
{
    public int CompanyId { get; set; }
    public string? SerialNumber { get; set; }
    public decimal ExpectedLife { get; set; }
    public decimal EstimatedValue { get; set; }
    public DateTime PurchaseDate { get; set; }
    
    #region Navigation Properties

    public virtual Company.Company? Company { get; set; }    

    #endregion
}

Property Asset Table

public class PropertyAsset : Asset
{
    public int Rooms { get; set; }
    public int Bathrooms { get; set; }
    public decimal LeaseRate { get; set; }
    public decimal SqFeet { get; set; }
}

External Property Table

public class ExternalProperty : PropertyAsset
{
    public decimal LeaseRatePerSquareFt { get; set; }
    public decimal SquareFoot { get; set; }
    public decimal AnnualIncome { get; set; }
    public decimal MonthlyLease { get; set; }
}

Internal Property Table

public class InternalProperty : PropertyAsset
{
    public decimal PurchasePrice { get; set; }
    public PaymentMethod PaymentMethod { get; set; }
    public decimal DownPayment { get; set; }
    public decimal PaymentsRemaining { get; set; }
    public decimal Borrowing { get; set; }
    public decimal AnnualTax { get; set; }
}

How would I create let's say the InternalProperty entity using this approach, with the values reflecting upwards? My thoughts are that I would need to use a DTO and map it with AutoMapper, but when thinking about it, I doubt that approach will work, or if it would, I'd need to create three entities separately (InternalProperty, then PropertyAsset and somehow use the Id of the newly created internal property, and then finally the Asset table itself.)

Am I using the TPT approach incorrectly in this instance?

entity-framework-core c#
1 Answers

I'd need to create three entities separately (InternalProperty, then PropertyAsset and somehow use the Id of the newly created internal property, and then finally the Asset table itself.)

Negative. Inheritance represents is a relationship, so you just need to create a single object of the desired type, add it to the context and EF Core will do the rest.

this, along with polymorphic queries is the whole purpose of EF Core database inheritance strategies. The only difference between different strategies (TPH, TPT) is how the data is stored (single table with discriminator column and union of all base and directly or indirectly derived entities data vs multiple tables storing just the data associated with the corresponding level of the entity hierarchy), respectively how is queried. But in both cases, you simply add assignable object instance to the corresponding set. For instance, if you have

var instance = new InternalProperty { ... };

then you can use any of the following

context.Set<Asset>().Add(instance);
context.Set<PropertyAsset>().Add(instance);
context.Set<InternalProperty>().Add(instance);
context.Add(instance);

(the last accepts object type argument).

All that works because EF Core uses the actual type (basically instance.GetType() except for proxy types) for handling the operation.

Similar happens when querying. Polymorphic queries means that anytime you query a base level, it includes the level plus all direct and indirect derived levels, with correct actual instance types.

For instance, if you query context.Set<Asset>, you would get all Asset, PropertyAsset, ExternalProperty and InternalProperty instances (with correct actual type). context.Set<PropertyAsset> will include all PropertyAsset, ExternalProperty and InternalProperty entities while context.Set<ExternalProperty> and context.Set<ExternalProperty> will include only the corresponding items.

Shortly, it is combination of query filter and query materialization creating correct object type and populating it with data. All that done automatically by EF Core.

So the usage of TPT (or TPH) is pretty valid for such object model. Just mark the classes (if any) which are not supposed to be creatable as abstract. It won't change the database structure (especially for TPT), but will allow generating more efficient queries (filters and joins) by EF Core.

user_52025630
Related