c# - Use Table Per Hierarchy Inheritance for 1:2 Relationship

Question

I have the following Jobcard class with has two subclases MechanicalJobcard and PanelbeatingJobcard

public abstract class Jobcard : Entity, IAggregateRoot
{
    public int VehicleId { get; private set; }
    public int JobcardTypeId { get; protected set; }
    public string Opened_By { get; private set; } //TODO Identity Sever
    public DateTime Open_Date { get; private set; }
    public Vehicle Vehicle { get; private set; }
    public JobcardType JobcardType { get; private set; }

    public Jobcard()
    {
        Opened_By = "Job card opener";
        Open_Date = DateTime.Now;
    }
}
public class PanelBeatingJobcard : Jobcard
{
    public PanelBeatingJobcard()
    {
        JobcardTypeId = 1;
    }
}

public class MechanicalJobcard: Jobcard
{
    public MechanicalJobcard()
    {
        JobcardTypeId = 2;
    }
}

And i have a vehicle the has both MechanicalJobcard and PanelbeatingJobcard

public class Vehicle: Entity, IAggregateRoot
{
    public int Id{ get; set; }
   //Other properties here...

    public PanelBeatingJobcard PanelBeatingJobcard { get; set; }
    public MechanicalJobcard MechanicalJobcard { get; set; }
}

I can seem to use the fluent API to map these to the database.

I have tried

class PanelBeatingJobCardEntityTypeConfiguration: IEntityTypeConfiguration<PanelBeatingJobcard>
{
    public void Configure(EntityTypeBuilder<PanelBeatingJobcard> panelBeatinglJobcardConfig)
    {
        panelBeatinglJobcardConfig.HasOne(j => j.Vehicle).WithOne(v => v.PanelBeatingJobcard).HasForeignKey<Jobcard>(j => j.VehicleId);
    }
}
class MechanicalJobcardEntityTypeConfiguration: IEntityTypeConfiguration<MechanicalJobcard>
{
    public void Configure(EntityTypeBuilder<MechanicalJobcard> mechanicalJobcardConfig)
    {
        mechanicalJobcardConfig.HasOne(j => j.Vehicle).WithOne(v => v.MechanicalJobcard).HasForeignKey<Jobcard>(j => j.VehicleId);
    }
}

But i get the following error on Add-Migration:

You are configuring a relationship between 'PanelBeatingJobcard' and 'Vehicle' but have specified a foreign key on 'Jobcard'. The foreign key must be defined on a type that is part of the relationship

if i remove the both configurations i get the following error on Add-Migration:

Both relationships between 'Jobcard.Vehicle' and 'Vehicle' and between 'MechanicalJobcard' and 'Vehicle.MechanicalJobcard' could use {'VehicleId'} as the foreign key. To resolve this configure the foreign key properties explicitly on at least one of the relationships.

EDIT:

if change the Entity Type Configuration to the following, as suggested in the comments:

class PanelBeatingJobCardEntityTypeConfiguration: IEntityTypeConfiguration<PanelBeatingJobcard>
{
    public void Configure(EntityTypeBuilder<PanelBeatingJobcard> panelBeatinglJobcardConfig)
    {
        panelBeatinglJobcardConfig.HasOne(j => j.Vehicle).WithOne(v => v.PanelBeatingJobcard).HasForeignKey<PanelBeatingJobcard>(j => j.VehicleId);
    }
}
class MechanicalJobcardEntityTypeConfiguration: IEntityTypeConfiguration<MechanicalJobcard>
{
    public void Configure(EntityTypeBuilder<MechanicalJobcard> mechanicalJobcardConfig)
    {
        mechanicalJobcardConfig.HasOne(j => j.Vehicle).WithOne(v => v.MechanicalJobcard).HasForeignKey<MechanicalJobcard>(j => j.VehicleId);
    }
}

I get duplicate VehicleId keys (VehicleIdand VehicleId1) in the Migrations file:

 table.ForeignKey(
    name: "FK_jobcard_vehicles_VehicleId",
    column: x => x.VehicleId,
    principalSchema: "dbo",
    principalTable: "vehicles",
    principalColumn: "VehicleId",
    onDelete: ReferentialAction.Cascade);
table.ForeignKey(
    name: "FK_jobcard_vehicles_VehicleId1",
    column: x => x.VehicleId,
    principalSchema: "dbo",
    principalTable: "vehicles",
    principalColumn: "VehicleId",
    onDelete: ReferentialAction.Cascade);

question from:https://stackoverflow.com/questions/65647421/use-table-per-hierarchy-inheritance-for-12-relationship

Answer 1

I've created a full working example that doesn't have any issues creating the initial migration. They map to the same table and use the same foreign key.

Adding HasBaseType might be the problem. This will instruct EF that these two entities share a base type.

class Program
{
    static void Main()
    {

    }

    public class Vehicle
    {
        public int Id { get; set; }
        public PanelBeatingJobcard PanelBeatingJobcard { get; set; }
        public MechanicalJobcard MechanicalJobcard { get; set; }
    }

    public abstract class Jobcard
    {
        public int Id { get; set; }
        public int VehicleId { get; set; }
        public Vehicle Vehicle { get; set; }
    }

    public class PanelBeatingJobcard : Jobcard { }

    public class MechanicalJobcard : Jobcard { }

    public class Context: DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MechanicalJobcard>(buildAction =>
            {
                buildAction.HasBaseType<Jobcard>();
                buildAction.HasOne(e => e.Vehicle).WithOne(e => e.MechanicalJobcard).HasForeignKey<MechanicalJobcard>(e => e.VehicleId);
            });

            modelBuilder.Entity<PanelBeatingJobcard>(buildAction =>
            {
                buildAction.HasBaseType<Jobcard>();
                buildAction.HasOne(e => e.Vehicle).WithOne(e => e.PanelBeatingJobcard).HasForeignKey<PanelBeatingJobcard>(e => e.VehicleId);
            });
        }
    }
}

Migration Output

public partial class InitialMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Vehicle",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Vehicle", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "Jobcard",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                VehicleId = table.Column<int>(type: "int", nullable: false),
                Discriminator = table.Column<string>(type: "nvarchar(max)", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Jobcard", x => x.Id);
                table.ForeignKey(
                    name: "FK_Jobcard_Vehicle_VehicleId",
                    column: x => x.VehicleId,
                    principalTable: "Vehicle",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Jobcard_VehicleId",
            table: "Jobcard",
            column: "VehicleId",
            unique: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Jobcard");

        migrationBuilder.DropTable(
            name: "Vehicle");
    }
}