Efcore: Relational: TPT inheritance mapping pattern

[satyajit-behera]

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]

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]

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

[rowanmiller]

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]

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]

@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]

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

[GSPP]

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]

@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]

@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]

:+1: 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]

@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]

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]

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]

@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]

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]

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 ).

[jimmymain]

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

[rowanmiller]

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).

[ToddThomson]

Thank-you for the feedback @rowanmiller .

[satyajit-behera]

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]

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]

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

[satyajit-behera]

@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]

Triage: We do plan to do this based on feedback from the community. Purely due to time constraints it won't be in the initial release (just TPH will be in).

[AndyNewland]

Thanks @rowanmiller, good news

[nteague22]

I know as any release draws near, things can get hectic... But I at least want to take a moment and give you guys a round of applause. You and your team have stepped up in most every manner, continue to impress on response time and feedback, and I feel personally have shown a lot of poise. To many of my peers, the pre existing entity framework was one of the old and sluggish libs still around from Microsoft less nimble days, and your team has not only made it better and community first, but has undergone the scrutiny of developing in the open here, which increases pressure across the board. There's s reason many restaurants don't do open kitchens and similar businesses follow suit -- scrutiny is tough, especially from non empathetic view point. I will shut it down now, but I just wanted to say thanks, I have followed you guys since beta-2 on this, and am psyched at what new levels of uml branching to rapid ddl design I can achieve from just what I've used already!

[satyajit-behera]

Thanks @rowanmiller for putting this again as a backlog item. I hope, this will get a place in the final release.

[sanalbakis]

I see on the most comments, everyone would like to have the TPT, we also. I hope, it comes soon.

[satyajit-behera]

@rowanmiller Congratulations for the first release of EF Core as a Release Candidate. Thank you all to build such a wonderful product. I hope that the TPT implementation will also start soon. Not able to think of moving my existing projects to the newer version of asp.net waiting for this feature. Thanks a lot.

[vincedan]

We don't think that TPT inheritance is an "anti-pattern", on the countrary, it is the elegant way for "Conceptual Modeling" of datameta. without TPT, we can not move system to EF Core and only discard it. to refer performance issue, I think that developers should consider banlance and compromising. but as new product, you should provide this very important function.

[rowanmiller]

@vincedan - you may have already seen this from the comments... but we have agreed, based on feedback, that TPT should be added to EF Core.

[satyajit-behera]

@rowanmiller Thanks a lot :+1:

[Sbiligudy]

@rowanmiller Thanks a lot, TPT is very usefull!

[vincedan]

@rowanmiller very glad to hear that your guys have already added it in backlog. Thanks a lot.

[sthiakos]

I'm okay with Composition for TPT for now. @ToddThomson makes a very good recommendation to use composition, rather than inheritance, for TPT scenarios. The one drawback is that our object model now requires what would have been a base class to be instantiable rather than abstract, making the relational model influence our object model design.

[tomqwertysdsad]

Glad to see TPT will be implemented into EF7. I understand that it won't be in the first release but is there any guess to when we can expect to see it (6 months, this year)? Is there a workaround or crude implementation I could use in the mean time?

[rowanmiller]

@tomsharratt we'll be doing planning/prioritization for the post-7.0.0 releases around the time we release 7.0.0... so I don't have specific plans to share yet, but we will share them publically as soon as we have them.

[sthiakos]

Given

CREATE TABLE Base (
    BaseId INT NOT NULL Identity,
    BaseProperty INT NOT NULL,
    CONSTRAINT PK_Base PRIMARY KEY (BaseId)
)

CREATE TABLE Sub (
    BaseId INT NOT NULL,
    SubProperty BIGINT NOT NULL,
    CONSTRAINT PK_Sub PRIMARY KEY (BaseId),
    CONSTRAINT FK_Sub_Base FOREIGN KEY (BaseId) REFERENCES Base(BaseId)
)

TPT representation would be:

``` c#
public abstract class Base
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int BaseId { get; set; }

public int BaseProperty { get; set; }

protected Base()
{
}

}

public class Sub : Base
{
public long SubProperty { get; set; }
}

The Composition (alternative) representation is:

``` c#
public /*abstract*/ class Base
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int BaseId { get; set; }

    public int BaseProperty { get; set; }
}

public class Sub
{
    [Key]
    public int BaseId { get; set; }
    public Base Base { get; set; }

    public long SubProperty { get; set; }
}

[crozone]

@sthiakos does this work at all with polymorphism? To do a query on this, it appears that one needs to perform a separate query for every subclass that inherits Base

Eg. Context.SubAs.Where(s => s.Base.BaseProperty == "Something");
Context.SubBs.Where(s => s.Base.BaseProperty == "Something");
Context.SubCs.Where(s => s.Base.BaseProperty == "Something");

And each result set would need to be dealt with separately.

[sthiakos]

I _think_ you're right @crozone.

[satyajit-behera]

@rowanmiller Hi, hope the team will start with this feature now. Thanks.

[taspeotis]

Hi,

Thanks for all your hard work on EF Core. I realise it's pre-release software but the omission of TPT is disappointing.

I also find it somewhat disappointing that it's considered an anti-pattern. In the application I look after we have people operating in various contexts (users, contacts, employees). At the end of the day they're all people so I have a Person table that contains GivenName, Surname, DateOfBirth etc.

We never actually care about a person without their context (be it a user, contact or employee). So the bad queries that TPT can generate like:

SELECT ...
  FROM Person
       LEFT JOIN ( User ...
                   UNION ALL Contact ...
                   UNION ALL Employee )

Never happen.

Instead, if I wanted to do something like report on users who have recently failed to sign in (think _unitOfWork.Users.Where(u => u.LastFailedAttempt > ...)) there would be a query dispatched like:

SELECT ...
  FROM Person
       INNER JOIN User
 WHERE ...

Which is likely to be an efficient merge join between Person and User.

Our database is a bit more complicated than what EF Migrations would be ideal for. In among the tables that EF deals with there are things like user defined table types, materialized views and filtered indexes. To this end, we use SQL Server Data Tools to manage the schema.

TPT is nice because there's one place in the database for each attribute and no redundancy. (TPH has this too but at the expense of infecting my schema with its god tables and discriminator columns. TPC does not have this.)

To me TPH is an anti-pattern because of its wide tables and TPC is an anti-pattern because there's three places for me to maintain for any one attribute.

For now we use EF6. Please consider implementing TPT as soon as possible.

Again, thanks for all the hard work.

[GSPP]

To me TPH is an anti-pattern because of its wide tables and TPC is an anti-pattern because there's three places for me to maintain for any one attribute.

Be careful with such statements, all three forms have their uses. It depends on the requirements which is, in fact, a point you made earlier. Access patterns and storage concerns vary widely.

@taspeotis

[taspeotis]

Hi, thanks for reading.

Please don't read too much into that sentence: what I intended to convey that was in the application I maintain TPH and TPC appear as anti-patterns for the particular data we store.

TPH would result in wide tables. TPC would result in maintain some columns in tables in triplicate. In contrast, TPT appears as a desirable pattern. This is not necessarily true of TPH, TPC and TPT generally.

I probably should have started it with "For me..."

[jimmymain]

Do third normal form database design principles not contraindicate both TPC and TPH.

I agree that trade offs are made for many reasons, including expediency and efficiency, but I cannot see a scenario in which my clients database teams would sign of on either approach. They would insist on a database design consistent with TPT to comply with the principles of at least third normal form - some take it further.

[GSPP]

@jimmymain It does contraindicate that, yes. But the normal forms are just guidelines.

If your client is unwilling to denormalize in specific, well-reasoned cases they are causing themselves problems. This is not an issue with the three patterns. It's a lack of understanding on their part.

This insistence on following rules to the letter is a typical and understandable step in the path of most developers. As they become even more experienced they learn when to relax the rules.

[sthiakos]

I'd say TPC is not an anti-pattern nor contrary to 3NF. In TPC there is no repeating data _within_ the table. "fixing" TPC would imply having one table for the entire database that is for the attribute that is to be maintained (i.e. a master lookup table)

It's tough to accept relaxing the rules (even as an experienced developer). I'm not a fan at all of TPH because it swings to much to the OOD mindset. It seems TPH is the all-encompassing solution and the alternative is a TPC-TPT combination.

[mhosman]

Any news about TPT in EF7? I think this is CRITICAL. I started a new project with EF7 and when I discovered the omission of TPT I had to change and back to EF6. This is dissapointing.

[rowanmiller]

We have committed to implementing TPT (based mostly on feedback from the community), but it will be a post v1.0.0 feature. You can see a list of the features we consider most important post v1.0.0 as part of our Roadmap https://github.com/aspnet/EntityFramework/wiki/Roadmap.

[jimmymain]

Hi,

That's great news - but it's a show stopper for all our clients we will all
eagerly await version 2.

On Mon, 23 May 2016 at 10:10 PM Rowan Miller [email protected]
wrote:

We have committed to implementing TPT (based mostly on feedback from the
community), but it will be a post v1.0.0 feature. You can see a list of the
features we consider most important post v1.0.0 as part of our Roadmap
https://github.com/aspnet/EntityFramework/wiki/Roadmap.

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
https://github.com/aspnet/EntityFramework/issues/2266#issuecomment-221081340

[satyajit-behera]

@rowanmiller I hope, work on this item will start now. Thanks a lot.

[robhurd86]

+1 vote for TPT.
Is there news about when this feature will be released? I read it's high priority.

[weitzhandler]

Is there any temporary workaround to target TPT in EF 7?
It's is a mandatory requirement in all of our projects.

[sanalbakis]

@weitzhandler what do you need excatly? Instead of tpt, I made something else in my project. Unfortunately more db connections.

BaseTable: Id, BaseId, XXX, createdbyId, createdDate, ...
ChildTable: Id, BaseTableId,YYY

before each insert into ChildTable, I make an insert into BaseTable, get the BaseId, and use it in ChildTable - BaseTableId as foreign-key.

[JohnAskewBB]

TPH will facilitate this if you define a DbSet for each type.

On Oct 20, 2016 7:35 AM, "Shimmy" [email protected] wrote:

What I need is pretty simple:
-Person (Id, Name)
-Employee (Salary)
-Customer (Address, Phone)

Should be translated to 3 different tables, with the columns as they
appear above.

LOB apps became very time consuming since the death of Silverlight and RIA
services. Gosh I miss those days.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFramework/issues/2266#issuecomment-255081370,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGafBAQ0XMA4I3f6OiX7oxENsO9DVpLuks5q11IEgaJpZM4EnOQ5
.

[JohnAskewBB]

By contributing to an open source project, writing some SQL, or making
multiple queries.

If you want Person as anything other than an abstract type I would stick
with TPH for now and just don't look directly at the DB.

Ignorance is bliss

On Oct 20, 2016 7:56 AM, "Shimmy" [email protected] wrote:

And how do I retrieve all Persons?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFramework/issues/2266#issuecomment-255085287,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGafBMIgqH9nsARxG7ruCBiznHiMz7deks5q11b6gaJpZM4EnOQ5
.

[weitzhandler]

Ignorance is bliss

WOW @JohnAskewBB and you call this ignorance!
I'm looking for efficiency. I can write all my queries in direct SQL.

Anyway, I haven't tried this yet, but I'm curious what happens if I make a DbSet<Person> but in the model building stage I mark Employee and Contact to be generated as a table, what would happen then, will they be generated?
Can you make this happen, and if yes, can I plug in a convention or some other hack to automate this and fool EF7 to be TPT?

[anpete]

@weitzhandler One way might be to map via TPH to a database view. Then use something like INSTEAD OF triggers to send the updates to the actual target tables.

[JohnAskewBB]

I don't think that will work. You would end up being able to save the same
entity in either the Person table or the Contact table for example. You
could give it the same id but you would need to keep the records in sync
somehow. and you wouldn't be able to cast a Person to a Contact. You would
have to get it from Contact by id.

On Oct 20, 2016 7:56 PM, "Shimmy" [email protected] wrote:

WOW @JohnAskewBB https://github.com/JohnAskewBB and you call this
ignorance!
I'm looking for efficiency. I can write all my queries in direct SQL.

Anyway, I haven't tried this yet, but I'm curious what happens if I make a
DbSet but in the model building stage I mark Employee and Contact
to be generated as a table, what would happen then, will they be generated?
Can you make this happen, and if yes, can I plug in a convention or some
other hack to automate this and fool EF to be TPT?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFramework/issues/2266#issuecomment-255259526,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGafBIlNj5AeqoD-OWEzkZGpskR-oym-ks5q1_-ZgaJpZM4EnOQ5
.

[satyajit-behera]

@rowanmiller Hope this is taken up soon and allow everyone to move to DOTNET CORE faster. This is one of the important feature holding us back. No other alternative working out as a replacement for this feature. Thanks.

[rowanmiller]

@weitzhandler it's not on the plan for a release as yet. At this stage, that means it would be post 2.0

[jimmymain]

If anyone want's a workaround for EF6 that works well, I have used this https://github.com/mrahhal/MR.AspNet.Identity.EntityFramework6

it's a drop in replacement that allows ASP.NET core + EF6 with the new identity management for ASP.NET core. if you're starting a new project and want to use EF6 until such time as EF7 is mature enough, this project will help - assuming to want to leverage ASP.NET identity.

if you wanted to start migrating (re-write) an existing MVC5 application to MVC6 but need EF6 it's also a great solution.

Obviously the caveat remains that you need to use the full framework, you cannot deploy to core only.

[jimmymain]

Have you all seen this?
https://www.infoq.com/news/2017/05/ASPNET-Core-2?utm_campaign=infoq_content&utm_source=infoq&utm_medium=feed&utm_term=global

It would appear that entity framework 6 on dotnet core is going to be unsupported pretty soon. This makes all the work we have been doing in porting to dotnet core in jeopardy. It also makes TPT vastly more urgent than it used to be.

What's the status of these advanced features on EF core?
There's little or no communication. It's been two years or more since this issue was logged.

What does POST 2 mean?
2018?
2019?
2020?

We might have to consider back porting our dotnet core web sites back to ASP.NET / MVC 5 just to keep them working?

[weitzhandler]

@jimmymain that's upsetting.
Probably the only reason keeping me away from EF Core is TPT and ComplexTypes, that's two features we can't live without.
Imagine a scenario of:

• Vehicle (20 columns)
• Boat : Vehicle (another 10 columns)
• Car : Vehicle (another 15 columns)

Mandatory is obtaining a searchable DbSet<Vehicle>. TPH and TPC make no sense for me in here. Lack of Complex Types makes a mess in the code-first models. Hearing the EF6 is going to be kicked out of .NET Core before EF Core provides answer to those very elementary features, questions the entire advantage of .NET Core in many situations when MVC 5 was robust enough.

[soeron]

+1 For this feature.
It is very needed and I think it should have been prioritized much earlier. I really hope to see some action on this for the current 2.0 timeframe. Even some very optimistic and basic functionality can unblock current and new projects to continue development while the feature is completely implemented. This issue is becoming a show stopper.

[kakone]

I'm really curious to know the reasons to consider class table inheritance (or TPT) like an antipattern. You can use TPH, TPC or TPT, each one of them has its pros and cons. I think these three basic inheritance mapping strategies should be implemented. If you want to use an ORM on an existing database that uses TPT, it's difficult to choose EF Core because of this lack.

[imuni4fun]

I do hope this gets some attention soon. TPT really is a critical requirement when consistency is more important than raw throughput. There are often parts of an application that need throughput and should be optimized for that use case... at the same time, other parts can be fairly slow by comparison but it's critical that they be really concise and accurate and worth the wait. Tracking various types of transactions can be one example. It's more important that it's right than it's fast... as opposed to total 'likes', search results, etc. TPH can be sloppy by comparison as it prevents the specification of constraints possible with TPT.

[JanEggers]

im thinking of building tpt myself as this doesnt get any love for years. currently im using ef 6 and cant migrate because this feature is missing. problem is i dont really know what it takes to make it work. i guess i have to build some EntityTypeBuilder extensions to generate the correct metadata, but besides of that i could not find any code that needs to be touched but i guess the query visitors must be modified too but its just guessing....

[fernandojs]

Unfortunately, without TPT support the EF7 is stopping the upgrade for lot of apps in the .netCore world.

[jimmymain]

On Fri, 28 Jul 2017 at 4:09 PM fernandojs [e-mail] wrote:

Unfortunately, without TPT support the EF7 is stopping the upgrade of lot
of apps in the .netCore world.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFramework/issues/2266#issuecomment-318661832,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ACb8qtl9cFDadOSJYFrMJdWHD7hOaX1Tks5sSeuwgaJpZM4EnOQ5
.

Yes it is - come on guys - give us a roadmap. EF6 with its few frustrations
really rocks compared to the competition. Particularly for the .Include
method.

Give us some guidance.

[gravbox]

I am trying to move the nHydrate modeler forward to EF Core and the main stumbling block is no TPT. I need this to make the EF Core generation compatible with the EF6 implementation. This seems so simple, why can you not just join the tables in the hierarchy on the PK and be done with it? How is a database join an anti-pattern?

[cubikca]

Guess this isn't ready for production use yet. I'm not going to rip apart my existing database (despite its design flaws) to accommodate the lack of EF Core features. Maybe when 3.0 comes around it'll be useful. Too bad, because .NET Core adoption is important to us, but not to the point of constantly working around missing features.

[marchy]

EF Core is far from being ready for real production use. Best advice: stay the heck away from it and keep using EF6. Your business will thank you for it.

PS: This also means you cannot use .NET Core and must use .NET Framework – which means you cannot dev on MacOS for anything other than research/hobby projects. The chain of dependencies is still bottlenecked :(

[cubikca]

Not using .NET Core is not really an option when developing microservices that have a small footprint. The full .NET stack is too large to create a reasonably-sized container.

I've had some success by mapping an entity model to a domain model. This seems to be the direction that many people are going, and there's a lot to recommend this approach. It is, however, more work. In general, though, it's not going to be possible to force a good DDD model into an ORM. EF Core just makes this fact a little more obvious...

[marchy]

@cubikca Great point on the microservices part. Another way to put this is that microservice arch on .NET is not yet feasible in practice/production systems.

What do you mean by mapping entity model to domain model though? Do you mean having two separate model layers? If this is the case it's directly against the EF code-first goals/spirit (ie: to squish your Data Access layer and Modelling layer into one so that you don't have to maintain two layers and mapping between them) – which is quite a large architectural complexity/cost increase. I think it is entirely the EF's team goal to prevent the need for these kinds of approaches – which at least in the EF6 world they had achieved.

[rorymurphy]

It's disappointing to see that TPT and TPC are still not being prioritized and, given how long the issue has been open, it's tough to tell if it's actually coming or doomed to sit in purgatory forever.

Several good arguments for TPT & TPC have already been made but I'll throw in even another - TPH prevents you from being able to extend a DbContext in another assembly and add a child class of one of the entities (since there could be multiple assemblies doing this same thing, none of which can know about all the columns). It's not the microservices way of doing things, but can be useful, particularly if you don't control the code for the other assembly.

I won't argue that one is better than another, but each has its uses, and the opinionated stance that TPT and TPC are "nice to haves" and not really necessary is hurting adoption without a doubt.

[jimmymain]

What's completely nuts is that I discovered that server side group by
wasn't even implemented until recently.

Imagine discovering all your grouping was being done "client" side a couple of weeks into a project.

Sorry - but EF Core is nothing more than a POC toy. It's not production
ready at all.

On Sat, 26 Aug 2017 at 7:33 AM rorymurphy [e-mail] wrote:

It's disappointing to see that TPT and TPC are still not being prioritized
and, given how long the issue has been open, it's tough to tell if it's
actually coming or doomed to sit in purgatory forever.

Several good arguments for TPT & TPC have already been made but I'll throw
in even another - TPH prevents you from being able to extend a DbContext in
another assembly and add a child class of one of the entities (since there
could be multiple assemblies doing this same thing, none of which can know
about all the columns). It's not the microservices way of doing things, but
can be useful, particularly if you don't control the code for the other
assembly.

I won't argue that one is better than another, but each has its uses, and
the opinionated stance that TPT and TPC are "nice to haves" and not really
necessary will hurt adoption without a doubt.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/2266#issuecomment-325089259,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ACb8qmnHm-zNwGRXLlwgCTk-lmr2e8SEks5sb64vgaJpZM4EnOQ5
.

[simon-tamman]

Remember that in many cases we don't get to choose our schema so this is a sticking point for transition.
God knows what I'm going to have to do now seeing as we haven't got it. Some sort of gash composition shenanigans I guess. Ugh, my model is gonna look ugly as sin which then means I have to work harder at serialisation too because the default is going to be inefficient and wrong.
All of this is painful so it would be good if it wasn't.

[ajcvickers]

EF Team Triage: We really appreciate the recent feedback on this issue. Feedback from developers is extremely important in determining how resources across the team are assigned. However, please keep in mind that that feedback is only one of several inputs into the planning process and that--like in all software projects--resources are limited and every decision to implement a specific feature usually involves postponing others.

To shed a little more light onto our planning process we have created some documentation on the common considerations we take when deciding what to work on next.

We read and consider all feedback (and try to make the right choices based on it) no matter how it is delivered; that is, whether it is provided politely and with respect or not. Yelling at us at best makes you feel good and us feel bad. It doesn’t achieve anything concrete. We are doing our best to prioritize features and implement them in the best way we can. We really appreciate your feedback and it makes a big difference on shaping the product. We personally appreciate it more when feedback is delivered in a respectful way, but please don’t stop providing the feedback.

[AndriySvyryd]

The implementation could be simplified if we used a database model #8258

[wcc-neal]

Wait, this is still not available in EF core 2 ? Consider this another vote - pretty much required for our use case.

[mohammadsj]

I just found the need to TPT and ended up to this discussion. I'm telling myself: "Welcome to the world of open source".
We should learn to be patient.

[JauernigIT]

I just stumbled across this thread while searching for TPT in EF Core. Thanks to official Microsoft documentation, it's not obvious which types of inheritance are currently supported...

Now I found out here that TPT is not supported in EF Core, even not in v2.0. ARE YOU SERIOUS?! In my eyes, it's a crucial and base ORM feature. To still not have it is ridiculous (sorry to say this).

[sthiakos]

+1 For Me - Note to all that you should "thumbs up" the initial post by @satyajit-behera to vote up this issue. This and TPC is how DB's are/should be modeled.

[sinjens]

Also came here from Google searching for TPC/TPH inheritance model. In my last business system working for another company I had to make my own ORM due to TPT and performance, I really thought I wouldn't have to do that again. I'm with another company now, so I can't copy my old ORM either. EFCore seems very nice and to the point and I was in the middle of modeling and writing controllers when I suddenly realized that the only inheritance implementation is TPH :( That breaks my entire model. I'll have to look for another ORM now :(

[crozone]

I understand the need for this feature, but is writing an ORM really easier than working around it for now? Currently you can fake TPT by using composition, the current TPH, and non-mapped properties. Is it really such a big deal to either jump through a navigation property, or write boilerplate to do it for you?

[rorymurphy]

I'd say yes, it's a big deal to have to muck up the entire object model of your application to fit within the limitations of the ORM. Composition does not address all the same issues as inheritance - it's often not an option to swap one for the other.

[gravbox]

When you have a multi-million dollar project in the field that you cannot just re-write, it is kinda big deal that we cannot move to the next EF version. We have prepped everything for EFCore but we cannot move forward until this is addressed.

[e-davidson]

One of the benefits of EF core being open source is that you guys are free to implement it yourselves.
Just my 2 cents.

[fernandojs]

@e-davidson Yes, it's. But, this feature is so complex, otherwise it would has been developed. Then if you couldn't help people here, please, don't waste your time.
Your answer doesn't worth 2 cents.

[gravbox]

I guess I could rebuild my own car engine and re-roof my house too but why? Our business is building business solutions not ORM tools. We are not going to assign a developer to half-ass implement a custom solution that we then have to fully own and maintain forever. One depends on third party vendors to build the tools on which others then build more abstract software.

[marchy]

@fernandojs Agreed. Let's keep it constructive on here guys. We are all trying to steer the framework in the right direction and get the EF team the feedback they need to make the right priority calls. Currently this is a feature only THEY can execute and is blocking many of us from considering EFCore as a real alternative to EF 6.

Until then... let's hope that EF 6.3 can bring EF 6 to .NET Core so that we can at least have a migration path forward for our apps that are currently stuck on the full, heavy-weight .NET Framework (Issue here: https://github.com/aspnet/EntityFramework6/issues/271).

[jeffreypalermo]

At this point, the most complex applications I've built have used NHibernate. I look forward to when these can be converted to EF Core by updating the framework to 4.7.1 But there is a level of feature parity that has to ben achieved. What mappings should be included is a conversation that happened over the past 15 years. I believe those conversations have passed, and the industry has a body of knowledge built up through real usage of other ORMs. I believe EF Core should leverage this industry body of knowledge. http://nhibernate.info/doc/nhibernate-reference/inheritance.html#inheritance-strategies

[SchneMa]

Does anyone know a good workaround to use existing Databases with tpt inheritance?

[weitzhandler]

@SchneMa Entity Framework 6 😢

[ajcvickers]

Note: see also #10739

[milosloub]

Hello EF Core masters. Is there any progress in TPT or TPC? I'm developing enterprise financial system and several APIs are connected to IdentityServer. Each API has own type of user and each type is manipulated as complex hierarchy tree and joined to other related database tables. I can´t imagine to implement TPH only, because there are hundreds of thousands rows. Each user type has unique column structure ( from 10 - 40 unique columns against ancestor), so at this moment the result table is one big table with a lot of redundand optional empty rows ( most records comes from table with 10 unique columns).

Is there some hope that this is being implemented?
Thank you

[crozone]

@milosloub It's on the backlog of high priority features, but won't make 2.1:

https://github.com/aspnet/EntityFrameworkCore/wiki/roadmap

However, TPH might not be as bad as you think. The cost of TPH can be reduced to a single byte for the discriminator row (by using a byte backed enum), and then a single bit for each null-able column that isn't used. You're looking at ~6 bytes overhead per row, maybe 9 depending on the database and padding.

[jgonte]

Any news on support for TPT?

Taken from:
https://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt

TPT Advantages
The primary advantage of this strategy is that the SQL schema is normalized. In addition, schema evolution is straightforward (modifying the base class or adding a new subclass is just a matter of modify/add one table). Integrity constraint definition are also straightforward (note how CardType in CreditCards table is now a non-nullable column).

[brianjlowry]

Additionally, in terms of creating indexing, indexes work better when you include all columns of the table to prevent column lookup. Not implementing this properly prevents one of the most important performance enhancements for a database (index seek vs. scan) - beyond the idea of having 50-100 columns for a model that only needs 10.

[ajcvickers]

@jgonte @milosloub TPT support is something we plan to implement, but it is a big cross-cutting feature. I can't say that it will be in the next release following 2.1 because there are many other competing features that are also high priority. However, each release we also think about which building blocks we need to get closer to being able to support these big cross-cutting features. So we are getting closer, but it's going to take some more time.

[jgonte]

I understand that although, the entity framework 6 might provide a starting point:

Mapping the Table-Per-Type (TPT) Inheritance
In the TPT mapping scenario, all types are mapped to individual tables. Properties that belong solely to a base type or derived type are stored in a table that maps to that type. Tables that map to derived types also store a foreign key that joins the derived table with the base table.

modelBuilder.Entity().ToTable("Course");
modelBuilder.Entity().ToTable("OnsiteCourse");

Meanwhile IMHO using EF Core for serious DDD or best practices development besides creating a Blog or a ToDo app is impossible.

I just hope you realize that and move it up if possible in the priority queue :-)

[kakone]

Meanwhile IMHO using EF Core for serious DDD or best practices development besides creating a Blog or a ToDo app is impossible.

Your domain model should be separated from your persistence model and should not depend on any ORM. You could have all inheritance you want in your domain models, and use EF Core only for persistance, nothing else. It works. Separating has a cost (more coding, more classes to maintain, ...), but it's not impossible.

[jgonte]

@kakone I am kind of lost here ...

If I use EF Core for persistence, nothing else ... but it does not support TPT mapping ... it works???

So the EF Core masters do not need to work on any issue anymore, since the persistence model should not depend on any ORM?

[brianjlowry]

@kakone It doesn't persist it in a manner that most would consider correct and forces those using a TPT mapping into poor DB design, by default.

[kakone]

If I use EF Core for persistence, nothing else ... but it does not support TPT mapping ... it works???

Yes, it works. There are one table per type in my database and I use EF Core, but my persistence models must use composition because of lack of TPT support in EF Core. My domain models uses inheritance and I do the mapping between persistence and domain models.

So the EF Core masters do not need to work on any issue anymore, since the persistence model should not depend on any ORM?

No, it would be really useful to have TPT (and TPC) inheritance in EF Core. I only say that it's not impossible to do serious DDD with EF Core because serious DDD should not depend on any ORM :
http://www.mehdi-khalili.com/orm-anti-patterns-part-4-persistence-domain-model
http://enterprisecraftsmanship.com/2016/04/05/having-the-domain-model-separate-from-the-persistence-model/
http://blog.sapiensworks.com/post/2012/04/07/Just-Stop-It!-The-Domain-Model-Is-Not-The-Persistence-Model.aspx

[brianjlowry]

@kakone I don't use a persistence model, and I still have zero persistence details within my domain model (code-first / POCO). Adding another layer to translate just seems like unneeded overhead. You are defeating your own argument by saying that you need a completely separate set of classes just for the ORM - what happens if you switch ORMs? Are you going to have to completely change the persistence models now to fit into nHibernate / insert ORM?

My POCOs can work with any ORM as-is - I share this guy's viewpoint from one of your links: http://disq.us/p/1idhyw4

[kakone]

@brianjlowry I'm just saying that in DDD, you can use EF Core without TPT support, no matter how the database is built because you can use composition instead of inheritance in your persistence models. I use TPT in my database and I use EF Core. OK, I would prefer to have TPT support in EF Core, it would be simpler, but, currently, I work without.

[brianjlowry]

@kakone I understand that it "works", but it leads to an absolutely terrible DB design which harms performance drastically (see my earlier comment about indexes) - especially in scenarios where you have many parents that inherit a model (like a generic Person class).

Using composition instead of inheritance is exactly the opposite of DDD. You are now building your models to suit the shortcomings of your ORM. I didn't have this problem before moving to Core - it worked fine.

[JanEggers]

cmon guys dont troll this thread

[jgonte]

I think two facts are drawn from this discussion . Either:

• Best practices can be straightforward if EF Core implements TPT or
• It still can achieved Separating has a cost (more coding, more classes to maintain, ...),

So please prioritize this feature so we can do it the best way
Thanks

[nero-philip-wang]

we really need this feature. 0.0

[lloydpowell88]

Does anyone have a suitable alternative to achieve TPT concepts, ideally I don't want to use TPH because I don't want 100s of columns as my solution grows, is there a 'tidy' solution in which this can be achieved.

Seeing as this has been in the priority list for nearly 2 years I guess it's not coming any time soon which is a big shame because if it's not achievable :-(

[simon-tamman]

@lloydpowell88 Do some sort of CQRS? I.E. Make the read and write models are different so you can denormalise for the reads.
It doesn't really solve the problem as you can't use TPT properly for the writes but it mitigates the issue slightly by introducing another. :D

[jgonte]

The issue is not to map all the inheritance tree to a single table. That is an absolute lack of normalization and the best practices in database design is to normalize the data ... as simple like that

[simon-tamman]

best practices in database design is to normalize the data

For standard-traffic source databases sure but I'm asking the question if the user needs to see the actual direct source. You can project denormalised projections of your data and map to those.

[jgonte]

I don't think the user needs to see that. For that we have the view model mapping, but still Entity Framework, at the entity level, accesses a database that needs to be designed with normalized data and at this stage, for inheritance, EF does not allow that

[SPEMoorthy]

I am waiting see TPT And TPC in EFCore as soon as possible. It is really need

[ignusin]

Wow, I got hit with that one too.
We were really considering the move to .NET Core with EF Core, but now it seems impossible without TPT.
Hopefully it will be released soon.

Small addition: after two weeks of attempts, we were able to move our synchronization services to Linux/Ubuntu, and it works great!
TPT inheritence in DB was patched and became TPH.
Amazing, we finally got to the point where .net code can run on linux with no issue, amazing!
Thank you NETCore team!

[seroche]

Do we have an ETA for this? I can see it's not even included in the roadmap for 2.1.

[Tyology]

It's frustrating running into project showstoppers with EF Core. I would consider no support for TPT to be one of them for certain types of projects. The column bloat and lack of enforcement by using TPH outweigh the performance gain. Not to mention we would lose the flexibility of working with separate types.

+1 for TPT please, I understand there are drawbacks (maybe some unforeseen) to TPT vs TPH, but I would not take that to mean TPT is an anti-pattern. There are definitely drawbacks to TPH that TPT alleviates. It would be great to be able to have a choice between tradeoffs if it means the project will use EFCore or not at all.

Edit: I think a fellow developer summed it up best for me. Until EF Core decides to put it's big boy pants on and support enterprise level business models, it's only appropriate for small personal projects.

[bugproof]

What about having a couple of DbSet like:

public abstract class Person
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class SomeOtherPerson1 : Person
{
    public int MySpecialProperty1 { get; set }
}

public class SomeOtherPerson2 : Person
{
public int MySpecialProperty2 { get; set }
}

public DbSet<SomeOtherPerson1> OtherPerson1 { get; set; }
public DbSet<SomeOtherPerson2> OtherPerson2 { get; set; }

I've tested it and it seems to create separate tables with columns of properties inherited from Person.

What is wrong with this approach? Is this "TPT" ?

Using

public DbSet<Person> People { get; set; }

however, results in the TPH and the problem described in this issue?

[weitzhandler]

What is wrong with this approach? Is this "TPT" ?

It isn't TPT. It's maybe TPC.

TPT is where 3 tables are created: People, Person1, and Person2, where the latter two are joined with the first.

So with TPT, you have a DbSet<Person> which you can then filter out appropriately (or not).

[bugproof]

@weitzhandler yeah I just tested it and with TPT it was relating derived tables with base table using foreign key. While the approach above was just creating tables for derived types (no table for base type). And tables of derived types contain columns of base type.

[blecalex]

@rowanmiller for when is TPT planned? We really need this feature. And apparently we are not the only ones. Thanks for your news!

[ajcvickers]

@blecalex This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[JanEggers]

@ajcvickers if you sort issues by upvotes this issue is the second on the backlog list. so from my point of view it should be the second item that is done for the next release. if you have different measures on priority please share. this issue needs to be solved asap.

[peppinho89]

@rowanmiller We need this features. Please prioritize.

[ajcvickers]

@JanEggers The number of upvotes is not the only factor, as is explained in our docs on the release planning process. We recognize that TPT is a very important feature for many people. Unfortunately, it's also one of the more cross-cutting features that requires work in many areas, which makes it difficult to fit into a schedule. We do our best to schedule work appropriately, and let me restate that we understand and take into consideration that TPT is an important feature whenever we prioritize work.

[simon-tamman]

@ajcvickers as much as that makes sense there is often a danger in an objective approach that one misses the woods for the trees. If the release planning process fails to imbue its thought process with subjective strategy as well as objective rationality then this feature will never get done.
Sorry to comment on what is none of my business its just that type of thinking is something that I feel some of the places I work struggle with and ultimately just spin into a decline of only making "sensible decisions" that in context make sense but from a much broader perspective (strategic) do not.

[rainer-helbing]

For years I'm stuck to the "old" .NET framework, because of missing features like TPT and spatial data types in EF core. So there's no way to use Linux (which Microsoft loves) and use effectively new technologies like Docker.

Please add TPT

[bmonsterman]

I think labeling TPT as an anti-pattern is very broad and a bit dogmatic. There are situations where TPT is not a good fit for sure, but there are also situations where it fits perfectly. It's likely that someone might design something in lieu of TPT that carries the same cost (or worse), but is not as clean. For the EF team to discard the support of this pattern because of the potential of performance/scalability problems in it's implementation seems a bit overbearing.

[jimmymain]

Its quite simply not an anti-pattern:
https://en.wikipedia.org/wiki/Third_normal_form

Kind Regards Craig
Palantir (Pty) Ltd http://www.palantir.co.za/
https://github.com/jimmymain https://www.facebook.com/craigma
https://www.twitter.com/@jimmymain
https://stackoverflow.com/users/231821/jim
https://www.linkedin.com/in/craig-main-b768051/

On Thu, May 10, 2018 at 10:33 PM, Bryan Martin [e-mail]
wrote:

I think labeling TPT as an anti-pattern is very broad and a bit dogmatic.
There are situations where TPT is not a good fit for sure, but there are
also situations where it fits perfectly. It's likely that someone might
design something in lieu of TPT that carries the same cost (or worse), but
is not as clean. For the EF team to discard the support of this pattern
because of the potential of performance/scalability problems in it's
implementation seems a bit overbearing.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/2266#issuecomment-388176863,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ACb8qva8OEcDbFlKO-nwu5lwCI6gbxBDks5txKQzgaJpZM4EnOQ5
.

[taspeotis]

I quite like how EF Core is lighter and faster, but the reality is I have existing databases that are NOT managed by Entity Framework and the schema is ... well I get what I'm given. With EF Core I have to compromise.

At least there is some nebulous information about EF 6 coming to .NET Core over here so some day soon we can eschew EF Core's lightness-but-with-shortcomings for EF 6's maturity.

[mglgmz]

We are moving our application from .net to core and our database uses TPT a lot. What we are doing now to make EF Core work with that is map the inheritance as navigation properties of the base class, so we can load the entities from the DB and then map it to our business model that does use inheritance.
Not what we would like to be doing, but what we can do until this is in place.

so instead of:
BaseEntity
DerivedEntity:BaseEntity
we are doing:
BaseEntity
DerivedEntity Derived
and we map the FK using EntityConfiguration

[milosloub]

@mglgmz That sounds interesting as temp solution. Can you provide some example with EntityConfiguration? Personally we map Base and "Derived" table to our business model structrure using AutoMapper.

[mglgmz]

@milosloub Sure

Having the following scenario where ElementA and ElementB Inherit from Element

public class Element {
    public int Id { get; set; }

    //other base props

    //map derived types as properties
    public virtual ElementA ElementA { get; set; }
    public virtual ElementB ElementB { get; set; }
}

public class ElementA {
    public int Id {get; set; }
}

public class ElementB {
    public int Id { get; set; }
}

//Configuration:
public class ElementEntityConfiguration : IEntityTypeConfiguration<Element>{
    public void Configure(EntityTypeBuilder<PackageElement> builder)
    {
        builder.HasKey(b => b.Id);

         builder.HasOne(b => b.ElementA)
            .WithOne()
            .HasForeignKey<ElementA>(ea => ea.Id);
        builder.HasOne(b => b.ElementB)
            .WithOne()
            .HasForeignKey<ElementB>(eb => eb.Id);

        builder.ToTable("Element");
    }
}
//Element A and B only need to map the Id and the table
public class ElementAEntityConfiguration : IEntityTypeConfiguration<ElementA>
{
    public void Configure(EntityTypeBuilder<ElementA> builder)
    {
        builder.HasKey(b => b.Id);
        builder.ToTable("ElementA");
    }
}
public class ElementAEntityConfiguration : IEntityTypeConfiguration<ElementB>
{
    public void Configure(EntityTypeBuilder<ElementB> builder)
    {
        builder.HasKey(b => b.Id);
        builder.ToTable("ElementB");
    }
}

I then created the following Model

public abstract class ElementModel {
    public int Id {get;set;}
    //other needed props
}
public class ElementAModel : ElementModel{
    //elementA props
}
public class ElementBModel : ElementModel{
    //elementB props
}

and when I load the entities from the database I have code to check the navigation
properties like

if (elementEntity.ElementA != null) GetElementAModelFrom(elementEntity);
else if (elementEntity.ElementB != null) GetElementBModelFrom(elementEntity);

It's a little awkward, but get's the job done

[tuespetre]

@AndriySvyryd @divega is there anything I can do to help here? Perhaps the relational Update piece could be expanded to support such a model and be tested with ad-hoc models so that later the Metadata, Query, and Migrations could grow into it?

[AndriySvyryd]

@tuespetre I will investigate and prototype the model changes to give us a better idea what needs to be updated. If there is any work that we can split out we'll create separate up-for-grab issues.

[markusschaber]

PostgreSQL as an object-relational database supports inheritance on the table level (https://www.postgresql.org/docs/current/static/tutorial-inheritance.html). I don't know whether other databases also support this kind of inheritance, but it could map very well to C# as it's also single-inheritance, and thus could be exposed in EF Core. It should deliver both optimal speed and space usage in most cases, thus performing better on average than TPC, TPH, TPT or the hackish "Use a single table for the base class, and stash all additional fields from subclasses in a JSON column" way.

Edit: I just learned that this is already tracked: #10739.

[roji]

@markusschaber PostgreSQL table inheritance is already tracked by #10739.

[insylogo]

This is incredibly important and blocking about half of the work I'd like to do on a replacement of an existing project with a .NET Core version. Is there anything that us mortal github contributors can do to help streamline this feature?

[AndriySvyryd]

@insylogo Once #12846 is done we'll consider what is the best way to break down the remaining work in manageable pieces.

[RowlandShaw]

In our case we were looking to move our application over to .Net Core/EF Core, but as the existing database used TPT, this is a blocking issue, without having to migrate all the existing data, and ancillary tooling/reporting at the same time.
Brings in to question the financial viability of migrating to netcore and EF.Core :(

[SergeyLimonov]

@RowlandShaw While waiting when TPT will be implemented you can try to use composition on data layer instead of inheritance but with some restrictions (for example you can't querying concrete entities from "base" table and inheritance is available only by interface types). Please see my example:

`
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;

namespace CompositionExample
{
#region IUser

public interface IUser
{
    Guid Id { get; }
    string UserName { get; }
}

public class User: IUser
{
    public Guid Id { get; set; }
    public string UserName { get; set; }

    public List<Content> ContentObjects { get; set; }
}

#endregion

#region BaseClasses

public enum ContentType
{
    Undefined = 0,
    ContentAlias = 1,
    UserProfile = 2,
}

public interface IContent
{
    Guid Id { get; }

    ContentType Type { get; }
    string Name { get; }

    Guid OwnerId { get; }
    IUser Owner { get; }
}

public class Content: IContent
{
    public Guid Id { get; set; }

    public ContentType Type { get; set; }
    public string Name { get; set; }

    public Guid OwnerId { get; set; }
    public User Owner { get; set; }
    IUser IContent.Owner => Owner;
}

public abstract class ContentBase: IContent
{
    protected ContentBase()
    {
    }

    protected ContentBase(Content content, ContentType type)
    {
        if (content.Type == ContentType.Undefined)
            content.Type = type;
        else if (content.Type != type)
            throw new ArgumentException(
                $"Content object should be created with content type = '{type}' or '{nameof(ContentType.Undefined)}' but was not.",
                nameof(content));

        Id = content.Id;
        Content = content;
    }

    public Guid Id { get; set; }

    public Content Content { get; set; }

    public ContentType Type { get; set; }
    ContentType IContent.Type => Type;

    public string Name { get; set; }
    string IContent.Name => Name;

    public Guid OwnerId { get; set; }
    Guid IContent.OwnerId => OwnerId;
    IUser IContent.Owner => Content?.Owner;
}

#endregion

#region IContentAlias

public interface IContentAlias: IContent
{
    Guid TargetContentId { get; }
}

public class ContentAlias: ContentBase, IContentAlias
{
    public ContentAlias()
    {
    }

    public ContentAlias(Content content)
        : base(content, ContentType.ContentAlias)
    {
    }

    public Guid TargetContentId { get; set; }
}

#endregion

#region IUserProfile

public interface IUserProfile: IContent
{
    string Nickname { get; }
}

public class UserProfile: ContentBase, IUserProfile
{
    public UserProfile()
    {
    }

    public UserProfile(Content content)
        : base(content, ContentType.UserProfile)
    {
    }

    public string Nickname { get; set; }
}

#endregion

#region Database Context

public class ThisDbContext: DbContext
{
    public DbSet<Content> ContentObjects { get; set; }

    public DbSet<ContentAlias> ContentAliases { get; set; }
    public DbSet<UserProfile> UserProfiles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Content>(builder =>
            {
                builder.ToTable("ContentObjects")
                    //.HasDiscriminator<int>(nameof(Content.Type))
                    //.HasValue<ContentAlias>((int) ContentType.ContentAlias)
                    //.HasValue<UserProfile>((int) ContentType.UserProfile)
                    ;
                builder.Property(e => e.Name).HasMaxLength(128).IsRequired();
                builder.HasOne(e => e.Owner).WithMany(e => e.ContentObjects).HasForeignKey(e => e.OwnerId).HasPrincipalKey(e => e.Id);
            });

        modelBuilder.Entity<ContentAlias>(builder =>
            {
                builder.ToTable("ContentAliases");
                builder.HasOne<Content>().WithMany().HasForeignKey(e => e.TargetContentId).HasPrincipalKey(e => e.Id)
                    .OnDelete(DeleteBehavior.Restrict);
                builder.HasOne(e => e.Content).WithOne().HasForeignKey<ContentAlias>(e => e.Id).HasPrincipalKey<Content>(p => p.Id)
                    .OnDelete(DeleteBehavior.Cascade);
            });

        modelBuilder.Entity<UserProfile>(builder =>
            {
                builder.ToTable("UserProfiles");
                builder.Property(e => e.Nickname).HasMaxLength(64).IsRequired();
                builder.HasOne(e => e.Content).WithOne().HasForeignKey<UserProfile>(e => e.Id).HasPrincipalKey<Content>(p => p.Id)
                    .OnDelete(DeleteBehavior.Cascade);
            });
    }
}

#endregion

#region User Profile Store

public interface IUserProfileStore
{
    IQueryable<UserProfile> GetAll();
    UserProfile TryGet(Guid id);
    UserProfile Get(Guid id);

    UserProfile Save(UserProfile entry);

    UserProfile Delete(UserProfile entry);
    UserProfile Delete(Guid id);
}

public class UserProfileStore: IUserProfileStore
{
    public UserProfileStore(ThisDbContext dbContext)
    {
        DbContext = dbContext;
    }

    public ThisDbContext DbContext { get; }

    public IQueryable<UserProfile> GetAll()
    {
        return DbContext.UserProfiles.Include(e => e.Content);
    }

    public UserProfile TryGet(Guid id)
    {
        return GetAll().FirstOrDefault(e => e.Id == id);
    }

    public UserProfile Get(Guid id)
    {
        UserProfile result = TryGet(id);
        if (result == null)
            throw new InvalidOperationException($"Can't find user profile with id = '{id}'.");
        return result;
    }

    protected T Modify<T>(Func<T> action)
    {
        T result = action();
        DbContext.SaveChanges();
        return result;
    }

    public UserProfile Save(UserProfile entry)
    {
        return Modify(() =>
            {
                EntityEntry<UserProfile> entityEntry;
                if (Equals(entry.Id, Guid.Empty))
                {
                    entry.Id = Guid.NewGuid();
                    entityEntry = DbContext.UserProfiles.Add(entry);
                }
                else
                {
                    entityEntry = DbContext.Entry(entry);
                    entityEntry.State = EntityState.Modified;
                }
                return entityEntry.Entity;
            });
    }

    public UserProfile Delete(UserProfile entry)
    {
        return Modify(() => DbContext.UserProfiles.Remove(entry).Entity);
    }

    public UserProfile Delete(Guid id)
    {
        UserProfile entry = TryGet(id);
        return entry == null ? null : Delete(entry);
    }
}

#endregion

}
`

[RowlandShaw]

One of our use cases is loosely:

class Place {...}
class Depot : Place {...}
class CustomerAddress : Place {...}

And then, we rely on the polymorphism to be able to do stuff like

Place toPlace = ...
CustomerSite cs = toPlace as cs;
if (cs != null)
{
   DoStuff(cs);
}

Appreciate this could be rewritten something like

Place toPlace = ...
if (toPlace.PlaceType == PlaceType.CustomerSite)
{
   CustomerSite cs = context.CustomerSite.SingleOrDefault( c=>c.Id == toPlace.Id)
   DoStuff(cs, p); // Or change DoStuff() to follow the relationship
}

Or

Place toPlace = ...
if (toPlace.PlaceType == PlaceType.CustomerSite)
{
    CustomerSite cs = toPlace.CustomerSite;
   DoStuff(cs, p); // Or change DoStuff() to follow the relationship
}

But that's a lot of code to change just because we've chosen TPT historically

[SergeyLimonov]

@RowlandShaw in any case you can use inheritance in interfaces so you can define following interfaces
public interface IPlace {}
public interface IDepot: IPlace {}
public interface ICustomerAddress : IPlace {}

You can't use your old sources without specific to .NET Core modifications because it differs from .NET Framework in some cases.

[IngbertPalm]

Any news about this feature?

Since the planning of version 3.0 o .NET Core has already started, I'm asking me if this feature will be included in version 3.0 of EF Core or if this feature will be a sort of Vaporware :-)

It would be nice if anyone from MS could give a statement when this feature will be integrated.

[ajcvickers]

@IngbertPalm TPT will not be included in EF Core 3.0, since we don't have the resources to fit it into the schedule for 3.0. We will reconsider the backlog after 3.0, but I can't give any kind of concrete answer as to when we will get to TPT given the number of issues on the backlog compared to the number of resources working on EF.

[weitzhandler]

😢

But at least thanks for the update.

[IngbertPalm]

@ajcvickers Thank you for the update!

[Deilan]

[AlonCG]

Sorry about the rant. I wish I was in a different position.

This request has been floating around for over 3 years now and it still gets "backlogged" and postponed release after release ... with no end in sight. This is the single reason prohibiting us from moving our code-base to DotNet Core (and I did not want to re-write currently working code based on a blog post!).

I've been a Microsoft-centric developer since 1997, and this is my biggest disappointment (luckily I did not invest in SilverLight!). I wish I could have just dropped EF altogether, though hindsight is always 20/20.

Now ... with the announcement that EF 6 will work with Dot Core 3, I presume this will ultimately just be booted permanently. This was only mentioned briefly in a community stand-up and I have not seen any other detailed information. So if this is true, that would mean that we would not need TPT in EF core, and we would continue to use EF 6? Anyone able to shine any additional light on that concept? (Or share links where I may have missed?)

[ajcvickers]

@AlonCG "I presume this will ultimately just be booted permanently." This is not the plan. I am intentionally being very honest and open with what is going on, which I why I am saying TPT will not be coming soon, even though it pains me to say it and I know its not what people want to hear. So when I say that we do still plan to bring TPT to EF Core I am also being honest and open.

EF Core and EF6 are different products implemented differently. EF Core already has many capabilities that do not exist in EF6, but it is also missing some things that EF6 can do, such as TPT. Most of the EF team, even though it is a small team, are working to make EF Core better, so this is where the improvements and new capabilities will come.

However, because EF Core is a different product it does behave differently and have different characteristics from EF6. For people with existing, running applications that use EF6, porting to EF Core can be a fair amount of work even when the features are available. We've worked with many customers who have done transitions from EF6 (and LINQ to SQL) to EF Core and been successful with it. This is worth doing for an application that continues to be in active development since it can take advantage of the additional capabilities of EF Core going forward.

For an application that is essentially done and uses EF6, but which is being ported to .NET Core, it may be more pragmatic to continue using EF6. That's why we are bringing EF6 to .NET Core. It doesn't mean that in general investment is switching to EF6.

[AlonCG]

@ajcvickers Thanks for the response and I do appreciate what you (and your team) accomplishes. As well as your openness and honesty.

I do understand that EF 6 and EF Core are different products and the recommended guidance for using which one at which time. Initially the guidance was to use EF 6 if we needed a feature that was not implemented in Core. Had I thought initially that TPT in EF Core would keep getting booted down the line, I could have possibly removed any dependencies on EF 6 then and work around our usages of TPT.

While I understand not bringing TPT to EF Core is not in the plan ... I really have not seen any serious movement from the Microsoft side (other than possibly your commitment) to really take this request into consideration. (Just read the comments, "anti-pattern"!) Given the following scenario that (it appears to be that):

Dependencies on EF 6 are keeping projects similar to mine from migrating to Dot Net Core.

Once EF 6 gets ported to Dot Net Core 3.0, what stimulus does Microsoft then have to implement TPT in EF Core? (This is what I was trying to ask in my initial "rant".) By then another year or so would have passed and a new discussion may arise such as .. ."why should I be using 4+ yr old tech in my application?"

I do hope to be proved wrong and again, thanks for all the work you and your team does accomplish!

[ajcvickers]

@AlonCG I understand your concerns. First, on TPT itself, there is a feeling among some that this is an anti-pattern, and earlier in the evolution of EF Core there was a desire for it to be more "opinionated" than EF6 and possibly more aligned to greenfield projects where the structure of the database was not already fixed. However, for at least the last couple of years we have acknowledged that there is significant demand for this kind of mapping and that we should support it in EF Core. This is somewhat like lazy-loading, which is supported in EF Core even though many people believe it is an anti-pattern precisely because many other people do not believe this and want to use it. So on the team we are committed to TPT, and we discuss it quite frequently.

With regard to why we would invest in TPT if EF6 runs on Core, the answer is that because EF6 still has all the limitations and baggage that caused us to start work on what became EF Core in the first place. For example, value/type conversions (which don't align well with the rigid EDM type system) are coming along very nicely in EF Core--there are still some issues in SQL translation, but generally they are working well and are opening up many scenarios on EF Core that are not possible on EF6. Going forward the gap between what EF6 can do and what EF Core can do will only get bigger, and telling people to use EF6 if they need TPT won't cut it because then they won't get all the other stuff they need in EF Core.

All that being said, whenever we talk about anything beyond the next release, and certainly when we're talking a year or two out it is important to remember that things can change. Strategic shifts and even tactical changes could result in changes in investment and/or priorities. However, right now I get the clear impression from management that there will be continued investment in EF Core going forward, and that TPT will be part of that.

[tjad]

Bump - this is important!

@ajcvickers Glad that TPT is being considered - hope it makes the next release!

EFCore is opinionated in that it persistently only presents 1 option as a strategy despite demand that is acknowledged for an alternative strategy.

TPT being an anti-pattern is an opinion, and that opinion may be valid in some scenarios.
Applying TPT to avoid sparse tables as a strategy is effective and provides application performance gains. Joins aren't an issue at all in context of this strategy or for database technology today. In context of the data model complexity we create today, this certainly counts heavily against EFCore as a choice of ORM for many applications.

And yes, I do acknowledge TPH is a good default strategy.

[tjad]

Do we have any PRs for this ? Is there an understanding of what's required to be done that can be shared to get more involvement on this?

[tjad]

And here's a workaround to at least achieve the correct database structure using a compositional coding style.

https://weblogs.thinktecture.com/pawel/2018/05/entity-framework-core-inheritance-tpt-is-not-supported-is-it-part-1-code-first.html

Note: Ideally we'd have native support.

[otabekgb]

Is TPT an anti-pattern??? TPH maybe, in a medium to large project, it gets messy.
Performance definitely is not a real issue since IDs will be indexed.
I hope this issue gets a milestone assigned soon, please. For most it is probably the cause not to choose EF, look at the reactions.

[insylogo]

Between this missing feature and no WCF services, I don't see why Microsoft expects anyone to migrate to .NET Core. Database normalization is not an "anti-pattern".

If I were in charge over there, I wouldn't even allow anyone to touch a document database while they still haven't implemented relational functionality for this ORM. That this issue isn't on the roadmap is a major clue to everyone not to adopt Core.

[bproven]

One of the main issues stopping us from migrating to Core.

[tcfialho]

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.

"Good pattern" for EF Core team is many-to-many with "join entity class" workaround. LOL

[ajcvickers]

I've updated the comment from 2015 to indicate that this no longer represents the feeling of the team.

[pantonis]

@ajcvickers Any estimate on this? Crucial, critical super important missing feature.

[ShahroozAnsari]

We need this feature asap.

[chrisdpratt]

Where on the roadmap is this feature currently? It's frankly beyond belief that as we're rolling up to EF Core 3.0, something as basic as TPT inheritance still isn't supported. I understand that it's a small team and you have many priorities, but honestly, lazy-loading, as just one example, should have taken a backseat to this.

[Bailoni]

@rowanmiller
okay? we are starting a project and wanted to start with EF Core, but I'm concerned that I do not have TPT support, a requirement in my company, can you tell the version or an estimated deadline for this release?

[ajcvickers]

@Bailoni If I had to make my best guess right now, I'd say there is a reasonable chance it will get done in the next major version--that is 4.0. It's unlikely to get done before because we think some breaking changes, especially to database providers will be needed, as well as it being a major feature for a small team to take on. However, since 4.0 isn't planned yet and has no date it's hard to know for sure where things will be at or whether TPT will be included.

[insylogo]

Pretty funny. The second version of Entity Framework from 10 years ago had table-per-type normalization.

[weitzhandler]

:cry:

[antoinebj]

@ajcvickers

as well as it being a major feature for a small team to take on.

Does Microsoft not have enough budget to get the team to the appropriate size? It's starting to look like the original EF development had a higher velocity and more investment put into it.
It's kind of sad to see this issue go way back to nearly 4 years ago and reading the comments getting more and more caustic about this feature being first called an anti-pattern and then just pushed back indefinitely. And there are so many other interesting things in the backlog. It must be overwhelming for a small team, I really hope you're able to cope.

[chrisdpratt]

@antoinebj I was thinking the same thing. EF hasn't seen any major advancement since EF Core came on the scene, and while .NET Core 3.0 will natively support EF6, you can't use it with ASP.NET Core without going the .NET Framework route currently. Even then, EF6 doesn't support certain important things like connection pooling that EF Core does. Right now, Enterprise users are in an effective limbo with no version or flavor of EF supporting all Enterprise needs.

The question then is why is the EF Core team so small in the first place? Microsoft should be throwing it's full weight behind you guys so it can have a truly world class ORM solution of some sort or another. Do we need to start a letter writing campaign? Maybe one of the bravest of you guys can get us Satya's cell, so we can ring that off the hook.

[ajcvickers]

@chrisdpratt @antoinebj First, EF Core has made a lot of progress and supports many things that classic EF does not support. (However, connection pooling is supported by both.)

With regard to the size of the team, at some level there is a mapping between investment and strategic importance to Microsoft. Right now, this is the level of investment that is deemed to be appropriate. The number of votes, etc. for this issue has been explicitly raised with the people who make those calls, but as of yet there is no change to the investment. I'm not going to say that that decision is wrong because deciding where and how to strategically place investment is very difficult, and the people making these calls have much more information available than I do.

As a team, what we can commit to is:

• Continuing to make the feedback here and elsewhere on EF Core known to the decision makers.
• Continuing to do what we can with the resources we have and the feedback we get to make EF Core as good as we can.

[ds1709]

Maybe you should imlement some basic features first, such as TPT, many-to-many relations, entity splitting? It's funny to hear what EF Core "supports many things that classic EF doesnt", but also it DOES NOT supports many BASIC things that classic EF DOES, which must be implementet primarily in any ORM, whether it's EF6, EF Core, XAF or another. It's like a car with perfect interior, but without wheels. Yes, it's beautiful, but you can't ride it. Like this, EF Core, maybe it's realy supports many things that classic EF does not, but I can't use it without basic features implemented.

[papakojo]

wow, we were trying to design a big project and this issue stopped us dead in our tracks.
Just a question: can you provide examples/tutorials on how to implement TPH/TPC instead of TPT that involves about 10 types/classes?
I figured you guys may know something some of us haven't thought about if TPT is not a priority.
No sarcasm, just curious.

[tjad]

@ajcvickers My point is that if there is "not enough funding" or "not enough investor focus", then your team should have a dedicated strategy to get the community involved in supplying a PR - as in trying to coordinate this to happen at the very least. At the moment this is not happening, and I have tried to stimulate this route.
(And your investors should really make more of a priority in listening to the community - noted that you've raised this with them).

@papakojo take a look at the workaround I've posted, it may help, though you will still need to build your own ORM methods/integration for your TPT. ( https://github.com/aspnet/EntityFrameworkCore/issues/2266#issuecomment-446959727 )

[milosloub]

@tjad @ajcvickers Exactly! I think there is a lot of capable developers who can help with PRs to get ready some building blocks for the most important missing features like this.
So what about focus on guidance what is needed?

But I don't understand to steps of the release process
https://docs.microsoft.com/cs-cz/ef/core/what-is-new/roadmap#release-planning-process
It's obvious that the first point is mostly ignored. So if those lines are made for investors decisions, then all of them made very bad investment.

[emes001]

I'm not sure why I never bothered to look in EF Core supported TPT or not... I figured it was such a basic need, that it would just be available. You should've seen the look of shock/sadness/dismay on my face when I started creating DB migrations and saw what I expected to be a TPT structure come out as TPH. 😞

[JohnCOsborne]

Another person here who needs/needed TPT. Just commenting to add a +1 to those wanting TPT. If I had the time I'd look through these comments to see if there are non EF Core/Microsoft employees arguing against TPT.

[behzad888]

+1

[arghlolrofl]

Yet another one whose company is desperately waiting for EFCore to support TPT. This really is a game breaker, as we are unable to port our current Data Access Layer to .NET Core. Looking at all the upvotes in this issue, it is just not comprehensible, why this feature is taking so long. Also the lack of a clear roadmap for this feature is just frustrating!

Please make this a (very) high priority issue!

[Zymex]

Is there no work-around yet? It's been ~1300~ 1450 + days since this was opened.

[stijnherreman]

@Zymex one harsh workaround is to use EF6, that's what we do. Unfortunately the EF6 implementation isn't perfect either, it does not properly support cascade delete.

[weitzhandler]

@Zymex your only bet is EF6. TPT ain't coming to EFCore any time soon, as you can see, it's in the Backlog milestone.

[MichaelPeter]

Also need TPT support

[garfbradaz]

Is this just the case of porting the EF6 code base for TPT into EF Core? If so, can the community help?

[AndriySvyryd]

@garfbradaz Unfortunately the involved areas - model building, query pipeline, update pipeline and migrations are very different from EF6 and a new approach would be needed. It would take at least 3 EF developers to just create a comprehensive design.

[julealgon]

Was considering starting a new project with some TPT in mind and quickly found this issue. Now I'm kinda sad that it's not even in the mid-term roadmap, and this was opened in 2015.

Probably doable using composition and automapper to map the results into a real hierarchy in the domain layer, but I honestly wish I didn't have to go through that. It's just a LOT more work.

[davidathompson]

+1 for TPT.

[fisapras]

@garfbradaz Unfortunately the involved areas - model building, query pipeline, update pipeline and migrations are very different from EF6 and a new approach would be needed. It would take at least 3 EF developers to just create a comprehensive design.

Only 3 EF developers ... but countless happy users!!!

[inabeel]

TPT is essential to develop next generation of Application in optimized way. We can't live in big data world without smart tooling and with performance constraints

[Monah84]

The good news is that the EF6.3 will support ASP. NET Core, so all the problems that were preventing us from doing TPT and TPC, will be vanish soon. (expected on 23 Sep 2019).

[pantonis]

@Monah84 I don't think these are very good news. From what I read If I am not mistaken. EF 6.3 is not gonna be updated to support features that EF Core does. So this is not actually very useful when you do want to use EF Core.

[stijnherreman]

@Monah84 EF 6.3 will support running on .NET Core. The combination of EF 6 and ASP.NET Core is already possible today by targetting .NET Framework.

[inabeel]

@Monah84 Does it mean support for EF core is getting finished in this September? What will be the difference between EF core, and EF 6.3?

Not making sense to me!

[antoinebj]

@Monah84 Does it mean support for EF core is getting finished in this September? What will be the difference between EF core, and EF 6.3?

Not making sense to me!

EF 6.3 is a one-time port for EF 6.x to be compatible with the .NET Core runtime. Basically, it will have all features of EF 6.2, but not the features of EF Core that EF 6.2 doesn't have.
The difference between EF Core and EF 6.3 will be the same as between EF Core and EF 6.2, except for one thing : EF 6.3 will also be capable of running on .NET Core runtime.
This is simply to make it more likely for existing applications to be ported over to the .NET Core runtime.

EF Core is the one and only future of EF and EF 6.3 doesn't change that fact. EF Core will continue to be developed actively with new features, that EF 6.x will never have.

Edit: The most cynical will react to the last sentence by saying that it also looks like EF 6.x will keep having features that EF Core will never have. We're in the perfect topic for such a reaction ;)

[Monah84]

@Monah84 Does it mean support for EF core is getting finished in this September? What will be the difference between EF core, and EF 6.3?

Not making sense to me!

While Entity Framework Core was built from the ground up to work on .NET Core, 6.3 will be the first version of EF 6 that can run on .NET Core and work cross-platform. In fact, the main goal of this release is to facilitate migrating existing applications that use EF 6 to .NET Core 3.0. ~Diego Vega

EF Core support will not stop and will continue to include new features. For more information

https://devblogs.microsoft.com/dotnet/announcing-entity-framework-6-3-preview-with-net-core-support/

[matthiaslischka]

This issue has so much support. Aren't you afraid that people will go back from EF Core to EF 6.3 now?
While I understand why you killed TPT (for now) I still really miss the magic sometimes.

[pantonis]

@matthiaslischka why did they kill TPT? Because it is considered an antipattern? If that is the reason sorry but I don't agree

[brianjlowry]

I'm going to chime in real quickly as I was in the same boat as you and have been using TPT for eternity. As implemented in EF currently, TPT is an anti-pattern at scale for the sheer reason that it creates horrific 3,000 line queries to return a single row of data. The more inheritance you add to TPT, the slower EF becomes - regardless of whether or not you have data being returned. It will burn your server to the ground with OutOfMemoryExceptions.

You can Google it yourself - I didn't until the above issues started happening. Look here, for example:
https://stackoverflow.com/questions/21385188/table-per-type-inheritance-in-ef6-still-unuseable-slow

[Monah84]

I'm going to chime in real quickly as I was in the same boat as you and have been using TPT for eternity. As implemented in EF currently, TPT _is_ an anti-pattern _at scale_ for the sheer reason that it creates horrific 3,000 line queries to return a single row of data. The more inheritance you add to TPT, the slower EF becomes - regardless of whether or not you have data being returned. It will burn your server to the ground with OutOfMemoryExceptions.

You can Google it yourself - I didn't until the above issues started happening. Look here, for example:
https://stackoverflow.com/questions/21385188/table-per-type-inheritance-in-ef6-still-unuseable-slow

IMHO, I don't think this has a relation with the EF design at all. While designing the relations and inheritance and polymorphism, we should also take into consideration the database design. Designing classes with 75 attributes is surely anti-pattern and sure violating the normalization rules. TPT for large data tables should be with higher performance than the TPH, since to get your data, you are joining primary-primary key which should apply seek and not scan on the table.

[brianjlowry]

@Monah84 - that was simply an example that I Googled. In our case, we have many inherited tables with < 5 properties or so per table. The issue is that it joins all tables in a very inefficient manner. I am literally talking about thousands of lines for a generated EF query.

Here is another example - his query is 10,000 lines:
https://stackoverflow.com/questions/52815618/entity-framework-6-big-query-in-inheritance

It's a serious problem at scale. I'm trying to warn you, because I went through rewriting it all into TPH which is 20-30x faster... even with all those properties in one table. *And it doesn't bomb on OutOfMemoryExceptions any longer...

[markusschaber]

Hi,

IMHO, I don't think this has a relation with the EF design at all. While designing the relations and inheritance and polymorphism, we should also take into consideration the database design.

Ideally, databases would support inheritance directly in their data model. For example, PostgreSQL already provides table inheritance, which can be mapped 1:1 to class inheritance hierarchy.

But somehow, this feature never made it into other DBMSes nor the SQL standard, despite the fact that every ORM could use it. :-(

Part of all that "NoSQL" and "SchemaLess" fuzz is about databases not providing the tools to efficently map objects into tables, so I don't understand why table inheritance is not more widespread, as it solves a part of the problem.

[pushist1y]

But somehow, this feature never made it into other DBMSes nor the SQL standard, despite the fact that every ORM could use it.

That's because relational databases are based on relational algebra which is a strict mathematical model which has nothing to do with objects or inheritance at all. ORM as an attempt to map objects to relational tables is a leaking abstraction and should be treated so. Working with an ORM you are not working with objects, you are working with relations and should always keep that in mind.

Any attempts of Postgres (or anyone else) to introdues tables inheritance are in fact a set of custom workarounds and should only be used with full understanding of underlying mechanics unless you want one day to find yourself in a situation when your code does not work to your expectations and you have no idea why.

[tjad]

@brianjlowry TPT is a very different way of constructing the database, and the types of queries being performed should be considered before choosing this pattern. Inefficiency would be expected if one were to treat a database designed with TPT as a database designed with TPH.

My feeling is that the EFCore team continue to choose TPH as the defacto for a matter of simplicity. This is as terrible as the decision that Docker chose. Simplicity of framework design/construction over space usage. The team simply don't care, based on some idea that data space means nothing. Pure ignorance.

I continue to show disgust at the EFCore team (including investors) due to their poor attendance to this issue(#2266) and TPT strategy, so far as to ban EF Core from being used within our projects. It is a pity, there are some really good founding decisions in EF Core, but those don't warrant prolonged (several years) and stubborn attitudes toward our community of EFCore users. The very definition of opinionated. After all, we're without a doubt having more experience with the use cases of EFCore than the developing team ever could.
Listen to your community.

@pushist1y the relational algebra that RDBMS use have no bearing on this issue. Simply put, TPT is a strategy to avoid such algebraic complexity (especially when performing aggregate queries).

[tjad]

@brianjlowry Just to elaborate why TPT shouldn't cause efficiency problems if proper design was put in place.

I have inheritance hierarchy as follows

A <- B <- C <- D .... .... .... <- Z

If the database was designed properly, tables A to Z would have a common/shared index, meaning I could directly join tables A and Z without the rest of the in between table joins (B to Y). Or join any table with any other table in the hierarchy with n -1 joins, where n is the number of tables being joined.

Further, the benefit in the data storage is that if each of my tables had 5 columns, in TPH, I'd be forced to have a mega table with sparse storage of 130 columns. If many of my columns were CHAR (255), I'd waste 255 bytes on each unused column in the sparse setting with TPH. Let's say at worst, they were all CHAR (255). Each record would force on storage of 255 bytes per column. so even if I only created a record which consisted of table A data only. I'd use 32.3Kb per record ?? (255 * 5 * 26 bytes)
I could try add some database optimisation (if the database supported any of it, such as a sparse column descriptor) to my TPH, but I'd open a new can of worms. On such a large hierarchy (26 levels, from A to Z, which can still be quite a realistic number of tables for inheritance), how efficiently do those algorithms run, at what performance cost? (so here's a trade-off, space or computational time)

I take the same scenario and put it in a TPT model. I add a record to A. It only stores 1.2Kb of data per record (and independently of the other tables and their columns). Moreover, I avoid any other computational complexity in storing and retrieving that data.

Now whilst you think, this doesn't matter to my super raided server with multiple terrabytes (or petabytes) of disk space for data, my RAM disagrees with this design 300%.

Think about how ORMs work in the traditional setting. They load up all the data for a record into memory, now that record has to be transcribed into the object layer of the programming language (where a lot more performance is used and allocations are being done for transcribing and dirty checking of each value within the inheritance model).

Quite frankly, It's absurd to think that TPT is not the default inheritance strategy?? Especially in today's world of data complexity.

UPDATE: Example now uses CHAR (as VARCHAR does not illustrate my point well)

[brianjlowry]

@tjad, I appreciate your viewpoint, and you are preaching to the choir. My original post simply said (emphasis mine):

I'm going to chime in real quickly as I was in the same boat as you and have been using TPT for eternity. As implemented in EF currently, TPT is an anti-pattern at scale for the sheer reason that it creates horrific 3,000 line queries to return a single row of data.

Since EF generates the queries and they are incredibly inefficient, there isn't a remedy short of modifying the source code. I've seen multiple ways that TPT queries could be made more efficient (drop the CASE statements, for example), but my point remains the same. Once your db reaches a certain threshold of inheritance/properties/etc., you will see thousand line queries to simply grab one of the inherited objects as the generated SQL tries to join all of the possible inherited tables _and_ properties.

[tjad]

@brianjlowry So essentially you're pointing out that EF hasn't done a great job implementing TPT either.

I'm just pointing out that it doesn't have to be inefficient. And in fact, there is no reason not to TPT as a core feature today.

[markusschaber]

That's because relational databases are based on relational algebra which is a strict mathematical model which has nothing to do with objects or inheritance at all.

True.

But that Model has a certain age (it's from the late 60ies IIRC), and one could argue that such a model could be modernized and extended to cover for aspects like inheritance within 60 years in a fast-pacing industry.

ORM as an attempt to map objects to relational tables is a leaking abstraction and should be treated so.

Agreed.
But IMHO, this is not a counter-argument to the idea that we could try to reduce the gap and plug some of the leaks by enhancing the relational model, given that most programming nowadays happens in object oriented environments.

[crozone]

@brianjlowry TPT is a very different way of constructing the database, and the types of queries being performed should be considered before choosing this pattern. Inefficiency would be expected if one were to treat a database designed with TPT as a database designed with TPH.

My feeling is that the EFCore team continue to choose TPH as the defacto for a matter of simplicity. This is as terrible as the decision that Docker chose. Simplicity of framework design/construction over space usage. The team simply don't care, based on some idea that data space means nothing. Pure ignorance.

Seeing huge tables because of TPH is an edgecase and result of using fixed width datatypes. The default in most databases is to use a variable length (eg TINYTEXT) type. Nullable columns are usually implemented with a bitmap, so often a nulled out column will take up one bit of space, and in practice you usually get 4 bytes extra for each row for the bitmap. This isn't the end of the world by any stretch - you can get pretty silly with the inheritance before noticing serious perf and storage overheads. You also save a lot of space by using an integer as the discriminator column.

Yes, I think that TPT is a necessary feature that not only needs to be implemented, but also needs to work well. Having TPH as the default is certainly not Armageddon as you're implying. It works well enough for simple inheritance scenarios.

In high volume, high performance computing, database inheritance is usually a red flag on the database design anyway. In my experience, you're usually better off composing and managing the joins yourself, especially once you start to run raw queries. A huge enterprise database with millions of rows and huge inheritance stacks is a recipe for disaster.

I continue to show disgust at the EFCore team (including investors) due to their poor attendance to this issue(#2266) and TPT strategy, so far as to ban EF Core from being used within our projects. It is a pity, there are some really good founding decisions in EF Core, but those don't warrant prolonged (several years) and stubborn attitudes toward our community of EFCore users. The very definition of opinionated. After all, we're without a doubt having more experience with the use cases of EFCore than the developing team ever could.
Listen to your community.

@pushist1y the relational algebra that RDBMS use have no bearing on this issue. Simply put, TPT is a strategy to avoid such algebraic complexity (especially when performing aggregate queries).

Seems like overkill, but hey, if you need TPT you need TPT. I just hope that you understand that the current EF6 implementation carries huge caveats (as mentioned above), and that a proper, clean solution for EF Core will take serious time to be ready, because there are a lot of dependencies that need to be ready for this functionality. I don't think this is an issue of stubbornness as much as it is an issue of time and practicality.

[Monah84]

Once again, I would like to share my experience in healthcare applications with data exceeding 1TB. I am using TPT using EF6, Resources<-Branch, Resources <-Services, Resources<-People, People<- Customer, People <- Employee, People <-Patient, Service <- Trade, Services <- Medicine, Resources <-Schedules, Resources <- Reservations... and much more. Everything works great since we know how to maintain our database indexes, and we carefully chose the data-types, and respected the normalization rules to the maximum. It is not logical to have a table with over 9 fields. If necessary, we should think in vertical partitioning. EF6 generates the queries as expected. if we want to query a trade table from stock system, the EF6 includes inner join to Service and Resources as long as we are using one of their fields. TPT is a great feature especially to minimize the work from application side compared to the TPT workaround exists in EFCore.

[tjad]

@crozone I agree to some extent on the above. Totally on your understanding of the underlying storage engine. I would point out that the various storage engines would typically differ, which may be something to consider.

I also agree on the length of time that may be involved in getting TPT up and running, but at the same time, far more than enough time has passed (5 years) to sew the seed of TPT. A lot of technical debt has been incurred in EFCore due to not having sewed the seed early, and that's no excuse now regarding time. (Tech debt will just continue to soar).

I would like to emphasise that without TPT it is extremely difficult to build enterprise grade database models that are aligned with patterns seen in books like "The Data Model Resource Book, Vol. 1".
I mean, sure EFCore will suffice for some low or non polymorphic database model or where there is only lots of CRUD involved, but when it comes to the practical use and separation of data, databases are designed using principles aligning with TPT.

Even in school we were taught to be more aligned with principles that agree with TPT (the various stages of normalised databases)

UPDATE: Perhaps contrary to above mentioned, it's not extremely difficult, but tedious. Very little stands to reason in using EFCore in such scenarios (what benefits am I actually getting out of EFCore that support the data model designs ? I'd have to jump through extra hoops beyond building my own impl/abstractions when I'm not dealing with simple use cases where EFCore does a great job). Then there is no consistency, and an increased learning curve when introducing developers to our application stack. It's messy.

[tjad]

@crozone Also, regarding the various types that are amalgamated into 1 table, I'd lose flexibility/control at the database level, such as when I want columns to be strictly non null. Those columns' data usage are forced into the entire inheritance hierarchy per record. That's not avoidable without using multiple tables. And now constraints are being applied to parts of my model unnecessarily.

[tjad]

Each thing individually may not seem significant, it certainly all adds up, and most significantly, unnecessarily.

[tjad]

And I'll emphasise again. It's not disk space I'm particularly worried about. Bad database design trickles all the way through the entire application stack. Memory/RAM and CPU is more of a concern.

[brianjlowry]

@Monah84 - it seems as though you still only have 5 tables or so inheriting from a base table, albeit many times over. Is that correct? Our problem occurred around 30 tables being inherited from one base table. There's no design flaw in our DB structure, and we have minimal properties in all tables (5-10 max). Queries at that scale try to join all 30 tables, and that's where things went south as the query it generated was thousands of lines.

We never saw the problem as you defined your database; everything worked great with ~5 inherited tables. It wasn't until we kept stacking more tables on top that we starting getting one minute queries to return a row of data, etc. That led us to look into the queries being generated, and I'll reiterate: it was thousands of lines.

[markusschaber]

@brianjlowry I'm not exactly sure whether the query really needs to be that big, or the ORM just generated very inefficient queries.
With queries of that size, part of the time might be parsing the query, and things like the query optimizer in the backend running amok. Maybe tuning the query planner via server configuration might improve things a bit.
Of course, even generating and sending the query takes some time, in this case. :-(
As always, prepared statements might help, and maybe the ORM could even use views (one view per type) to reduce the parsing cost at runtime.

[tjad]

BUMP: @ajcvickers @bricelam @smitpatel @anpete @AndriySvyryd @maumar @dotnet-maestro-bot @rowanmiller

Perhaps shed some light on guidance toward getting this feature in. Give a potential task list / itinerary on the above. At the very least, work toward syncing the community by getting it on the roadmap.

[bricelam]

FYI @tjad, anpete and rowanmiller are no longer on the team, and dotnet-maestro-bot is ...a bot. Please don't spam top repo contributors with mentions.

[ajcvickers]

I would like to address a few points from the recent discussions.

On performance

Our analysis of potential performance benefits of TPT have shown them to be minimal. This is assuming an efficient implementation of TPT that does not join to a table when it is not needed. That being said, it is of course possible to create scenarios where TPT would create a more performant query than TPH for certain combinations of column count, density, and amount of data. However, this isn’t terribly relevant since the reason we would support TPT in EF Core is not because of performance, but because people like to design their databases in this way and then like to map this database to an inheritance hierarchy. (As opposed to using composition patterns instead.) Also, we would likely support TPC along with TPT, and it’s easier to find performance gains with TPC than TPT.

On the technicalities of implementing TPT

The original EF Core architecture used a relatively simple mechanism for translations between entity shapes defined in code and entity shapes as stored in the database. This is simple and fast for the common cases. TPT requires some changes to that architecture to allow for more complex mappings when needed. We have been thinking about and planning for this as robustness of the architecture has improved incrementally through releases. However, there is still significant work to do here at the lowest levels, which makes this, relatively, one of the most expensive and involved features we could take on.

On plans to implement TPT

While it is true that we did not originally plan to support TPT in EF Core, that has not been the plan-of-record for some time now. However, as I have recently communicated elsewhere we have a small development team working on EF Core. (Currently post 3.0 we have five engineers plus a manager and a P.M.) Over the last couple of years this team has implemented many of the top-requested features and EF Core already supports many features that are missing in EF6. However, the size of the team does make complex features like this difficult to balance into release schedules. We have a document that describes the planning process in more detail. This issue is currently the third most voted issue on the EF repo, and this will be taken into account when planning post 3.0 work.

We have and will continue to communicate customer feedback up the management chain. It is then a strategic decision from Microsoft management whether to increase resources working on EF, as opposed to using those resources for other projects that may potentially have bigger immediate ROI or strategic impact. Currently the feedback we have received on this is that the current level of resourcing is sufficient, and the relatively slow progress on EF that results is acceptable from a strategic perspective.

On the EF team, we will continue to advocate for more resources on EF, and we will continue to do our best to move EF Core forward to the best of our ability.

On using EF6 versus EF Core

EF6 is being brought to .NET Core so that existing applications that use EF6 can be ported to .NET Core without also requiring a port to EF Core. It’s not our intention to encourage new projects to use EF6. That being said, we want you to use whatever is the most appropriate technology to achieve your goals. That might be Dapper, nHibernate, EF6, or anything else. The EF6 code base is not going to significantly evolve going forward, but if it works well for you, then by all means use it.

On keeping things civil

The EF team is dedicated and passionate about making EF Core as good as it can be. We listen to, appreciate, and respect the feedback we get on this and many other issues. Please remember that we are all trying to do the best we can within the constraints of our environments. (By “all”, I mean the EF team, the EF community, and developers using EF in their code.) Attacks directed at the people on the EF team are not in any way helpful in moving anything forward. Please ensure that responses are kept civil and respectful as is covered in our code of conduct.

[chrisdpratt]

@ajcvickers: Thank you for this very thorough and reasoned response. I can't speak for everyone here, but for me personally, I want you and the rest of the team to know that my frustration is not with the team, but with Microsoft. Up to this point, EF Core is basically the only viable ORM for development in .NET Core. Microsoft has wasted no effort or expense in promoting .NET Core, and EF Core, for that matter, but yet deigns to assign any more than 5 developers to the effort. For a company with tens of thousands of employees, and something so critical to developer workflows, it's ridiculous to say the least.

For my part, I want you all to get the resources that you need and that the community demands, so that you aren't forced to have to reply with platitudes of "we're doing the best we can". We know you are. Microsoft is not.

[weitzhandler]

@ajcvickers Thank you so much for your kind and detailed response, and for the time you took writing it.
My 2 cents: I used to use TPT and was annoyed at first with its lack of support in EF Core. But after using TPH for a while and getting used to the discriminators, I discovered it's actually not so bad and is performant. The extra columns, when null, don't take much space and less joins are required. It feels semantically wrong for strict OO developers, but is actually not so bad.
It's not that I wouldn't want to see TPT implemented, just saying that I can live with TPH too somehow.

[ds1709]

@weitzhandler, it's semantically wrong in terms of database normalization, not just OO.

[pantonis]

@weitzhandler I don't see any other benefit apart from performance to use TPH whilst on the other end I see a dozen fundamental reasons, much more important, to not use it. Performance is very important but not more important that DB Normalization etc...

[tjad]

@ajcvickers Thank you, I appreciate your time taken to thoroughly elaborate on EF Core team's position.

[stijnherreman]

Myself and a lot of others are subscribed to this issue so we are notified about new developments regarding TPT and EF Core. Please don't abuse it for a back-and-forth troubleshooting discussion.

[tjad]

Regarding performance considerations .. I see many of us here are aware of relational concerns such as joining tables. I'd like to raise for discussion on underlying performance regarding 1 massive table beyond the surface clutter.

Paging
RDBMS tend to store data as a tuple, these tuples are the size of the number of columns (and their data sizes). Making large data tuples inherently makes the data more difficult to store - with regards to alignment. If the alignment is out, padding is done or worse, when the record can't fit within the page, a new page will be created and the new record stored in a new page. The more memory/data misalignment that happens, increases the more fragmentation of data happens. Whilst SSD drives wont be too pedantic about it in terms of disk seeks, mechanical drives that are still used today will be extremely unhappy. Never-the-less, data fragmentation is still a large concern - even for SSD.

Furthermore, given the nature of the underlying storage engine described above, it means that the entire heritance hierarchy's data will be fragmented. For instance, an animal class hierarchy may store all the kinds of animals of the zoo being modeled in 1 table (TPH), however, Chickens will reside with cows and ducks with with lions, and elephants with mice, well, they will all be together in "one place" fragmented across the pages in no organised manner. Imagine, a zoo like that... chaos with all the animals stepping on each others' trotters, hooves and toes... just like the underlying data being stored.

Locks
In order to comply with ACID, RDBMS make use of locks, generally made as a hierarchy lock system. For instance, MSSQL Server use:

Database Lock -> Table Lock -> Page Lock -> Row Lock

Meaning if you acquire a table lock, its descendent locks will be unavailable (page lock / row lock)
Or if you acquire a Page lock, all rows stored within that page are locked too.

Seeing this, it becomes quite apparent that there is another tradeoff with multiple tables vs 1 large table. That of locking.
Sure, we wont expect to lock the table too frequently (although if the hierarchy is complex enough with 1 table, it may suggest that frequent locks from various aspects of the application making INSERT and DELETE will be present.)
What we're going to worry about more is Page Locks (as it may appear that Row locks can't particularly be optimised upon in terms of large vs small tables).
As different parts of the inheritance hierarchy tend to do INSERT/UPDATE/DELETE at different frequencies/rates and at different times, it means that similar entities will not be stored in pages that are close to each other. Given this is the case, it would mean that there is a higher risk of total database locking (from all the page locks) due to the fragmentation of the inheritance hierarchy. As the database scales to take on more users' queries, the increased query rate will further make this issue more prominent as more queries unnecessarily wait upon other queries.

When relating this to the zoo example above, thinking about how locks work and updating Lion data , it may stop the Chicken records stored in the same page as the Lion record from being accessed when in fact they have nothing to do with each other, yet because they are on the same page, only one entity's data will be changeable at any given moment, even if in the class hierarchy they are completely functionally independent of each other (only connected by a hierarchical structure).

Note
This is to raise a discussion on something that isn't particularly avoidable without database normalisation. Perhaps others can provide insight ?

[markusschaber]

This depends a lot on how the databases internal storage format is defined.
e. G. PostgreSQL uses a bitmap of nullable columns, so a column which is not set just takes up a single bit of storage.
Also, a table can be CLUSTERed on an index of the discriminating column, so all chickens will be near other chickens, and elephants near other elephants.

[tjad]

Provided those columns are variational and nullable.

Thanks, I'm fond of PostgreSQL and that CLUSTER upon index is useful to learn about.

[insylogo]

@divega Now that 3.0 is released, 3.1 is announced as a bugfix/optimization release, what's the timeline for TPT? I've got multiple projects that would love to be in .NET core, but are blocked by the absurd delay on this issue.

[marchy]

@divega Now that 3.0 is released, 3.1 is announced as a bugfix/optimization release, what's the timeline for TPT? I've got multiple projects that would love to be in .NET core, but are blocked by the absurd delay on this issue.

This is blocking our reconsideration of migrating to EF Core as well – along with support for spatial types. We are after-all in a "mobile-first, cloud-first" era after-all... or are we?
(_mobile = location-based services_)

UPDATE: As per @ajaybhargavb 's comment bellow spatial support has been added since EF Core 2.2 🎉

[tcfialho]

How to priorize backlog... First, ignore one of the most commented issue for years... then... keep working in other stufs... maybe waiting for the death of 'relational' normalised databases for close that issue.

[divega]

TPT is one of the top "EF6 gap" priorities in the backlog that we will consider for after 3.1, alongside things like many-to-many, stored procedure mapping support, update model from database, etc.

As @ajcvickers explained in https://github.com/aspnet/EntityFrameworkCore/issues/2266#issuecomment-524077785, it is also probably the most expensive item in our backlog, meaning that if we decide to do it, we won't be able to fit much else.

Vote counts on the issues may already reflecting what our customers want (e.g. many-to-many has more votes than TPT), but we want to go a bit deeper on the cost/opportunity analysis.

[Monah84]

@divega I think many of us would like to contribute to bringing such features to .NET core. Share with us what you need to accomplish, and you will find many of us jumping to help and contribute. Our business is strickly depending on .NET. So when Microsoft level up, we do as well.

[insylogo]

As @ajcvickers explained in #2266 (comment), it is also probably the most expensive item in our backlog, meaning that if we decide to do it, we won't be able to fit much else.

It's also the most important thing that's been missing in EFCore since version 1.0, yet was implemented literally over a decade ago in the original Entity Framework. How can you be on version 3 when version 1 of Entity Framework had TPT? It's like saying I hope we can get to adding Search to Windows 10 - that's been a feature since forever, for crying out loud.

[Racerboymatt]

I've ready this entire thread and I must say that I cannot understand how it is possible we have gone to EF Core 3.0 and not had TPT yet. It really defies all belief... SERIOUSLY... I know you’re a small and dedicate team but you really have to be kidding. TPT is a very fundamental concept to a RDMS. To not have it shows a real lack of knowledge of database design patterns. Yes, there is a book on this by David C. Hay. Go read it, its full of TPT concepts. I think to say its “unneeded” or is a “bad design” shows great ignorance. To me, not having TPT is like not having "copy and paste" on a phone. Sure, Apple did it for almost two years but it was just as ridiculous as this. For the love of all things holy please put this in. Yes, we understand there are nuances and complexity to the implementation but really, this is insane that we have to ask this much and for this long to get it. You left out a fundamental feature in EF Core that we have had for years prior to this. This is such a basic concept in database design and not having this in EF Core is making life miserable for a great deal of us. I can’t say this any nicer, I’m really at a loss that I have to ask at all.

Please, PLEASE... implement TPT.

Always yours affectionately,
Matt

[gbjbaanb]

From experience, I had to work on a legacy system that was implemented as a TPH (though not because of EF) and frankly, it was a nightmare for maintenance. We had well over a couple hundred columns on that table in the end. Such a thing might work fine for small projects, but when you start to get complex, it won't suffice.

That said, when you get to such a level of complexity, its probably time to do a DB-first design and map it to EF rather than the other way round.

@marchy Spatial Types is in EF Core since 2018, I only use the simple point and distance over geography types but it works fine.

[Racerboymatt]

Please create this feature. For the love of all that is holy!

[marchy]

@marchy Spatial Types is in EF Core since 2018, I only use the simple point and distance over geography types but it works fine.

Oh fantastic, my apologies for this slipping through and missing the announcement (spatial was one of 3 critical missing features we've been tracking)

[gbjbaanb]

@marchy Spatial Types is in EF Core since 2018, I only use the simple point and distance over geography types but it works fine.

Oh fantastic, my apologies for this slipping through and missing the announcement (spatial was one of 3 critical missing features we've been tracking)

Frankly just write SQL and call it from Dapper (which is brilliant). Use EF for the simple stuff.

[marchy]

Frankly just write SQL and call it from Dapper (which is brilliant). Use EF for the simple stuff.

Ughh EF is LITERALLY built for the opposite reason. Complex, domain-driven entities that can be designed code-first as to model your domain as closely as possible, and able to handle complexities across relations, complex types (as of EF Core) and to HIDE as much needless ORM details as possible – not expose them.

If we were looking for a simplistic solution we wouldn't be on EF in the first place (ie: just use Dapper for everything)

[SoheilHasankhani]

It's ridiculous and unpleasantly disappointing, EF core team does not consider community need at all !!!
This feature has been on the backlog for a long time and the community always has asked for implementation. It seems the EF team does not care about community feedback and requirements.

[chrisdpratt]

@SoheilHasankhani If you read through the full thread, the team has responded multiple times on this. The problem boils down to limited resources and other features have been more important.

While I am sympathetic, I think the calculation of which features the team is focused on is suspect. For example, EF Core didn't need to support CosmosDB, but I imagine that decision was made above their paygrade, to support Microsoft's Azure efforts. Frankly, I think there's been a ton of mismanagement of EF Core, but it's not the team, itself. It's Microsoft 1) not devoting adequate resources to this project and 2) sidelining what resources there are on things that are important to Microsoft, but not necessarily the community using EF Core.

[gbjbaanb]

Ughh EF is LITERALLY built for the opposite reason. Complex, dom

EF LITERALLY doesn't do this, which is why this issue exists. So you have a choice of doing your DB in a DB-first approach, or trying to fudge your c# domain to fit what EF will let you have. In practical terms, that's only one real choice.

[brendansojo]

EF LITERALLY doesn't do this, which is why this issue exists. So you have a choice of doing your DB in a DB-first approach, or trying to fudge your c# domain to fit what EF will let you have. In practical terms, that's only one real choice.

EF Core doesn't do this; EF—all versions—SHOULD do this. Which is why most of us are ignoring EF Core and sticking with EF6 (or other). If MS wants us to take up Core they need to fix this. Otherwise it's just a toy tech - not suitable for serious enterprise development.

We know that other stuff is being prioritized. We get it. Which is precisely why we're all here, advocating for change.

[IonRobu]

I started a new code-first project and is truly impossible to manage a large data domain with TPH. I will try to revert to EF 6.3, hoping to manage the situation, but this very disappointing. I don't know how many votes, feedback, screams has this problem, but this lack of functionality makes EF Core unusable for large domains. I hope to succeed with EF 6.3 (all my basecode is written for EF Core) and I will stay there until TPT will come to EF Core (if any). Sorry...

[ToddThomson]

https://github.com/aspnet/EntityFrameworkCore/issues/2266#issuecomment-122121006

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".

EF Core 3 with Owned Entities (aggregation)+ 1-1 relationships is now a better good alternate choice to TPT.

EF Core 3 is definitely now a suitable replacement for EF 6.3! Well done to the EF Core team.

[IonRobu]

#2266 (comment)

EF Core 3 with Owned Entities (aggregation)+ 1-1 relationships is now likely a better choice than TPT.

EF Core 3 is definitely now a suitable replacement for EF 6.3! Well done to the EF Core team.

"To own - To have" is not "To be". This is not a natural representation of the domain.

[gbjbaanb]

"composition is just as much a pattern as inheritance". I remember being told that by Java and C# fans when I pointed out their idea of multiple inheritance was poor compred to C++ :)

Composition (ie aggregation but with cascade delete) is what I use to reflect inheritance in my DB model. It works very well, and IMHO is exactly the kind of pattern to use with a relational database. Trying to fudge SQL into looking like C# (with a single inheriatence model ;) ) is better achieved by using techniques suited to the DB.

Owned Types, when you look at the underlying SQL table,s its modelled exactly as you'd want.

[ToddThomson]

#2266 (comment)
EF Core 3 with Owned Entities (aggregation)+ 1-1 relationships is now likely a better choice than TPT.
EF Core 3 is definitely now a suitable replacement for EF 6.3! Well done to the EF Core team.

"To own - To have" is not "To be". This is not a natural representation of the domain.

@IonRobu I just wanted to point out that EF Core with OwnedTypes (Composition/Aggregation) can now be used to migrate from EF 6.3 TPT inheritance.

Personally, what I found was that using composition resulted in C# POCO models that required navigation properties to access the aggregates which is not quite as "clean" ( or simple, or beautiful ) as when using base classes and TPT.

In the end, I personally decided that the overall benefits of using EF Core 3 outweighed the exact mapping to C# inheritance. I also found the conversion to composition was without too many difficulties or issues.

In my opinion, EF Core 3 Owned Entity Types is a suitable option that may work for you when porting an older TPT based database. Having options is a good thing!

[marchy]

@ToddThomson, @gbjbaanb,
Guys, your argument for the fact that you fit a square peg into a round hole misses the entire point of an OBJECT-relational-mapper and the spirit of Entity Framework in the first place.

The reason Entity Framework split from the pack and took on a code-first approach, way back in EF 4.1 was because the point is programming models should best model the features of an object-oriented domain and make the implementation detail of how things are persisted be as transparent as possible when programming in that domain.

With something as basic as the concept of inheritance, and in certain domains where it makes sense to store these as relational data-sets rather than flat in a mega-table (there are a ton of valid cases), it is absolutely crucial to NOT mess up your entire OO representation because your ORM happens to be too immature and forces an un-natural skew upon your domain in how it is able to represent it.

Drop your workaround arguments that just makes worse programmers out of junior/intermediate engineers that don't know any better and in turn cause massive damage to code-bases over the long-term.

EF Core is definitely NOT a suitable replacement for EF 6, if you think otherwise just look at the ONE HUNDRED FIFTY bugs that the 3.1 release fixed after pre-maturely rushing 3.0 out to try to make the release train of the rest of .NET Core 3.0.

You're not making the world a better place by advising people to adopt a technology that is years from being ready. Save yourself the shot in the foot and DON'T take architectural hits to your codebase that are extremely costly, expensive, and in reality will likely never be re-architected/refactored away from. Adopt EF 6 or NHibernate and model things as they SHOULD be modelled. And let the EF Core team play around with their toy framework until finally a minor release will have 5-6 bugs to fix per release – as is the case with the mature and reliable EF 6.4.

PS: If you can't tell, EF Core has burned us BADLY.
TIME... and TIME.... AGAIN

stayaway

[gbjbaanb]

@marchy Dude, I work int he real world, and sometime you just have to workj with what you have. no amount of complaining will change that. So - complain all you like to the EF devs here, and hopefully they'll produce a good resolution, but that won't change anything for us who are writing code that needs to do things differently to what comes out of the box.

So I implement my workaround, and describe them so they might help others who would otherwise.... well, what would they do? Flatten their hierarchies, not use EF. If they have to use it, then making the most of it is a sensible approach.

Frankly, I always thought code-first was a amateur bodge that continued to approach real systems as if they were C# only. Which is a mistake. The DB is such an important factor in systems that anything other than hobby projects should be DB-first every time.

[rorymurphy]

Great to see this one getting tagged in commits. For others out there who may need a nearer-term solution, I was able to get something working using data transfer objects and an expression tree rewriter that translates LINQ queries written against domain objects into queries against DTOs, allowing them to still be converted to SQL commands against the database. Adds a bit of complexity in to create the DTO objects but, for some use cases, is still worthwhile to keep the domain model clean.

The library is available on Nuget, with code hosted at https://github.com/rorymurphy/Remapper. Under the test project, one of the unit tests demonstrates table-per-type mapping.

[RobsonRamos]

Hi guys,

I'm making a complex refactoring in my startup source code and I'm making a wokaround in the existent code because the TPT isn't available yet =/

I'd like to know when this feature will be available in a stable EF release?

[chrisdpratt]

@RobsonRamos it's tagged for the 5.0 milestone, so late this year, most likely.

[PieterjanDeClippel]

@RobsonRamos .NET 5.0 is planned to be released in November 2020, maybe December 2020 if there's some delay.

[chrfin]

Hopefully we can get TPT in one of the next previews @AndriySvyryd?

[AndriySvyryd]

TPT is about halfway done. Most likely it will be included in preview8

[marchy]

Much appreciate the insight @AndriySvyryd. Really helps up plan / set internal roadmap expectations!

[smitpatel]

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.

[marchy]

🎉

[tcfialho]

👏🏻👏🏻👏🏻👏🏻👏🏻

[pcasais]

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

[eraffel-MDSol]

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]

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?

https://github.com/dotnet/efcore/issues/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?

https://github.com/dotnet/efcore/issues/19811

[PieterjanDeClippel]

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]

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]

@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]

@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]

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]

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]

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]

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]

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