Relational: TPC inheritance mapping pattern
rowanmiller opened this issue · 64 comments
Consider configuring or issuing a warning to help with split identity seeding or shared sequence for the PK
FKs that point to a TPC entity type won't be enforced
- Model support
- Migrations support
- Seed data support
- SaveChanges support
- Query support
- End-to-end tests
Related: #17270 (comment)
Can I make assumption that TPC is now going to be in RC? judged by the following work as expected
public class Program
{
public void Main(string[] args)
{
var context = new InheritanceContext();
var sarin = new Manager() {Name = "Sarin"};
var siriphan = new Employee() {Name = "Siriphan", Manager = sarin};
context.Managers.Add(sarin);
context.Employees.Add(siriphan);
context.SaveChanges();
}
}
public class InheritanceContext : DbContext
{
public DbSet<Manager> Managers { get; set; }
public DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\MSSQLLocalDB;Database=Ef7Inheritance;Trusted_Connection=True;");
}
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
public class Manager : Person
{
public Collection<Employee> Staffs { get; set; }
}
public class Employee : Person
{
public Manager Manager { get; set; }
}
This code is tested EntityFramework 7.0.0-beta8
awesome!
@wangkanai you are really just getting two separate entity types with an unmapped base type. They each have a separate key space and you won't be able to run a LINQ query on Person. So it's not really inheritance in EF, it's two separate entity types that happen to share a base type in the CLR for implementation.
If their are in same key space would that work well with sql server auto incremental primary key?
@wangkanai not with Identity (since that is per table) but you could use a sequence to generate values. What I really meant is that with true TPC you can't have Manager with Id = 1 and an Employee with Id = 1 since then you would have two instances of Person with Id = 1 (and EF would throw if you every tried to have this). In you setup this is possible since they aren't really in an inheritance hierarchy as far as EF is concerned.
@rowanmiller so if in theory we make a convention to use Id as identity key as Guid (in theory it unique), this should work right?
I'd wanna see TPT before TPC. As @rowanmiller said, @wangkanai example will be used in rare cases when you explicitly wanna keep the entities unmapped or whatever other reason I can't think of now.
But TPT is even more important than TPH, because it can save a bunch of columns generated in each table completely DRYed and less-productive for some purposes.
Lack of TPC made me start a new project in MVC5 and give up the new beta.
Will this be implemented for the RC?
@wangkanai yep that would work. Just to be clear though... with the code you listed back at the start there is no need to ensure that the keys are unique between types since EF is treating them as completely separate types. But for true TPC, yes GUID keys is one option, or a sequence for numeric values, etc.
@weitzhandler I agree. Our database teams design with high normal forms in mind. TPT is what's required. I am of the opinion that TPC is a weak compromise compared to TPT. I often need to query base classes, and TPT makes this easy. A classic example of this is Martin Fowlers paper on Roles. Our clients will not be moving to EF until TPT is implemented.
What is the status on this feature? This turns out to be a really important feature in our case.
The implementation could be simplified if we used a database model #8258
+1 For Me - Note to all that you should "thumbs up" the initial post by @rowanmiller to vote up this issue. This and TPT is how DB's are/should be modeled.
The roadmap lists both TPC and TPT as high priority. Sad to not see either of these tickets in the 2.1 or 3.0 milestone.
Note: see also #10739
Will this ever make it in? 884 days and counting...
Any news about TPC ?
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.
@rowanmiller We're using TPC with something like #3170 (comment) but we're getting stuck trying to get one to one relationships to work. We get the following exception:
System.InvalidOperationException: A key cannot be configured on 'BloodPressure' because it is a derived type. The key must be configured on the root type 'Observation'. If you did not intend for 'Observation' to be included in the model, ensure that it is not included in a DbSet property on your context, referenced in a configuration call to ModelBuilder, or referenced from a navigation property on a type that is included in the model.
One to many relationships Works correctly, even considering that TPC is not supported in EF Core. Any idea for that?
@alexdrl If you think you are hitting a bug, then can you please open a new issue including a runnable project/solution/repo or complete code listing that exhibits the behavior?
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.
@markusschaber PostgreSQL table inheritance is already tracked by #10739.
@roji Thanks for the hint! Subscribing immediately :-)
@ajcvickers The problem was a non-mapped reference to the abstract class that was not mapped to a table, this caused EF Core to acknowledge that table, and throwing an exception because the base class was being tracked in the context.
Based in the example by @wangkanai, this would be something like:
public class Program
{
public void Main(string[] args)
{
var context = new InheritanceContext();
var sarin = new Manager() {Name = "Sarin"};
var siriphan = new Employee() {Name = "Siriphan", Manager = sarin};
context.Managers.Add(sarin);
context.Employees.Add(siriphan);
context.SaveChanges();
}
}
public class InheritanceContext : DbContext
{
public DbSet<Manager> Managers { get; set; }
public DbSet<Employee> Employees { get; set; }
public DbSet<Computer> Computers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\MSSQLLocalDB;Database=Ef7Inheritance;Trusted_Connection=True;");
}
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
public class Manager : Person
{
public Collection<Employee> Staffs { get; set; }
}
public class Employee : Person
{
public Manager Manager { get; set; }
}
public class Computer
{
public string Name { get; set; }
public Person User { get; set; }
}What is the timeline on this ? kind of a blocking Item for us
I am having "duplicate primary key values" problem and I have read somewhere that there are two ways to resolve this
-
Use [DatabaseGenerated(DatabaseGenerationOption.None)] on base class primary key and not map it on child classes at all and manage Id values manually (which I don't want to do)
-
Use the different initial seed for different types. Now I have 15 classes inherited from abstract BaseEntity class and I couldn't find any example of using the different initial seed for code first approach.
Can anyone please explain it to me how "different initial seed" works and is there any alternative to fix this without having to manage Id manually
Cheers
It continues to sit in the backlog. We had to rewrite everything in 4.7 as this was too much, and we had concerns around other things that may not be finished yet that we would find out later in our development process.
I forgot to add the code for the IDbContextOptions implemation. Maybe something needs to be activily dispoosed here?
public abstract class DbContextOptionsBase : IDbContextOptions
{
protected readonly DbContextOptionsBuilder Builder = new DbContextOptionsBuilder();
public virtual DbContextOptions GetOptions(string connectionString)
{
Configuring(Builder, connectionString);
var conventionSet = GetConventionSet();
return conventionSet == null ? Builder.Options : AddModelBuilder(conventionSet);
}
protected virtual void Configuring(DbContextOptionsBuilder optionsBuilder, string connectionString)
{
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll);
if (string.IsNullOrWhiteSpace(connectionString)) throw new ArgumentNullException("Connection string cannot be null");
Configuration(optionsBuilder, connectionString.ResolveConnectionString());
}
// e.g. optionsBuilder.UseSqlServer(ConnectionString);
// e.g. optionsBuilder.UseInMemoryDatabase(ConnectionString);
protected abstract void Configuration(DbContextOptionsBuilder optionsBuilder, string connectionString);
protected virtual DbContextOptions AddModelBuilder(ConventionSet conventionSet)
{
ModelBuilder = new ModelBuilder(conventionSet);
ModelBuilder = ModelCreating(ModelBuilder);
if (ModelBuilder != null)
{
Builder.UseModel(ModelBuilder.Model);
}
return Builder.Options;
}
public ModelBuilder ModelBuilder { get; protected set; }
protected virtual ConventionSet GetConventionSet()
{
return GetConventionSet<DbContext>();
}
protected virtual ModelBuilder ModelCreating(ModelBuilder modelBuilder)
{
return null;
}
protected virtual IEnumerable<IMutableEntityType> GetModelBuilderTypes()
{
return ModelBuilder.Model?.GetEntityTypes();
}
protected virtual void RemoveModelBuilderTypes(IEnumerable<IMutableEntityType> entityTypes)
{
foreach (var entityType in entityTypes)
{
ModelBuilder.Model?.RemoveEntityType(entityType);
}
}
protected virtual ConventionSet GetConventionSet<TDbContext>() where TDbContext : DbContext
{
var serviceProvider = GetServiceProviderForConventionSet().BuildServiceProvider();
using (var serviceScope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
{
using (var context = serviceScope.ServiceProvider.GetService<TDbContext>())
{
return ConventionSet.CreateConventionSet(context);
}
}
}
protected abstract IServiceCollection GetServiceProviderForConventionSet();
}In the docs about breaking changes of EF Core 3.0 it says that
Starting with EF Core 3.0 and in preparation for adding TPT and TPC support in a later release
I assume that means TPT / TPC will be supported in 3.1 maybe 3.2?
@MSiffert It does not imply that. It just means we know it's a break we need to make, and making the break sooner is preferable to leaving it like this for longer.
We are now moving a project that uses TPC to EF.Core. It is already built into architecture, is there any workaround we can use before TPC is officially supported? We used MapInheritedProperties(), but we never queried using Base type, as it was abstract. Will ordinary ToTable include inherited properties?
@Grasher134 Using the base type as an "unmapped base class" sounds like it might work for you. This is generally a good approach, but does require that entity types don't reference the base class directly. Mapping inherited properties should not be a problem.
Still no news about it?! 😔
Can we infer from the following documentation that TPC will be supported in EF Core 5.0? -
The text says it pretty clearly - TPT is a planned feature for 5.0, while TPC is currently only considered a stretch goal and may not make it in.
For shape of the query projection, consider discussion #2266 (comment) and final decision from #21509
@ajcvickers @smitpatel Does this mean that this is gonna be in EF Core 6? Btw what is the workaround until TPC is ready? I am migrating a project from EF6 and my client used TPC. Is there any workaround example?
@pantonis this issue is currently in the backlog, and not in the 6.0 milestone - that means it's not currently planned. However, it has the consider-for-next-release label, which means it's a high-priority item in case there's extra time to complete it.
Until this is done, you won't be able to migrate your EF6 model as-is, but there may be various ways to modify it to make it compatible with EF Core (TPH/TPT, or possibly something else).
You can remodel things in some way so that you don't need TPC, but barring that, TPC is not currently supported.
this issue is currently in the backlog, and not in the 6.0 milestone
Since this issue is marked with ef6-parity it should be part of 6.0 release due to https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/plan#ef6-query-parity
@e-davidson we indeed intend to try to reach query parity with EF6, but TPC isn't a query feature, it's a mapping strategy.
@roji - Is there any update as to whether this will be included in EF Core 6? This is the main issue preventing us from updating from EF6. And, if not, will it definitely be included in the following release (e.g. a point release)?
@zejji this is currently a "stretch goal" for EF Core 6, meaning that we may get around to it if we have extra time - but haven't commit to it. If not, it will most likely only be included in EF Core 7, and not in a point release of EF Core 6 - we only release bug fixes in point releases.
this feature would be very much appreciated.. scrambling for workarounds, I have read a few times now that this prevents people from upgrading
Hi,
I have downloaded preview of EF 7 just to test the TPC implementation.
I am not sure is fully implemented and my question is will the final release support scenario like the following (which is fully supported by Entity Framework 6.4):
Thanks in advance
`
#region abstract entity classes
public abstract class BaseEntity
{ }
public abstract class AMOS_ORDERFORM : BaseEntity
{
[Key]
public decimal ORDERID { get; set; }
public string FORMNO { get; set; }
public string TITLE { get; set; }
public decimal VENDORID { get; set; }
[ForeignKey("VENDORID")]
public AMOS_ADDRESS VENDOR { get; set; }
public ICollection<AMOS_ORDERLINE> ORDERLINEs { get; set; }
}
public abstract class AMOS_SPAREUNIT : BaseEntity
{
[Key]
public decimal PARTID { get; set; }
public decimal PARTTYPEID { get; set; }
public AMOS_SPARETYPE PARTYPE { get; set; }
}
public abstract class AMOS_SPARETYPE : BaseEntity
{
[Key]
public decimal PARTTYPEID { get; set; }
public string PARTNAME { get; set; }
}
public abstract class AMOS_ORDERLINE : BaseEntity
{
[Key]
public decimal ORDERLINEID { get; set; }
public decimal ORDERID { get; set; }
public AMOS_ORDERFORM ORDER { get; set; }
public string NAME { get; set; }
public decimal? PARTID { get; set; }
public AMOS_SPAREUNIT PART { get; set; }
}
public abstract class AMOS_ADDRESS : BaseEntity
{
[Key]
public decimal ADDRESSID { get; set; }
public string CODE { get; set; }
public string ALPHACODE { get; set; }
}
public abstract class AMOS_ADDRESSCONTACT : BaseEntity
{
[Key]
public decimal ADDRESSCONTACTID { get; set; }
public AMOS_ADDRESS ADDRESS { get; set; }
public decimal? ADDRESSID { get; set; }
}
#endregion
public class ADDRESS : AMOS_ADDRESS
{ }
public class ADDRESSCONTACT : AMOS_ADDRESSCONTACT
{ }
public class ORDERFORM : AMOS_ORDERFORM
{ }
public class ORDERLINE : AMOS_ORDERLINE
{ }
public class SPAREUNIT : AMOS_SPAREUNIT
{ }
public class SPARETYPE : AMOS_SPARETYPE
{ }
`
@Luigi6821 This issue is still open, meaning that it hasn't been implemented yet. Your scenario does look like something we would support
Is there any sort of guesstimate of when this will likely start landing in the daily builds? Excited to start playing with this. :)
At some point after preview 3 probably
I haven't been following closely, but since this closed, is TPC now ready to be played with in the nightly builds?
@eraffel-MDSol Yes, starting with tomorrow's build
Hi,
Looking at the new build preview I was playing with TPC to check the following scenario:
`public abstract class AMOS_ADDRESS
{
public decimal ADDRESSID { get; set; }
public string CODE { get; set; }
public string ALPHACODE { get; set; }
}
public abstract class AMOS_ADDRESSCONTACT
{
public decimal ADDRESSCONTACTID { get; set; }
public AMOS_ADDRESS ADDRESS { get; set; }
public decimal ADDRESSID { get; set; }
}
#endregion
public class ADDRESS : AMOS_ADDRESS
{ }
public class ADDRESSCONTACT : AMOS_ADDRESSCONTACT
{
}
....
modelBuilder.Entity<AMOS_ADDRESS>().ToTable("ADDRESS", "AMOS");
modelBuilder.Entity<AMOS_ADDRESS>().HasKey(e => e.ADDRESSID);
modelBuilder.Entity<AMOS_ADDRESS>().Property(e => e.CODE).HasColumnName("CODE");
modelBuilder.Entity<AMOS_ADDRESS>().Property(e => e.ALPHACODE).HasColumnName("ALPHACODE");
modelBuilder.Entity<AMOS_ADDRESS>().Property(e => e.ADDRESSID).HasColumnName("ADDRESSID");
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().ToTable("ADDRESSCONTACT");
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().HasKey(e => e.ADDRESSCONTACTID);
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().Property(e => e.ADDRESSCONTACTID).HasColumnName("ADDRESSCONTACTID");
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().Property(e => e.ADDRESSID).HasColumnName("ADDRESSID");
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().HasOne(e => e.ADDRESS);`
The error I receive when I try to run
var o = dbContext.Set<ADDRESS>().OrderBy(a => a.ADDRESSID).Take(10);
is:
The corresponding CLR type for entity type 'AMOS_ADDRESS' cannot be instantiated, and there is no derived entity type in the model that corresponds to a concrete CLR type.
The same code is perfectly supported by EF6.
Is the TPC implementation still not yet completed ?
Regards
Luigi
@Luigi6821 Don't call modelBuilder.Entity<AMOS_ADDRESS>().ToTable("ADDRESS", "AMOS"); or modelBuilder.Entity<AMOS_ADDRESSCONTACT>().ToTable("ADDRESSCONTACT");. If the entity type is abstract it means that there should be no rows in the corresponding table. Instead call
modelBuilder.Entity<AMOS_ADDRESS>().UseTpcMappingStrategy();
modelBuilder.Entity<AMOS_ADDRESSCONTACT>().UseTpcMappingStrategy();
modelBuilder.Entity<ADDRESS>().ToTable("ADDRESS", "AMOS");
modelBuilder.Entity<ADDRESSCONTACT>().ToTable("ADDRESSCONTACT");Also, you can't yet configure a different column name for one of the tables, that's coming in #19811
How are relationships from outside of an TPC hierarchy into any of its entities handled, given that no table is generated for the base/abstract entity?
For example, are any of the following supported?
(assuming the Animals entity hierarchy stated in the announcement post)
// Example 1
class SomeExternalEntity {
// NOT SUPPORTED in TPC?
public Animal Animal { get; private set; }
}
// Example 2
class SomeOtherExternalEntity {
// NOT SUPPORTED in TPC?
public IList<Animal> Animals { get; private set; }
}
With both TPH and TPT, the following gets generated:
// Example 1
CREATE TABLE [dbo].[SomeExternalEntity](
[AnimalID] [nvarchar](36) NOT NULL,
) ON [PRIMARY]
ALTER TABLE [dbo].[SomeExternalEntity] ADD CONSTRAINT [PK_dbo.SomeExternalEntity] PRIMARY KEY CLUSTERED
(
[AnimalID] ASC
)
ALTER TABLE [dbo].[SomeExternalEntity] WITH CHECK ADD CONSTRAINT [FK_dbo.SomeExternalEntity_dbo.Animals_Id] FOREIGN KEY([Id])
REFERENCES [dbo].[Animals] ([Id])
@marchy The relationship is handled normally, but no constraint is made in the database. We covered this in the community standup yesterday at around minute 58.
@ajcvickers With regard to the previous question, how will this work with deletes? Referencing the example given in the video, if I were to delete one of the animals, will the "Human" that references that animal as its favorite be deleted automatically, or would I have to manage preventing "orphan" records like that myself, since there's no FK constraint to enforce that.
@marchy The database won't perform automatic deletes, but EF will.
@ajcvickers does this mean that TPC uses Bulk (i.e. set-based) update/delete (without loading data into memory) #795 under the hood or that related entities need to be in the change tracker for cascade to work properly?
@bachratyg They need to be in the change tracker.
That means orphans have to be managed manually.
@bachratyg That depends on your definition of "manually". If data is appropriately loaded, then EF manages them. But if you are looking for a pattern that works well with database constructs like constraints and Identity keys, then TPC is not for you.
Data being appropriately loaded would mean a read lock on the principal row in all possible transactions modifying the relationship, some clever use of concurrency checks, or a business process that guarantees no concurrent phantom inserts. Either way serious steps should be taken to keep data consistent and that more or less covers my definition of "manually".
Don't get me wrong, I don't feel either way about the implementation, I just want to be sure I fully understand the consequences.