New issue

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

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

Already on GitHub? Sign in to your account

Relational: TPT inheritance mapping pattern #2266

Open
satyajit-behera opened this Issue May 24, 2015 · 157 comments

Comments

Projects
None yet
@satyajit-behera

satyajit-behera commented May 24, 2015

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

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

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

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

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented May 29, 2015

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

This comment has been minimized.

satyajit-behera commented May 29, 2015

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

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented May 29, 2015

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

@satyajit-behera

This comment has been minimized.

satyajit-behera commented May 29, 2015

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

@anpete

This comment has been minimized.

anpete commented May 29, 2015

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

@satyajit-behera

This comment has been minimized.

satyajit-behera commented May 30, 2015

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

@GSPP

This comment has been minimized.

GSPP commented Jun 15, 2015

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

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

@santanaguy

This comment has been minimized.

santanaguy commented Jun 23, 2015

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

@satyajit-behera

This comment has been minimized.

satyajit-behera commented Jul 3, 2015

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

@ToddThomson

This comment has been minimized.

ToddThomson commented Jul 16, 2015

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

@konstantin999

This comment has been minimized.

konstantin999 commented Jul 31, 2015

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

@konstantin999

This comment has been minimized.

konstantin999 commented Jul 31, 2015

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

@AndyNewland

This comment has been minimized.

AndyNewland commented Aug 27, 2015

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

@satyajit-behera

This comment has been minimized.

satyajit-behera commented Aug 27, 2015

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

@jimmymain

This comment has been minimized.

jimmymain commented Sep 18, 2015

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

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

@ToddThomson

This comment has been minimized.

ToddThomson commented Sep 18, 2015

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

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

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

@rowanmiller rowanmiller changed the title from TPT for EF7 to Relational: TPT inheritance mapping pattern Sep 18, 2015

@jimmymain

This comment has been minimized.

jimmymain commented Sep 18, 2015

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

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Sep 28, 2015

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

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

@ToddThomson

This comment has been minimized.

ToddThomson commented Sep 28, 2015

Thank-you for the feedback @rowanmiller .

@satyajit-behera

This comment has been minimized.

satyajit-behera commented Sep 29, 2015

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

@thomas-darling

This comment has been minimized.

thomas-darling commented Sep 29, 2015

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

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

@rowanmiller

This comment has been minimized.

Member

rowanmiller commented Sep 29, 2015

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

@satyajit-behera

This comment has been minimized.

satyajit-behera commented Sep 29, 2015

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

@rowanmiller rowanmiller added this to the Backlog milestone Oct 2, 2015

@tuespetre

This comment has been minimized.

Contributor

tuespetre commented May 19, 2018

@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

This comment has been minimized.

Member

AndriySvyryd commented May 19, 2018

@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

This comment has been minimized.

markusschaber commented Jun 4, 2018

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

This comment has been minimized.

Contributor

roji commented Jun 4, 2018

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

@insylogo

This comment has been minimized.

insylogo commented Aug 15, 2018

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 AndriySvyryd removed their assignment Aug 15, 2018

@AndriySvyryd

This comment has been minimized.

Member

AndriySvyryd commented Aug 15, 2018

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

@RowlandShaw

This comment has been minimized.

RowlandShaw commented Oct 11, 2018

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

This comment has been minimized.

SergeyLimonov commented Oct 11, 2018

@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

This comment has been minimized.

RowlandShaw commented Oct 11, 2018

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

This comment has been minimized.

SergeyLimonov commented Oct 11, 2018

@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

This comment has been minimized.

IngbertPalm commented Oct 31, 2018

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

This comment has been minimized.

Member

ajcvickers commented Nov 5, 2018

@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

This comment has been minimized.

Contributor

weitzhandler commented Nov 6, 2018

😢

But at least thanks for the update.

@IngbertPalm

This comment has been minimized.

IngbertPalm commented Nov 6, 2018

@ajcvickers Thank you for the update!

@Deilan

This comment has been minimized.

Contributor

Deilan commented Nov 7, 2018

image

@AlonCG

This comment has been minimized.

AlonCG commented Nov 8, 2018

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

This comment has been minimized.

Member

ajcvickers commented Nov 8, 2018

@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

This comment has been minimized.

AlonCG commented Nov 13, 2018

@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

This comment has been minimized.

Member

ajcvickers commented Nov 13, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment