Entity Framework multiple references to same table

Question

I'm having trouble creating my database with EF code-first. I have an entity Player and an entity friedship.

Each friendship references two players. One of the players is the sender, the other one is the receiver of the friendship.

This are my entities:

Player.cs

public class Player
{
    public int PlayerId { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Email { get; set; }

    [InverseProperty("Receiver")]
    public virtual List<Friendship> FriendshipsIncoming { get; set; }

    [InverseProperty("Sender")]
    public virtual List<Friendship> FriendshipsOutgoing { get; set; }
}

Friendship.cs

public class Friendship
{
    public int FriendshipId { get; set; }

    public int SenderId { get; set; }
    public int ReceiverId { get; set; }

    [ForeignKey("Sender")]
    public Player Sender { get; set; }

    [ForeignKey("Receiver")]
    public Player Receiver { get; set; }

    [Required]
    public bool Confirmed { get; set; }
}

I tried implementing the relationsships the way shown in this tutorial: http://www.entityframeworktutorial.net/code-first/inverseproperty-dataannotations-attribute-in-code-first.aspx

When trying to update the database with the "update-database" command i'm getting the following error message:

The ForeignKeyAttribute on property 'Receiver' on type 'Darta.WebApi.Models.Friendship' is not valid. The foreign key name 'Receiver' was not found on the dependent type 'Darta.WebApi.Models.Friendship'. The Name value should be a comma separated list of foreign key property names.

I also tried fixing the issue with fluent-api like shown here: http://csharpwavenet.blogspot.sg/2013/06/multiple-foreign-keys-with-same-table.html

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Friendship>()
            .HasRequired(b => b.Sender)
            .WithMany(a => a.FriendshipsOutgoing)
            .HasForeignKey(b=>b.SenderId);

        modelBuilder.Entity<Friendship>()
            .HasRequired(b => b.Receiver)
            .WithMany(a => a.FriendshipsIncoming)
            .HasForeignKey(b => b.ReceiverId);
    }

In this case I'm getting the following error:

Introducing FOREIGN KEY constraint 'FK_dbo.Friendships_dbo.Players_SenderId' on table 'Friendships' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

Answer 1

You should only need either the DataAnnotations or the FluentAPI. You don't need both. If you want to use the [ForeignKey] and [InverseProperty] attributes, then get rid of the FluentAPI code.

Also, note that in the [ForeignKey] and [InverseProperty] attributes, you need to specify the name of the column, not the navigation property.

public class Player
{
    public int PlayerId { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Email { get; set; }

    [InverseProperty("ReceiverId")]
    public virtual ICollection<Friendship> FriendshipsIncoming { get; set; }

    [InverseProperty("SenderId")]
    public virtual ICollection<Friendship> FriendshipsOutgoing { get; set; }
}

public class Friendship
{
    public int FriendshipId { get; set; }

    public int SenderId { get; set; }
    public int ReceiverId { get; set; }

    [ForeignKey("SenderId")]
    public Player Sender { get; set; }

    [ForeignKey("ReceiverId")]
    public Player Receiver { get; set; }

    [Required]
    public bool Confirmed { get; set; }
}

Answer 2

I'll correct the answer. InverseProperty must be a valid entity type. So in this case Friendship.Receiver, Friendship.Sender

public class Player { public int PlayerId { get; set; }

   [Required]
   public string Name { get; set; }

   [Required]
   public string Email { get; set; }

   [InverseProperty("Receiver")]
   public virtual ICollection<Friendship> FriendshipsIncoming { get; set; }

   [InverseProperty("Sender")]
   public virtual ICollection<Friendship> FriendshipsOutgoing { get; set; }

}

public class Friendship { public int FriendshipId { get; set; }

   public int SenderId { get; set; }
   public int ReceiverId { get; set; }

   [ForeignKey("SenderId")]
   public Player Sender { get; set; }

   [ForeignKey("ReceiverId")]
   public Player Receiver { get; set; }

   [Required]
   public bool Confirmed { get; set; }

}