Entity Framework TPT Inheritance with ObjectType Discriminator Column

493 Views Asked by At

I'm using Entity Framework as an ORM for my database. To model inheritance, my database uses the Table Per Type (aka Class Table Inheritance) pattern. Entity Framework does support TPT inheritance. However it is extremely slow. It generates very complicated select queries for the simplest linq queries. (see this post) For background on inheritance in databases, Here is a nice post on different types of inheritance patterns.

As an alternative my database uses a variation of the TPT pattern that includes the type of child the current object is. This is following the suggestions on this post. Including the type of the child should make simpler queries since you know what child table to join with.

Question: Is there any way to tell Entity Framework to understand the following TPT pattern and do more concise select queries accordingly? Secondly, can I hide the type column from the ORM and have it only appear in the database?

Take the following example:

Parent Table:

create table People 
( 
    PersonID int primary key, 
    PersonTypeID int references PersonType(PersonTypeID),
    Name varchar(10) 
    constraint People_AltPK unique (PersonID,PersonTypeID)
)

Children Tables:

create table Students 
(
    PersonID int primary key,
    PersonTypeID as 1 persisted, -- student 
    EnrollmentDate datetime,
    foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID) 
 ) 

 create table Teachers 
 (
    PersonID int primary key,
    PersonTypeID as 2 persisted, -- teacher
    HireDate datetime, 
    foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID) 
 )

create table Parents (
    PersonID int primary key,
    PersonTypeID as 3 persisted, -- parents 
    DifficultyScore int,
    foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
)
0

There are 0 best solutions below