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?

Tungus

I don't think this pertains to code first but check this post out. – Wallah

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

Damaging

Morteza, I've been following you here and on your blog. This works flawlessly! though the resulting table is named [MemberMembers] which is weird but that will be fixed in the RTM. Thanks mate! – Tungus

hello, I need to know how can we use multiple self referencing. for example on same sample code; public virtual IList<Member> Friends { get; set; } public virtual IList<Member> Parents { get; set; } – Soembawa

This worked perfectly, thanks. Do you know of any was to specify the name of the many-to-many table it will create? The MemberMembers thing works OK, but it looks ugly. – Babi

I did exactly what you explained, but when I query Friends of a user, I only get Friends from the joining table where the user is in the left column. – Nippers

@Morteza When I add HasMany I don't get the option to chain WithMany, – Setback

How do i do this in EF core latest – Likewise

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.

Dolmen

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.

Septal

True that. Hope RTM fulfills our requests. Code-first is just awesome! – Tungus

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

Screening

This even works without the InverseProperty attributes, as long as there are two user collections. EF will automatically assume they are counterparts. – Skim

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

Porche

To me it's can be many-to-many because it can be solved by introducing a table [Friends] with [LeftMemberId] and [RightMemberId]. Regardless of the correct naming, I can't get this to work :( – Tungus

Thanx mate, I didnt get a change to try your solution, but that was my initial solution to doing it manually. Can't wait for the RTM :) – Tungus