I'm working with Entity Framework and configuring relationships between the ConfirmationSkill, Skill, and Qualification entities. When I create a migration, an extra foreign key QualificationId appears in the ConfirmationSkills table. I have set up a composite primary key for SkillId and QualificationId, but during migration, an additional foreign key for QualificationId is being created.

Here is the configuration code for the ConfirmationSkill entity:

internal sealed class ConfirmationSkillConfiguration : IEntityTypeConfiguration<ConfirmationSkill>
{
    public void Configure(EntityTypeBuilder<ConfirmationSkill> builder)
    {
        builder.ToTable(DbNames.ConfirmationSkillsTable);

        builder.HasKey(s => new { s.SkillId, s.QualificationId })
            .HasName(DbNames.ConfirmationSkillsPrimaryKey);

        builder.Property(s => s.SkillId)
            .HasColumnName(DbNames.SkillIdColumn)
            .ValueGeneratedNever();

        builder.Property(s => s.QualificationId)
            .HasColumnName(DbNames.QualificationIdColumn)
            .ValueGeneratedNever();

        builder.Property(s => s.CreationDate)
            .HasColumnName(DbNames.CreationDateColumn)
            .IsRequired();

        builder.HasOne<Skill>()
            .WithMany()
            .HasForeignKey(s => s.SkillId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasOne<Qualification>()
            .WithMany()
            .HasForeignKey(s => s.QualificationId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

Here's the entity:

public sealed class ConfirmationSkill
{
    private readonly Guid _qualificationId;

    private readonly Guid _skillId;

    private DateTime _creationDate;

    private ConfirmationSkill(Guid qualificationId, Guid skillId, DateTime creationDate)
    {
        QualificationId = qualificationId;
        SkillId = skillId;
        CreationDate = creationDate;
    }

    public Guid QualificationId
    {
        get => _qualificationId;
        private init => _qualificationId = Guard.Against.NullOrEmpty(value);
    }

    public Guid SkillId
    {
        get => _skillId;
        private init => _skillId = Guard.Against.NullOrEmpty(value);
    }

    public DateTime CreationDate
    {
        get => _creationDate;
        private set => _creationDate = ConfirmationSkillValidator.ValidateCreationDate(value);
    }

    internal static ConfirmationSkill Create(Guid qualificationId, Guid skillId, DateTime creationDate)
    {
        return new ConfirmationSkill(qualificationId, skillId, creationDate);
    }
}

I'm working with Entity Framework and configuring relationships between the ConfirmationSkill, Skill, and Qualification entities. When I create a migration, an extra foreign key QualificationId appears in the ConfirmationSkills table. I have set up a composite primary key for SkillId and QualificationId, but during migration, an additional foreign key for QualificationId is being created.

Here is the configuration code for the ConfirmationSkill entity:

internal sealed class ConfirmationSkillConfiguration : IEntityTypeConfiguration<ConfirmationSkill>
{
    public void Configure(EntityTypeBuilder<ConfirmationSkill> builder)
    {
        builder.ToTable(DbNames.ConfirmationSkillsTable);

        builder.HasKey(s => new { s.SkillId, s.QualificationId })
            .HasName(DbNames.ConfirmationSkillsPrimaryKey);

        builder.Property(s => s.SkillId)
            .HasColumnName(DbNames.SkillIdColumn)
            .ValueGeneratedNever();

        builder.Property(s => s.QualificationId)
            .HasColumnName(DbNames.QualificationIdColumn)
            .ValueGeneratedNever();

        builder.Property(s => s.CreationDate)
            .HasColumnName(DbNames.CreationDateColumn)
            .IsRequired();

        builder.HasOne<Skill>()
            .WithMany()
            .HasForeignKey(s => s.SkillId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasOne<Qualification>()
            .WithMany()
            .HasForeignKey(s => s.QualificationId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

Here's the entity:

public sealed class ConfirmationSkill
{
    private readonly Guid _qualificationId;

    private readonly Guid _skillId;

    private DateTime _creationDate;

    private ConfirmationSkill(Guid qualificationId, Guid skillId, DateTime creationDate)
    {
        QualificationId = qualificationId;
        SkillId = skillId;
        CreationDate = creationDate;
    }

    public Guid QualificationId
    {
        get => _qualificationId;
        private init => _qualificationId = Guard.Against.NullOrEmpty(value);
    }

    public Guid SkillId
    {
        get => _skillId;
        private init => _skillId = Guard.Against.NullOrEmpty(value);
    }

    public DateTime CreationDate
    {
        get => _creationDate;
        private set => _creationDate = ConfirmationSkillValidator.ValidateCreationDate(value);
    }

    internal static ConfirmationSkill Create(Guid qualificationId, Guid skillId, DateTime creationDate)
    {
        return new ConfirmationSkill(qualificationId, skillId, creationDate);
    }
}

• c#
• entity-framework-core
• ef-core-9.0

• What do the entities look like? ConfirmationSkill looks like a bridge entity and in EF Core 7 and later doesn't even have to exist - EF Core itself will infer it from the entity properties. – Panagiotis Kanavos Commented Jan 30 at 10:36
• @PanagiotisKanavos, I added ConfirmationSkill entity – LemonPotion Commented Jan 30 at 10:51
• What version of EF are you using? – Alireza Maddah Commented Jan 30 at 10:52
• I use EF Core 9 – LemonPotion Commented Jan 30 at 10:52
• @AlirezaMaddah, EF Core 9 – LemonPotion Commented Jan 30 at 10:57

1 Answer 1

You don't need a Primary Key to not repeat values ​​in this relationship table. Normally I use a unique Key. However, if this is a requirement, you can do it like this on one side of the relationship:

 builder.HasMany(x => x.Qualifications)
   .WithMany(x => Skills);
   .UsingEntity<ConfirmationSkill>(
       l => l.HasOne<Skill>().WithMany().HasForeignKey(x => x.SkillId),
       r => r.HasOne<Qualification>().WithMany().HasForeignKey(x => x.QualificationId),
       j =>
       {
           j.HasKey(DbNames.SkillIdColumn, DbNames.QualificationIdColumn);
           j.ToTable(DbNames.ConfirmationSkillsTable); 
       })

Works for me