EF Core type configuration adds redundant FKs

Question

I am trying to configure a relationship between to entities in EFCore: Square and Position. Position has one (position) to many (squares) relationship with square named squares, as well as two one - to - one relationship between Square named enPassentTakablePawnOfWhite and enPassentTakablePawnOfBlack. Note that both the FKs as well as the PKs in both of the entities are shadow properties:

public class PositionTypeConfiguration : IEntityTypeConfiguration<Position>
    {
        public void Configure(EntityTypeBuilder<Position> builder)
        {
            builder.ToTable("Positions");
            builder.Property<Guid>("BoardId");
            builder.Property<Guid?>("CurrentPositionId");
            builder.HasOne<Board>().WithMany("previosPositions").HasForeignKey("BoardId");
            builder.HasOne<Board>().WithOne("currentPosition").IsRequired(false).HasForeignKey<Position>("CurrentPositionId");

            builder.HasMany<Square>("squares").WithOne().HasForeignKey("PositionId");
            builder.Property<Guid?>("EnPassentTakableWhitePawnId");
            builder.Property<Guid?>("EnPassentTakableBlackPawnId");
            builder.HasOne<Square?>("enPassentTakablePawnOfWhite").WithOne().HasForeignKey<Position>("EnPassentTakableWhitePawnId");
            builder.HasOne<Square?>("enPassentTakablePawnOfBlack").WithOne().HasForeignKey<Position>("EnPassentTakableBlackPawnId");

            builder.Property("canWhiteCastleKingSide");
            builder.Property("canWhiteCastleQueenSide");
            builder.Property("canBlackCastleKingSide");
            builder.Property("canBlackCastleQueenSide");
            builder.Property("_50MovesRuleCounter");
            builder.Property<Guid>("Id").ValueGeneratedOnAdd();
            builder.HasKey("Id");
        }
    }

 public class SqaureTypeConfiguration : IEntityTypeConfiguration<Square>
    {
        public void Configure(EntityTypeBuilder<Square> builder)
        {
            builder.ToTable("Sqaures");
            builder.Property(s => s.File);
            builder.Property<Guid>("PositionId");
            builder.Property(s => s.Row);
            //this is FK to some other, irrelavant entity.
            builder.HasOne(s => s.Occupier).WithOne().IsRequired(false).HasForeignKey<Square>("OccupierId").IsRequired(false);
            builder.Property<Guid>("Id");
            builder.HasKey("Id");
            builder.Property<Guid?>("OccupierId").IsRequired(false);
        }
    }

The problem is, that upon migration generation, the migration created the following two FKs to Position in Square:

  public partial class ChangedBoard : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            /...
            migrationBuilder.AddColumn<Guid>(
                name: "PositionId1",
                table: "Sqaures",
                type: "uniqueidentifier",
                nullable: true);

            migrationBuilder.CreateIndex(
                name: "IX_Sqaures_PositionId1",
                table: "Sqaures",
                column: "PositionId1");


            migrationBuilder.AddForeignKey(
                name: "FK_Sqaures_Positions_PositionId1",
                table: "Sqaures",
                column: "PositionId1",
                principalTable: "Positions",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        }
      //there is also ```"PositionId" in other migration

I recommend seeing this image instead, because it illustrates better:

Squares table:

Position table:

As you can see, both PositionId1, PositionId and BoardId are in there, even tho they sholden't. (only PositionId should exist, as part of the one-to many relationship) This has caused some real problems, as upon retrial of a Position entity using EFCore, I get all the squared duplicated (twice)

Why is that? And how can I resolve it? I tried Adding explicit type and FK name to the one-to-many relationship, changed configuration order etc.. nothing has worked.

Here are the entities:

Square:

public record Square(Piece? Occupier, int Row, int File) : IComparable<Square>
    {
        private Square() : this(returnNull())
        {

        }

        private static Piece returnNull()
        {
            return null;
        }

        public int Row
        {
            get; set;
        } = Row;
        public int File
        {
            get; set;
        } = File;
        public Square(Piece? occupier) : this(occupier, 0, 0)
        {
            this.Occupier = occupier;
        }
        public void SetLocation(int row, int file)
        {
            Row = row;
            File = file;
        }

        public int CompareTo(Square? other)
        {
            if(other is null)
                return 1;
            int result = this.Row.CompareTo(other.Row);
            if(result != 0)
                return result;
            return this.File.CompareTo(other.File);
        }
}

Position:

    public record Position
    {
        private List<Square> squares;
        public List<Square> Squares
        {
            get
            {
                return squares;
            }
        }
        private int At(int row, int file)
        {
            return row * 8 + file;
        }
        private bool canWhiteCastleKingSide;
        private bool canWhiteCastleQueenSide;
        private bool canBlackCastleKingSide;
        private bool canBlackCastleQueenSide;
        private Square? enPassentTakablePawnOfWhite;
        private Square? enPassentTakablePawnOfBlack;
        private const int RowAmount = 8;
        private const int FileAmount = 8;

        private int _50MovesRuleCounter;
        private readonly IDictionary<PieceType, Func<int, int, IEnumerable<Ply>>> possibleMovesGenerationFunctionsDictinary;
//.....

Edit: when I tried removing the entire type configurations of both Positions and squares (except the PK configuration), the FK from square to position has remained (only one of them)

Also, when I try to remove the Squares public property, after removing all the type configurations, the FK to position DOES go away, but the FK to board (which is not referenced at all, and should also not be here) still stays. changing the name of the public property from Squares to something else changes nothins, and I do still need this property so I cant just delete it. Marking it as [NoMapped] leadsto same result

Answer 1

The BoardId is clearly defined in your PositionTypeConfiguration class. If you don't need it just remove builder.Property<Guid>("BoardId"); from PositionTypeConfiguration.

Now about the PositionId1, PositionId issue.

You use this line to create a foreign key

builder.HasMany<Square>("squares").WithOne().HasForeignKey("PositionId");

And you have following field and property for the square collection in your Position class

private List<Square> squares;
public List<Square> Squares
{
    get
    {
        return squares;
    }
}

The problem is that you manually create a foreign key for a private field and EF automatically creates a foreign key for a public property. So you have 2 foreign keys configured hence 2 properties PositionId1 and PositionId

To resolve this issue use this line for config

builder.HasMany<Square>(x => x.Squares).WithOne().HasForeignKey("PositionId");

And remove private field, you can use private set instead in case you want it to be immutable

public class Position
{
    public List<Square> Squares { get; private set; }
}

Also i don't see the PositionId property on the Square class, without it you won't be able to save it to database using your model, because you've configured this column as not nullable.

And one last thing regarding record types.

EF core currently doesn't support change tracking for record types, so if you need that it's better to use regular classes

EDIT:

I don't see anything in the code you've posted that can cause BoardId to apear in Squares table. But it's possible that some other configuration in your project creates it.