EF code first mapping table with 2 FK's from same class

Question

I'm trying to create a Friendship mapping table that has 2 FK's that originate from the same class (User). On Add-Migration I get the following error:

Unable to determine the principal end of an association between the types 'GameAPI.Models.UserFriendMap' and 'GameAPI.Models.User'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Here are my two model classes.

public class User
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int UserID { get; set; }

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

    public ICollection<UserFriendMap> Friendships { get; set; }
}

public class UserFriendMap
{
    [Required]
    [Key]
    [Column(Order = 1)]
    public int UserID { get; set; }

    [Required]
    [Key]
    [Column(Order = 2)]
    public int FriendID { get; set; }

    [ForeignKey("UserID"), InversePropertyAttribute("Friendships")]
    public User User { get; set; }

    [ForeignKey("FriendID"), InversePropertyAttribute("Friendships")]
    public User Friend { get; set; }

    [Required]
    public DateTime FriendshipDate { get; set; }
}

I tried override the OnModelCreating method in the DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserFriendMap>()
        .HasKey(m => new { m.UserID, m.FriendID });

    modelBuilder.Entity<UserFriendMap>()
            .HasRequired(m => m.User)
            .WithMany()
            .HasForeignKey(m => m.UserID);

    modelBuilder.Entity<UserFriendMap>()
            .HasRequired(m => m.Friend)
            .WithMany()
            .HasForeignKey(m => m.FriendID);

}

This causes a new error message to be displayed.

Schema specified is not valid. Errors: The relationship 'GameAPI.Models.UserFriendMap_User' was not loaded because the type 'GameAPI.Models.User' is not available.

Any help would be greatly appreciated. Thanks in advance!!

Answer 1

I always try to approach such problems "bottom up", i.e. first try the class model without any mapping (fluent nor data annotations):

public class User
{
    public int UserID { get; set; }
    public string Username { get; set; }
    public virtual ICollection<UserAssociation> Associations { get; set; }
}

public class UserAssociation
{
    public int UserID { get; set; }
    public int FriendID { get; set; }

    public DateTime FriendshipDate { get; set; }

    public virtual User User { get; set; }
    public virtual User Friend { get; set; }
}

(Using the more neutral name UserAssociation).

That quickly failed because there was no key defined for UserAssociation. So I added the key annotations:

[Key, Column(Order = 1)]
public int UserID { get; set; }

[Key, Column(Order = 2)]
public int FriendID { get; set; }

Now the model was created, but UserID and FriendID were not used as foreign key. Instead, EF had added two foreign key fields itself. Not good. So I added foreign key instructions:

[Key, Column(Order = 1)]
[ForeignKey("User"), InverseProperty("Associations")]
public int UserID { get; set; }

[Key, Column(Order = 2)]
[ForeignKey("Friend"), InverseProperty("Associations")]
public int FriendID { get; set; }

This caused an exception:

Introducing FOREIGN KEY constraint ... may cause cycles or multiple cascade paths.

This is because both foreign keys are created with cascaded delete. SQL Server just doesn't allow two cascading foreign keys in one table. Preventing cascading delete can only be done by fluent mapping, so I removed all annotations (I don't like to mix fluent mapping and annotations) and added these mappings:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasMany(u => u.Associations)
                .WithRequired(a => a.User).HasForeignKey(a => a.UserID)
                .WillCascadeOnDelete(false);
    modelBuilder.Entity<User>().HasMany(u => u.Associations)
                .WithRequired(a => a.Friend).HasForeignKey(a => a.FriendID)
                .WillCascadeOnDelete(false);

    modelBuilder.Entity<UserAssociation>()
                .HasKey(a => new {a.UserID, a.FriendID});
}

Now it was bingo. As you see, you were close, but you started the fluent mappings from the association's side. Also, you had WithMany() in stead of WithMany(u => u.Associations)). However, using mappings like that I couldn't get it working (somehow, EF didn't seem to know the User class in time).

Another point is that when adding data, EF only accepted new UserAssociation if the were added to their corresponding DbSet of the context, not if they were added to User.Associations. Also, when creating an association between existing users, I had to set the primitive foreign key values in the new UserAssociation. Setting the object references resulted in

An error occurred while saving entities that do not expose foreign key properties for their relationships.

Which doesn't make sense, because UserAssociation does expose these key properties.

So it looks like with these self-referencing many to many associations with an explicit junction class we're only barely dodging all kinds of issues EF has in store. Doesn't feel good.

Answer 2

I have a solution to your problem but you need to modify your model. To create the friend relationship I create a Friend class that inherit of User. In this class is where I declare the property FriendshipTime, you don't need to declare an Id for it, it uses the Id of User. Now, in User you should have a collection of Friend.

public class User
{  
    [Key]
    public int Id { get; set; }

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

    public ICollection<Friend> Friendships { get; set; }
}

public class Friend:User
{
    public int UserID { get; set; }

    [ForeignKey("UserID"), InversePropertyAttribute("Friendships")]
    public User User { get; set; }

    [Required]
    public DateTime FriendshipDate { get; set; }
}

With this model you can do something like this:

 var user = new User()
          {
            Username = "User1",
            Friendships = new List<Friend>() { new Friend() { Username ="User2", FriendshipDate = DateTime.Now }, new Friend() { Username = "User3", FriendshipDate = DateTime.Now } }
          };

Other aclaration that I want to do is in the case where the Key field is an Integer, Code First defaults to DatabaseGeneratedOption.Identity. With a Guid, you need to explicitly configure this. These are the only types that you can configure to be Identity when Code First is generating the database