Self-referencing many-to-many recursive relationship code first Entity Framework

Question

I can't seem to make this work at all

class Member
{
    public virtual IList<Member> Friends { get; set; }
    [Key]
    public int MemberId { get; set; }
    public string Name{ get; set; }
}

I tried adding Mappings but in vain. Is there a way to do so with CTP5?

Answer 1

By convention, Code First will take uni-directional associations as one to many. Therefore you need to use fluent API to let Code First know that you want to have a many to many self referencing association:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Member>().HasMany(m => m.Friends).WithMany().Map(m =>
        {
            m.MapLeftKey("MemberId");
            m.MapRightKey("FriendId");
            m.ToTable("MembersFriends");
        }
    );
}

Answer 2

If I am correct you can influence the many to many table name with this code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Member>().HasMany(m => m.Friends).WithMany().Map(m =>
        {
            m.MapLeftKey("MemberId");
            m.MapRightKey("FriendId");
            m.ToTable("MembersFriends");
        }
    );
}

hope this helps.

Answer 3

You can get this to work in EF 4 CTP5 using Model-First, but the CTP5 Code First has too many bugs with self-referential and polymorphic query configurations to use Code First for such scenarios. Morteza Manavi (see other answer) has documented several of them on his blog.

Answer 4

I wanted to get this done without having to write fluent API code. So here is my take.

The following example trying to save whomever other users profiles the user visited and who visited that user profile. Sadly, the following example doesn't support extra properties other than two ids of the visiting and the visited user.

The navigation properties had been linked using the InversePropertyAttribute.
see more about it in entityframework.net and entityframeworktutorial.net

Model ↴

public class User
{   
    [InverseProperty(nameof(User.VisitingUsers))]
    public virtual List<User> VisitedUsers { get; set; }
    [NotMapped]
    public long? VisitedUsersCount { get { return this.VisitedUsers == null ? 0 : this.VisitedUsers.Count(); } }

    [InverseProperty(nameof(User.VisitedUsers))]
    public virtual List<User> VisitingUsers { get; set; }
    [NotMapped]
    public long? VisitingUsersCount { get { return this.VisitingUsers == null ? 0 : this.VisitingUsers.Count(); } }
}

Generated Migration Code ↴

CreateTable(
    "dbo.UserUsers",
    c => new
    {
        User_Id = c.Long(nullable: false),
        User_Id1 = c.Long(nullable: false),
    })
    .PrimaryKey(t => new { t.User_Id, t.User_Id1 })
    .ForeignKey("dbo.Users", t => t.User_Id)
    .ForeignKey("dbo.Users", t => t.User_Id1)
    .Index(t => t.User_Id)
    .Index(t => t.User_Id1);

Answer 5

Your example is not a many-to-many relationship, it is more of a recursive relationship.

I am not sure how to fix it. But the problem with your code is that your will get two fields on the same row with the same name. MemberId for the id of the row and MemberId for the id of the friend.

Edit

Try doing it like this:

    class Member 
    { 
        [Key]
        public int MemberId { get; set; } 
        public string Name { get; set; }

        public virtual IList<FriendRelationship> Friends { get; set; } 

    }

    class FriendRelationship
    {
        [Key]
        public int RelationshipId { get; set; }

        public Member Friend { get; set; }
    }