Code first Generating Extra Columns

Question

I have a set of players that are linked together through a FriendLinker table.

The table links two players together (in this case, its player->friend). I have the player setup in the following way:

public class Player
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key, Column(Order=0)]
    public Guid PlayerId { get; set; }

    public virtual ICollection<FriendLinker> Friends { get; set; }

    [Required]
    public string Password { get; set; } //Will be switched to byte[] for hash

    [MaxLength(100)]
    [Index(IsUnique = true)]
    public string Username { get; set; }
}

and the linker table is setup like this:

public class FriendLinker
{
    [Key]
    public int FriendLinkerId { get; set; }

    [Required]
    public Player Player { get; set; }

    [Required]
    public Player Friend { get; set; }
}

However, this is generating the following migration:

CreateTable(
"dbo.FriendLinkers",
c => new
{
     FriendLinkerId = c.Int(nullable: false, identity: true),
     Player_PlayerId = c.Guid(),
     Friend_PlayerId = c.Guid(nullable: false),
     Player_PlayerId1 = c.Guid(nullable: false),
})
.PrimaryKey(t => t.FriendLinkerId)
.ForeignKey("dbo.Players", t => t.Player_PlayerId)
.ForeignKey("dbo.Players", t => t.Friend_PlayerId, cascadeDelete: false)
.ForeignKey("dbo.Players", t => t.Player_PlayerId1, cascadeDelete: false)
.Index(t => t.Player_PlayerId)
.Index(t => t.Friend_PlayerId)
.Index(t => t.Player_PlayerId1);

The result creates an extra column Player_PlayerId1. When I do player.Friends.add(..), the playerId is inserted into PlayerId1.

What should I do to prevent that extra column PlayerId1 being generated?

Answer 1

It happens, because FriendLinker class has two links to Player class, but Player class has only one link and EF little confused about it, as a result additional column Player_PlayerId1 appears, this column is exactly linked to Player(ICollection property, that is why: When I do player.Friends.add(..), the playerId is inserted into PlayerId1.). Two other columns, that you specified, considered as implicitly linked to Player class. You can fix it by adding second link to FriendLinker class at Player class declaration and specify to which concrete properties this links will be related by means of InverseProperty attribute's constructor parameter:

public class Player
{
    [InverseProperty("Player")]
    public virtual ICollection<FriendLinker> Players { get; set; }

    [InverseProperty("Friend")]
    public virtual ICollection<FriendLinker> Friends { get; set; }

    [Required]
    public string Password { get; set; } //Will be switched to byte[] for hash

    [MaxLength(100)]
    [Index(IsUnique = true)]
    public string Username { get; set; }
}