One column with foreign key to multiple tables inf EntityFramework Core

Question

Is it possible to model such relation in EF Core?

class EntityA
{
public EntityAId Id {get;set;}
public string Name {get;set;}
}

class EntityB
{
public EntityBId Id {get;set;}
public string Name {get;set;}
}

enum TableTypes {A,B}

class TransitionEntity
{
public int Id {get;set;}
public string TransitionName {get;set;}
public TableTypes  TableType {get;set;}
public TableTypeId int {get;set;}
}

EntityAId and EntityBId are strongly typed entity identifiers.

The idea is when table containing TransitionEntity has:

• TransitionEntity.TableType = TableTypes.A then use relation TransitionEntity.TableTypeId == EntityA.Id.Value
• when TransitionEntity.TableType = TableTypes.A then use relation TransitionEntity.TableTypeId == EntityB.Id.Value

I'd like to have one-to-one relation. I tried to use TPH Hierarchy approach but no luck. Is it even possible in EF Core?

Answer 1

For all the others, I figured this out and it is possible. Using TPH is the key.

1. Create proper inheritance:

abstract class TransitionEntity
{
public int Id {get;set;}
public string TransitionName {get;set;}
public TableTypes  TableType {get;set;}

}

class TransitionEntityA:TransitionEntity
{
public EntityAId EntityAId {get;set;}
}

class TransitionEntityB:TransitionEntity
{
public EntityBId EntityBId {get;set;}
}

2. Configure TransitionEntity fluent builder

        public void Configure(Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<TransitionEntity> builder)
        {
            builder.HasKey(b => b.Id);
            builder.Property(b => b.Id) 
//other props
builder.HasDiscriminator<TableTypes >("TableType")
    .HasValue<TransitionEntityA>(TableTypes.A)
    .HasValue<TransitionEntityN>(TableTypes.B)

3. Configure proper overrides using fluent api.

Configure(Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<TransitionEntityA> builder)
 {
     builder.Property(d => d.TableTypeId ).HasColumnName("TableTypeId");

 }
Configure(Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<TransitionEntityB> builder)
 {
     builder.Property(d => d.TableTypeId ).HasColumnName("TableTypeId");

 }

4. Configure EntityA and EntityB using fluent api.

  public void Configure(EntityTypeBuilder<EntityA > builder)
  {
      builder.HasKey(b => b.Id);
      builder.Property<EntityAId>("Id")
      .HasConversion(id => id.Value, value => new EntityAId(value))
//other props
//you can extend class to use navigation, then use it like this
builder.HasOne(b => b.TransitionEntityA)
    .WithOne(p => p.TransitionEntity)
    .HasForeignKey<TransitionEntityA>(p => p.EntityAId );

Configure EntityB in the same way as EntityA. This approach initialy did not work because I had different vales than TableType.A and TableType.B in actual column TableType in TransitionEntity table. So I think it is important to cover all existing values from column which serves as discriminator.

Hope it helps.