Alternate Keys on Derived Types (TPT/TPH)

[bricelam]

When using inheritance, we should allow "candidate" keys on derived types. For example

modelBuilder.Entity<Person>();
modelBuilder.Entity<Employee>().AlternateKey(e => e.EmployeeNumber);

[gojanpaolo]

Hello! Are there any workarounds equivalent to this feature?

---

Cross-posting my question in stackoverflow which I think can be solved with this feature.

Given the following classes that models a TPT inheritance using EF Core 5:

public abstract class Entity
{
    public int Id { get; set; }
    public int PartitionId { get; set; }
    public Company Company { get; set; }
}
public class Employee : Entity { }
public class Company : Entity { }

How can the Entity.PartitionId be configured to be the FK for the Entity.Company navigation property?

I tried the following

public class MyContext : DbContext
{
    public MyContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Company>().ToTable("Company");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Entity<Entity>().ToTable("Entity");
        modelBuilder.Entity<Entity>(b =>
        {
            b.HasOne(e => e.Company)
            .WithMany()
            .HasForeignKey(e => e.PartitionId)
            .HasPrincipalKey(c => c.PartitionId);
        });
    }
}

---

which works as expected when reading data. e.g:

MyContext ctx = ...;
ctx.Set<Employee>().Include(_ => _.Company).ToList();

is correctly translated to

SELECT [e].[Id], [e].[PartitionId], [t].[Id], [t].[PartitionId]
FROM [Entity] AS [e]
INNER JOIN [Employee] AS [e0] ON [e].[Id] = [e0].[Id]
INNER JOIN (
    SELECT [e1].[Id], [e1].[PartitionId]
    FROM [Entity] AS [e1]
    INNER JOIN [Company] AS [c] ON [e1].[Id] = [c].[Id]
) AS [t] ON [e].[PartitionId] = [t].[PartitionId]

---

But I'm getting an exception when writing data. e.g.:

MyContext ctx = ...;

// company 1
ctx.Set<Company>().Add(new Company { PartitionId = 1 });
ctx.Set<Employee>().AddRange(new Employee { PartitionId = 1 }, new Employee { PartitionId = 1 }); // exception here

// company 2
ctx.Set<Company>().Add(new Company { PartitionId = 2 });
ctx.Set<Employee>().AddRange(new Employee { PartitionId = 2 }, new Employee { PartitionId = 2 });

ctx.SaveChanges();

System.InvalidOperationException
  HResult=0x80131509
  Message=The instance of entity type 'Employee' cannot be tracked because another instance with the same key value for {'PartitionId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.ThrowIdentityConflict(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.Add(TKey key, InternalEntityEntry entry, Boolean updateDuplicate)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.Add(TKey key, InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.Add(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.StartTracking(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges, Boolean modifyProperties)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState entityState, Boolean acceptChanges, Boolean modifyProperties, Nullable`1 forceStateWhenUnknownKey)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.PaintAction(EntityEntryGraphNode`1 node)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityEntryGraphIterator.TraverseGraph[TState](EntityEntryGraphNode`1 node, Func`2 handleNode)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.AttachGraph(InternalEntityEntry rootEntry, EntityState targetState, EntityState storeGeneratedWithKeySetTargetState, Boolean forceStateWhenUnknownKey)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.SetEntityState(InternalEntityEntry entry, EntityState entityState)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.SetEntityStates(IEnumerable`1 entities, EntityState entityState)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.AddRange(TEntity[] entities)

Thanks in advance!

[eveneveneven]

@gojanpaolo I think I have a similar problem.
It seems like the usage of .HasPrincipalKey(c => c.PartitionId) creates an alternate key with PartitionId and that means it has a unique index.
I wonder if it is possible to have this unique constraint filtered like filtered indexes which are also useful when using TPH.
I would like this unique index to be a filtered index at least, so that its only unique within the derived class/discriminator.

I'm not sure if this is related to this issue, but your comment seems related.

[user98392]

please add them there's a relevant use case it's when you need a composite (alternate) key in a relationship that includes the derived type.