Why Does Entity Framework Create an Extra Foreign Key in a Composite Key Setup?

Below is an explanation of what’s happening and one common way to resolve it.

---

Why the Extra Foreign Key Appears

Entity Framework Core uses conventions to “connect the dots” between keys and relationships. In your setup:

• You’ve defined a composite primary key using SkillId and QualificationId.
• You configure two relationships by calling HasOne() and HasOne() along with their respective foreign keys.

Even though you supply properties for both foreign keys, EF Core doesn’t see any navigation properties on ConfirmationSkill (or on the other side) that explicitly tie the FK property to the relationship. In those situations, EF Core may choose to create a shadow property to represent the relationship it thinks isn’t fully configured. This can lead to an extra column being created (in your case for QualificationId) because EF has inferred an additional relationship.

---

How to Fix It

The most straightforward fix is to add navigation properties to your dependent entity so that the FK properties are clearly associated with the correct relationship. For example:

CSHARPCopied
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);
    }

    // Explicit navigation properties
    public Skill Skill { get; private set; }
    public Qualification Qualification { get; private set; }

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

Then, update your configuration to include these navigation properties:

CSHARPCopied
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(s => s.Skill)  // Now referencing the navigation property
            .WithMany() // Or specify .WithMany(t => t.ConfirmationSkills) if available
            .HasForeignKey(s => s.SkillId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasOne(s => s.Qualification)  // Now referencing the navigation property
            .WithMany() // Or specify .WithMany(q => q.ConfirmationSkills)
            .HasForeignKey(s => s.QualificationId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

With these navigation properties set up explicitly, EF Core knows exactly how to associate your FK properties with the relationship. There is no ambiguity, and it will not create an extra shadow FK column.

---

Alternative Approaches

If for some reason you want to keep your domain model free of navigation properties, you can also try to “tell” EF Core to ignore the extra relationship by:

• Making sure that the relationship configuration in your Fluent API does not conflict
• Using the Ignore method on any properties EF Core might have inferred as shadow properties

However, adding the explicit navigation properties is the most straightforward and recommended approach.

---

Summary

The extra foreign key appears because, without navigation properties, EF Core ends up creating a shadow relationship based on convention. Adding the appropriate navigation properties in your entity and updating the configuration ensures that EF Core recognizes the intended relationships correctly, removing the extra column from your migration.

This should resolve the issue.