Efcore: Self referencing many to many relationship

[michalejoye]

I have a class DBUser which has a property Friends(which should be a collection of DBUser object). I am trying to figure out what to put on the OnModelCreate method to get EFCore to understand this relationship. I have the same relationship with the property BlockUsers

```C#
public class DBUser
{
public int Id { get; set; }

    public string Name { get; set; }
    public GamePlatform Platform { get; set; }
    public virtual ICollection<DBUser> Friends { get; } = new List<DBUser>();
    public virtual ICollection<DBUser> BlockUsers { get; } = new List<DBUser>();
}

```

[igoventura]

Why don't you create a many-to-many table? I mean, You can create a DBUser_Friends with two fields - id_user and id_friend. In the model You can refer one ICollection that have the fk of dbuser.

Something like:

public class DBUser
    {
        public int Id { get; set; }

        public string Name { get; set; }
        public GamePlatform Platform { get; set; }

        public virtual ICollection<DBUser_Friends> Friends { get; } = new List<DBUser_Friends>();
        public virtual ICollection<DBUser_BlockUsers> BlockUsers { get; } = new List<DBUser_BlockUsers>();
    }

public class DBUser_Friends
    {
        public int Id { get; set; }

        public int UserId { get; set; }
        public DBUser User { get; set; }

        public int FriendId { get; set; }
        public DBUser Friend { get; set; }
    }


public class DBUser_BlockUsers
    {
        public int Id { get; set; }

        public int UserId { get; set; }
        public DBUser User { get; set; }

        public int BlockedUserId { get; set; }
        public DBUser BlockedUser { get; set; }
    }

[ajcvickers]

@michalejoye The code @igoventura is essentially the way to go, although it's a little more idiomatic to use composite keys in the join tables, and there will need to be some fluent configuration:
```C#
public class DBUser
{
public int Id { get; set; }

public string Name { get; set; }
public GamePlatform Platform { get; set; }

public virtual ICollection<DBUserFriends> Friends { get; } = new List<DBUserFriends>();
public virtual ICollection<DBUserBlockedUsers> BlockUsers { get; } = new List<DBUserBlockedUsers>();

}

public class DBUserFriends
{
public int UserId { get; set; }
public virtual DBUser User { get; set; }

public int FriendId { get; set; }
public virtual DBUser Friend { get; set; }

}

public class DBUserBlockedUsers
{
public int UserId { get; set; }
public virtual DBUser User { get; set; }

public int BlockedUserId { get; set; }
public virtual DBUser BlockedUser { get; set; }

}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(b =>
{
b.HasKey(e => new { e.UserId, e.FriendId });
b.HasOne(e => e.User).WithMany(e => e.Friends);
b.HasOne(e => e.Friend).WithMany().OnDelete(DeleteBehavior.ClientSetNull);
});

modelBuilder.Entity<DBUserBlockedUsers>(b =>
{
    b.HasKey(e => new { e.UserId, e.BlockedUserId });
    b.HasOne(e => e.User).WithMany(e => e.BlockUsers);
    b.HasOne(e => e.BlockedUser).WithMany().OnDelete(DeleteBehavior.ClientSetNull);
});

}
```
It's worth pointing out a few things here:

• There are two many-to-many relationships--one for Friends and one for BlockedUsers. However, only one side of each has a navigation property, so you can navigate from a user to their friends, but not from a user back to who has friended them. To be able to go back, add in the inverse nav props
• SQL Server cannot handle cascade deletes on both directions, so one side is set not to cascade delete.

Many-to-many relationships can be found in the docs here: https://docs.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many

[ajcvickers]

See also https://github.com/aspnet/EntityFramework.Docs/issues/815

[igoventura]

Basically is what @ajcvickers did. I just don't made that way because I've programmed directly at github comment. ;)

[ajcvickers]

@dbag1010101 See https://docs.microsoft.com/en-us/ef/core/modeling/relationships