EF Core type configuration adds redundant FKs

Viewed 336

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:

The Squares table

Position table:

The 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

2 Answers

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.

The main reason is in this code block

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

You have added the ForeignKey for Square, but I think that you may not notice that it is case sensitive, so you would just need to change the "s" to "S".

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

That will make the migration mechanism realize that the registration is the same as the public List<Square> Squares field at public record Position. It will not register it again. Or, you could remove the line of codes.

The question is that do you want to register ForeignKey on private List<Square> squares? Or just the case issue? I don't really understand what is your design, so I keep the original form of code.

I have used the code you provide for migration, but the BoardId field does not appear in the Sqaures table. If you still not find the reason, may you provide other model configurations about the Square?

enter image description here

Related