Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Relational: TPT inheritance mapping pattern #2266

Closed
satyajit-behera opened this issue May 24, 2015 · 278 comments
Closed

Relational: TPT inheritance mapping pattern #2266

satyajit-behera opened this issue May 24, 2015 · 278 comments

Comments

@satyajit-behera
Copy link

@satyajit-behera satyajit-behera commented May 24, 2015

Even if TPT is considered slow, it is a boon for many real world business scenarios. If implemented properly and well thought of, TPT is not slow for a given need in most of the cases.

If possible, optimization can be done to TPT.
But its a very important feature for EF to be accepted for developing DDD applications.

The other method, Composition over Inheritance does not look viable since we cannot use interface property to create our model. Binding to a concrete type takes away the flexibility to customize the models. TPT makes the customization very easy and flexible.

@rowanmiller rowanmiller added this to the Backlog milestone May 29, 2015
@rowanmiller
Copy link
Contributor

@rowanmiller rowanmiller commented May 29, 2015

Update: we are planning to implement TPT. This comment is from 2015. Please read the rest of the thread, not just this comment.

The feeling from our team is that TPT is generally an anti-pattern and results in significant performance issues later on. While enabling it may make some folks "happier" to start with it ultimately just leads to issues. We are willing to consider it though, so we're leaving this open and will consider it based on the feedback we get.

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented May 29, 2015

Thanks. Issue is the absence of any alternative to build an OO application. Even Composition using interfaces does not look viable for now.

@rowanmiller
Copy link
Contributor

@rowanmiller rowanmiller commented May 29, 2015

We are in the process of implementing TPH at the moment, so you will be able to persist an inheritance hierarchy to a single table in the near future.

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented May 29, 2015

But it does not solve the main issue of extending a table. TPT becomes a necessity in practical business scenario. Otherwise, it may become a table with 100s of fields in it.

@anpete
Copy link
Contributor

@anpete anpete commented May 29, 2015

@satyajit-behera TPT is an inheritance mapping pattern. Are you referring to "Entity Splitting". i.e. Having a single entity mapped to more than one table?

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented May 30, 2015

@anpete I am referring to One table per inherited entity type.

@GSPP
Copy link

@GSPP GSPP commented Jun 15, 2015

TPT can be important for performance. Think of huge tables. Usually, you want only the absolutely required fields in such a table. TPH has column bloat, TPT is lean. Also, with TPT database statistics can be specialized for each type. This is important because different types can have vastly different data distributions.

TPT is quite an important pattern and I don't see why it would be considered an anti-pattern. An anti-pattern is a thing that is almost always the wrong choice. This does not seem to hold here.

@santanaguy
Copy link

@santanaguy santanaguy commented Jun 23, 2015

@GSPP i agree with you. Having TPT is a must for an ORM and i think it was recurrent solution for a lot of problems in EF6 in the real world. I used it in my models a lot. One should be careful with the tools he uses, but that doesn't mean he shouldn't have those tools available. Both types of inheritance should be present in EF Core. Please include this scenario in the 7.0 release

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented Jul 3, 2015

@rowanmiller I hope you will prioritize and include the TPT inheritance mapping for EF 7. All of us can discuss if some specific feature make it slow and get a workaround for the same. But the feature itself is very useful in business applications. Please include this in EF Core release. Thanks a lot.

@ToddThomson
Copy link

@ToddThomson ToddThomson commented Jul 16, 2015

👍 TPT inheritance may have some performance issues, but the design clarity it provides for real world scenarios outweighs them. I also do not feel that TPT inheritance is an "anti-pattern".

@konstantin999
Copy link

@konstantin999 konstantin999 commented Jul 31, 2015

@rowanmiller TPT inheritance is the reason why we can not use EF in our projects :( It would be nice if it will included in EF Core.
http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/1015337-tpt-table-per-type-inheritance-performance
https://entityframework.codeplex.com/workitem/2332

@konstantin999
Copy link

@konstantin999 konstantin999 commented Jul 31, 2015

For example, in DO this problem was solved by including a TypeId field in all types. http://help.x-tensive.com/Default.aspx##DataObjects.Net%20v3.9/html/P_DataObjects_NET_DataObject_TypeID.htm

@AndyNewland
Copy link

@AndyNewland AndyNewland commented Aug 27, 2015

Agree 100% with @ToddThomson. This is another strong vote to add this feature ASAP. I think that TPH can be viewed as a SQL Server anti-pattern once the number of columns becomes unmanageable from a statistics and (more importantly) indexing viewpoint.

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented Aug 27, 2015

@rowanmiller Hi, now you can consider including this feature asap. There is really no alternative to this to model real world objects in business application.

@jimmymain
Copy link

@jimmymain jimmymain commented Sep 18, 2015

I agree with andy, if the EF team honestly believe that they cannot do this because it's too slow, we would have to look to alternate products. Entity framework becomes pretty useless for our real world business scenarios. Our SQL team will never endorse de-normalised tables because of EF.

I think some evidence is necessary before labeling this an anti pattern.

@ToddThomson
Copy link

@ToddThomson ToddThomson commented Sep 18, 2015

Preface: This is not a knock against the EF Core team and their design goals. I really just want to know what I'm going to be working with if I choose to go with EF Core over EF6 for the next 12 months or so.

I think that there are real world scenarios that benefit from either TPH, TPC or TPT inheritance. I feel that the EF Core team needs to state clearly that TPC and/or TPT are going to be part of the future EF Core implementation or perhaps only to make some people happier down the road given enough community votes and when time permits. If TPC or TPT inheritance is not a high priority ( implementation if any, most likely later in 2016 ), then composition is the most likely alternative for those who want a normalized database schema.

For me personally, I believe moving to ASP.NET Core and EF Core is a port of my MVC 5, EF 6 application and I am willing to try different approaches. If I can't get EF Core to work then EF6 is still an option ( although the ASP.NET 5 Identity package requires EF Core and EF 6 code first migrations are issues ).

@rowanmiller rowanmiller changed the title TPT for EF7 Relational: TPT inheritance mapping pattern Sep 18, 2015
@jimmymain
Copy link

@jimmymain jimmymain commented Sep 18, 2015

A clear statement of intent would assist us in making sound decisions on behalf of our clients.

@rowanmiller
Copy link
Contributor

@rowanmiller rowanmiller commented Sep 28, 2015

TPT is definitely out for our November RC release (purely due to time constraints). Clearing up milestone so that we can re-discuss in triage and see if we can make a clear yes/no on our intent to support the feature in the future (my feeling is that we have enough feedback that folks want it).

@rowanmiller rowanmiller removed this from the Backlog milestone Sep 28, 2015
@ToddThomson
Copy link

@ToddThomson ToddThomson commented Sep 28, 2015

Thank-you for the feedback @rowanmiller .

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented Sep 29, 2015

Thanks @rowanmiller . Hope you will take this item with priority, since an alternative is not available to implement the concepts possible with TPT. Critical and Important. I am sure many enterprise level applications will be using this.

@thomas-darling
Copy link

@thomas-darling thomas-darling commented Sep 29, 2015

Another vote for TPC and TPH - we absolutely need it and will look for another ORM if it's not supported. I agree that query performance is not great, but it is very important if we want a nicely normalized domain model. We can always create denormalized tables or views optimized for specific queries, but our single source of truth must be nicely structured.

The fact that inexperienced developers tend to abuse a feature, that does not mean the feature is not a criitical requirement for those of us who know what we are doing. It just means that people should learn about their tools before using them and that documentation should be improved to clearly state performance characteristics.

@rowanmiller
Copy link
Contributor

@rowanmiller rowanmiller commented Sep 29, 2015

@thomas-darling TPH is already being implemented for our RC release in November (it mostly works now) and TPC is on our backlog (#3170). This issue is specifically about TPT and whether that pattern needs to be supported.

@satyajit-behera
Copy link
Author

@satyajit-behera satyajit-behera commented Sep 29, 2015

@rowanmiller Difficult to get rid of TPT without any alternative implementation for the same. One common "Id" shared across all inherited classes. Base class should allow typecasting to inherited class. And all reside in different tables.

@rowanmiller rowanmiller added this to the Backlog milestone Oct 2, 2015
smitpatel added a commit that referenced this issue Jul 3, 2020
Resolves #2266
@smitpatel
Copy link
Member

@smitpatel smitpatel commented Jul 3, 2020

Design meeting notes from Jul 2, 2020

The pattern for gathering the data for hierarchy when queried for base type
For a hierarchy of Animal -> Bird -> { Kiwi, Eagle } where Animal/Bird both were abstract, EF6 generated something like below for DbSet() in FROM (prettified the SQL for readability)

    FROM    (SELECT 
        [e].[Species],
        [e].[Group],
        CAST(NULL AS tinyint) AS [FoundOn], 
        cast(1 as bit) AS [C2]
        FROM [Eagles] AS [e]
    UNION ALL
        SELECT 
        [k].[Species], 
        CAST(NULL AS int) AS [Group], 
        [k].[FoundOn], 
        cast(0 as bit) AS [C2]
        FROM [Kiwis] AS [k]) AS [t]
    INNER JOIN [Animals] AS [a] ON [t].[Species] = [a].[Species]
    INNER JOIN [Birds] AS [b] ON [t].[Species] = [b].[Species]

In EF Core we currently generate this

FROM [Animals] AS [a]
INNER JOIN [Birds] AS [b] ON [a].[Species] = [b].[Species]
LEFT JOIN [Eagle] AS [e] ON [a].[Species] = [e].[Species]
LEFT JOIN [Kiwi] AS [k] ON [a].[Species] = [k].[Species]

Apart from simplicity of the SQL, the UNION in first query can also cause subsequent joins to not use indexes for joining causing potentially slow perf. We have also found an old documentation from EF6 which discussed and profiled this specific difference and talks about using Joins all the way through rather than using Unions for siblings.

The shape of projection
EF6 generated something complicated like below

SELECT 
    [a].[CountryId], 
    CASE WHEN (((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1) AND ((CASE WHEN (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1)) THEN '0X0X' WHEN ((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) = 1) THEN '0X0X0X' ELSE '0X0X1X' END AS [C1], 
    [b].[Species],
    [a].[Name],
    [b].[IsFlightless],
    [b].[EagleId],
    CASE WHEN (((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1) AND ((CASE WHEN (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1)) THEN CAST(NULL AS int) WHEN ((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) = 1) THEN [t1].[Group] END AS [C2], 
    CASE WHEN (((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1) AND ((CASE WHEN (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t2].[C1] = 1) AND ([t2].[C1] IS NOT NULL))) THEN cast(0 as bit) END) <> 1)) THEN CAST(NULL AS tinyint) WHEN ((CASE WHEN (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([t1].[C1] = 1) AND ([t1].[C1] IS NOT NULL))) THEN cast(0 as bit) END) = 1) THEN CAST(NULL AS tinyint) ELSE [t2].[FoundOn] END AS [C3], 
    [b].[Eagle_Species]

The old documentation referred above, also discussed about simplifying the case blocks in above projections. Apart from that, EF6 generated a pseudo discriminator with values in the form '0X0X0X' where every X describes going down 1 level in the hierarchy tree and the number describes the entity index from left for all the nodes at that level.
In EF Core we have currently added a different pattern

SELECT [a].[Species], [a].[CountryId], [a].[Name], [b].[EagleId], [b].[IsFlightless], [e].[Group], [k].[FoundOn], CASE
    WHEN [e].[Species] IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsEagle], CASE
    WHEN [k].[Species] IS NOT NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsKiwi]

Rather than generating a pseudo discriminator, we generate IsType column for each derived type to identify which type to materialize.

The shape of projection will also made same in TPC for consistency and it matters a bit for users since this is the shape we will expect when we implement FromSql for TPT/TPC so user can write their FromSql query accordingly.
We are going to profile 2 approaches for perf and see if one is far superior than the other.

  1. bool column for each derived type similar to how EF Core generates atm.
  2. a string column for pseudo discriminator in the form below where the discriminator values are entity type short name (in case of clash we can allow configuring those values by relaxing HasDiscriminatorValue API on TPT/TPC.
CASE
    WHEN [e].[Species] IS NOT NULL THEN N'Eagle'
    WHEN [k].[Species] IS NOT NULL THEN N'Kiwi'
END

@roji has volunteered to run some perf tests on above query figure out a winner.

In case of similar perf,
We will likely go with Join over union and pseudo discriminator over bool columns.

Filed #21509 for above perf investigation
Filed #21508 for FromSql
Filed #21510 for additional testing for cross-cutting features.

smitpatel added a commit that referenced this issue Jul 3, 2020
Resolves #2266
@msftbot msftbot bot closed this in #21474 Jul 3, 2020
msftbot bot pushed a commit that referenced this issue Jul 3, 2020
Resolves #2266
@marchy
Copy link

@marchy marchy commented Jul 3, 2020

🎉

@tcfialho
Copy link

@tcfialho tcfialho commented Jul 3, 2020

👏🏻👏🏻👏🏻👏🏻👏🏻

@pcasais
Copy link

@pcasais pcasais commented Jul 5, 2020

Thanks a lot @smitpatel !! This is brilliant. Can't wait to use it!

@eraffel-MDSol
Copy link

@eraffel-MDSol eraffel-MDSol commented Aug 5, 2020

Question: Not sure if I'm wiring things up wrong, but when I generate the migrations I'd expect there to be a FK from the Id column on the derived table to the Id column on the base table. Is this possible, or not done on purpose?

Additionally, what if I wanted a property with the same names on both the base and derived tables (i.e., using the new keyword), is there a way to make that work?

@AndriySvyryd
Copy link
Member

@AndriySvyryd AndriySvyryd commented Aug 5, 2020

Question: Not sure if I'm wiring things up wrong, but when I generate the migrations I'd expect there to be a FK from the Id column on the derived table to the Id column on the base table. Is this possible, or not done on purpose?

#21943

Additionally, what if I wanted a property with the same names on both the base and derived tables (i.e., using the new keyword), is there a way to make that work?

#19811

@PieterjanDeClippel
Copy link

@PieterjanDeClippel PieterjanDeClippel commented Aug 11, 2020

Question: Not sure if I'm wiring things up wrong, but when I generate the migrations I'd expect there to be a FK from the Id column on the derived table to the Id column on the base table. Is this possible, or not done on purpose?

Not really, the main issue with the following design

Person
Id FirstName LastName
1 John Doe
2 Jim Socks
Student
Id PersonId Grade
1 1 6
Teacher
Id PersonId Course
1 2 Math

is that at some point you could have multiple Teachers/Students for one Person. By using the following design instead:

Person
Id PersonableType PersonableId FirstName LastName
1 Student 1 John Doe
2 Teacher 2 Jim Socks
Student
Id Grade
1 6
Teacher
Id Course
1 Math

You're certain that there can only be one derived entity per parent.

@boomalator
Copy link

@boomalator boomalator commented Aug 23, 2020

is that at some point you could have multiple Teachers/Students for one Person. By using the following design instead:

That happens in the real world, though. In my last year of college, I was a STUDENT in my courses, but also hired to be a TEACHER in the Cont Ed division (teaching AutoCad and Excel to senior citizens... tons of fun).

While a bird is not going to be both an Eagle and and a Kiwi, a person is totally able to be both a Student and a Teacher. Another example is a well-known online accounting system that doesn't allow a person (or a company) to be more that one of "supplier", "customer" and "employee", which means multiple records when a staff member has to be paid for some odd expense, or you both buy from and sell to another company.

The real world answer tends to be creating a "John Smith (Student)" and "John Smith (Teacher)" record, but that is confusing and leads to errors (when John moves and only one address is updated).

What's the right way to model those kinds of relationships?

@gojanpaolo
Copy link

@gojanpaolo gojanpaolo commented Aug 23, 2020

@boomalator You could design it such that a Person (John Smith) can have many Jobs (Student and Teacher). It might be a many-to-many relationship because multiple Person can have the same Job.

@boomalator
Copy link

@boomalator boomalator commented Aug 23, 2020

@boomalator You could design it such that a Person (John Smith) can have Jobs (Student and Teacher). It might be a many-to-many relationship because multiple Person can have the same Job.

That would suggest to me that modeling Student and Teacher as inheriting from Person would be flawed as a practice and as an example.

@gojanpaolo
Copy link

@gojanpaolo gojanpaolo commented Aug 23, 2020

For simple cases and examples, inheritance will work just fine. But it doesn't mean that we need to keep modeling it as inheritance when the scope becomes bigger than what it was initially designed for. It also doesn't mean that we should avoid using inheritance at all. It really depends on the domain you're working on. 😀

@marchy
Copy link

@marchy marchy commented Aug 23, 2020

is that at some point you could have multiple Teachers/Students for one Person. By using the following design instead:

That happens in the real world, though. In my last year of college, I was a STUDENT in my courses, but also hired to be a TEACHER in the Cont Ed division (teaching AutoCad and Excel to senior citizens... tons of fun).

While a bird is not going to be both an Eagle and and a Kiwi, a person is totally able to be both a Student and a Teacher. Another example is a well-known online accounting system that doesn't allow a person (or a company) to be more that one of "supplier", "customer" and "employee", which means multiple records when a staff member has to be paid for some odd expense, or you both buy from and sell to another company.

The real world answer tends to be creating a "John Smith (Student)" and "John Smith (Teacher)" record, but that is confusing and leads to errors (when John moves and only one address is updated).

What's the right way to model those kinds of relationships?

This is a wonderful question @boomalator.

This comes up a lot at Facebook for example, where their Graph API models everything as Nodes (ie: entities) and Edges (ie: relationships), as well as Fields which store the data about those entities/relationships. These are used to model everything including People, Organizations, Pages, Events, Groups etc.

While you don't necessarily have to abstract your entire data base into an object graph like Facebook does, the moment you start hitting 'roles' that a person can take on (ie: them being a Student, a Teacher, a Father, Investor etc.)... inheritance is likely a misfit for modelling the real-world structure at hand.

Think about the domain you are trying to model and try to pick the right level of abstraction. Is it a Position that might have a title, length of employment etc. (such as for recruiting/LinkedIn), or something more/less abstract like a Job (ie: job would not appropriately account for a sitting on an executive board or volunteering for something, as those are not technically 'jobs').

@PieterjanDeClippel
Copy link

@PieterjanDeClippel PieterjanDeClippel commented Aug 24, 2020

is that at some point you could have multiple Teachers/Students for one Person. By using the following design instead:

That happens in the real world, though. In my last year of college, I was a STUDENT in my courses, but also hired to be a TEACHER in the Cont Ed division (teaching AutoCad and Excel to senior citizens... tons of fun).

While a bird is not going to be both an Eagle and and a Kiwi, a person is totally able to be both a Student and a Teacher. Another example is a well-known online accounting system that doesn't allow a person (or a company) to be more that one of "supplier", "customer" and "employee", which means multiple records when a staff member has to be paid for some odd expense, or you both buy from and sell to another company.

The real world answer tends to be creating a "John Smith (Student)" and "John Smith (Teacher)" record, but that is confusing and leads to errors (when John moves and only one address is updated).

What's the right way to model those kinds of relationships?

Okay, indeed not such a good example. The example of Bird <- Kiwi and Bird <- Eagle is more applicable if you're talking about Inheritance. Or another example:

Vehicle <- Car
        <- Bike
        <- Motorbike

The case you're talking about, eg.

Member <- Customer
       <- Supplier
       <- Contact

Where a Member can be both Customer, Supplier and Contact is not an example of inheritance, but can be modelled like this:

public class Member
{
    public List<Customer> Customers { get; set; }
    public List<Supplier> Suppliers { get; set; }
    public List<Contact> Contacts { get; set; }
}

public class Customer
{
    public Member Member { get; set }
}

public class Supplier
{
    public Member Member { get; set }
}

public class Contact
{
    public Member Member { get; set }
}

Then a member can be both Customer and Supplier

@RonOsmo
Copy link

@RonOsmo RonOsmo commented Oct 10, 2020

I have been working on a monolithic system. My employer has developed a caching ORM for their ASP.NET web application.
If one opens a SalesOrder page there are approximately 800 individual calls to SQL Server.
In order to do a quick proof of concept to try and load those 800 calls upfront, I chose to use the TPT features of EF5 Core. Unfortunately there are 2 very basic problems with this approach:

  1. I cannot find a way to override the shadow property name - it always uses the base class's primary key field. I understand why, but I don't want to change a lot of code. It usually drops the key I wanted and replaces it with another, which your code wants. This does not play nice with my needs. This is the kind of hack I have to do now:-
    public int UserId { get => base.ContactId; set { base.ContactId = value; } }

             May I suggest a new approach? what about:-
             `entity.ToTable("User").HasBaseType<<Contact>>("UserId");' // _optional param to set the shadow property name_
    
  2. I now find that migrations drops inheriting tables (when they have few columns - 1 to ?) and adds a discriminator in the base table. What? I am sure its very cool code, but I told the system to put it in a certain table, and I think EF Core should honor it. This feature seems much worse than 1 above. I'm now going to try and add more columns to that inheriting table. Do you think I don't know there is only one relevant column in the inheriting table? Over time we have written a lot of queries for reports and such which will now have to be reviewed and changed, please don't go down this path - it is only good for green fields development - not for the real world.

Real developers only need to reverse engineer their database once (or in part), then they can use EF Core to play around with it.

@AndriySvyryd
Copy link
Member

@AndriySvyryd AndriySvyryd commented Oct 10, 2020

@RonOsmo Please file those as separate issues with small runnable projects that show the unwanted behavior

@kimballjohnson
Copy link

@kimballjohnson kimballjohnson commented May 16, 2021

Sorry, but I'm not seeing a clear story here for either the 'for' or the 'against' argument regarding TPT.

Certainly, the obvious use case here has to be the PostgreSQL hierarchical table arrangement.
While I understand the natural inclination for a Microsoft framework is to advance integration scenarios that support well-established database platforms.

But discussions of samples and conceptual representations of TPT as a query-syntax-structure problem rather than a data-entity-relationship challenge seem intentionally designed to avoid the value of hierarchical tables in favor of the TPT implementation pattern (or style, depending on how it is conceived).

My viewpoint is that those posting requests for TPT are making a DB design and implementation argument in favor of the established value of the hierarchical table use case.

But the perspective of the EF team is that all issues are aspects of the conceptual framework of TPH/TPT and its variants.

All of these are implementation issues, not use cases, and that perspective drives this discussion, and the decision it should be targeting toward basing this fundamental framework design choice exclusively on what is essentially a SQL Query Optimization decision tree.

I believe the correct elucidation of the TPT question, and the path to the answer lies in an examination of the source code of PostgreSQL Hierarchical Table implementation, since none of the requesters are expressing a requirement based on a modified or replaced TPT strategy, but are looking for the data design and entity-relationship efficiency that isolates Application-side Entities from the repetitive server-side utility and tracking data tables that are attached by means of relations to true 'entity rows' tables that are mapped to objects in code by EF and EF Core.

This pattern of isolation of entity-specific database tables to utility-generic database tables is the essence and the point of the hierarchical table structure. Without a basic explication of that Functionality, no real communication on this topic will occur.

But all parties should do the basic research necessary to be able to explain what they are trying to achieve in this discussion and why they may be trying to conflate a TPT implementation pattern and a Data Storage design pattern.

@roji
Copy link
Member

@roji roji commented May 16, 2021

@kimballjohnson TPT support was already introduced as an EF Core feature into version 5.0, so the discussion may be moot at this point.

Regardless, the PostgreSQL hierarchical table arrangement is, well, PostgreSQL-specific. One could argue that it should exist in MySQL and SQL Server, but that's quite out of EF Core's scope. Note that PostgreSQL hierarchical tables are also considered legacy by some.

So, concretely speaking, users needing inheritance mapping today - on non-PostgreSQL databases - need to make a choice between TPH and TPT; and TPT is known to have problematic perf in some scenarios because of the additional JOINs it introduces(see more about this in our docs).

@kimballjohnson
Copy link

@kimballjohnson kimballjohnson commented May 18, 2021

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.