CODEWITHSUNDEEP
c#entity-frameworkmany-to-manyentity-framework-migrations
NanoBob
NanoBob

Reputation: 80

Entity Framework many to many on same table

I am using C# and Entity Framework, code first migrations.

I've got a class, user, which has a many to many relation with itself, with the property Friends. Currently I have this code responsible for creating that database structure and the relationship itself.

[InverseProperty("ReverseFriends")]
public virtual ICollection<User> Friends { get; set; }
[InverseProperty("Friends")]
public virtual ICollection<User> ReverseFriends { get; set; }

However I suspect/hope there is a way to do this with only a single navigation property, instead of two. Is this the case? Or is the way I have done so above the way to do a many to many on a single table?

Upvotes: 1

Views: 2478

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205619

Your model and data annotations are just fine.

However I suspect/hope there is a way to do this with only a single navigation property, instead of two.

Negative. Relationships always include two entities (although they could be of one and the same type as here). And many to many w/o fluent API require two navigation properties.

Imagine many to many relationship between two entities A and B:

public class A
{
    [InverseProperty("As")]
    public ICollection<B> Bs { get; set; }
}

public class B
{
    [InverseProperty("Bs")]
    public ICollection<A> As { get; set; }
}

Note that InverseProperty annotation is optional in this case because EF can automatically correlate the two navigation properties and form a single many to many relationship.

Now let substitute A == B. What we got is:

public partial class A
{
    [InverseProperty("As")]
    public ICollection<A> Bs { get; set; }
}

public partial class A
{
    [InverseProperty("Bs")]
    public ICollection<A> As { get; set; }
}

or after consolidation:

public class A
{
    [InverseProperty("As")]
    public ICollection<A> Bs { get; set; }
    [InverseProperty("Bs")]
    public ICollection<A> As { get; set; }
}

i.e. exactly what you did.

If you want to create the relationship with just one navigation property, then you must use fluent API. For instance, if you want to keep only Friends, e.g.

public class User
{
    // ...
    public virtual ICollection<User> Friends { get; set; }
}

you would need the following fluent config:

modelBuilder.Entity<User>()
   .HasMany(u => u.Friends)
   .WithMany(); // no inverse navigation property

But note that while this allows you to define the relationship, the Friends property will still contain only the elements from the join table where let say UserId equals User.Id. It won't contain the elements of the join table where FriendId equals User.Id.

The best is to keep both navigation properties and explicitly configure the join table, e.g.

public class User
{
    // ...
    public virtual ICollection<User> Friends { get; set; }
    public virtual ICollection<User> FriendOf { get; set; }
}

modelBuilder.Entity<User>()
   .HasMany(u => u.Friends)
   .WithMany(u => u.FriendOf);
   .Map(m => m.ToTable("UserFriends")
       .MapLeftKey("UserId")
       .MapRightKey("FriendId"));

This allows you to get all join table elements where user is either UserId or FriendId, i.e. all user friends:

var query = db.Users.Select(u => new
{
    User = u,
    AllFriends = u.Friends.Concat(u.FriendOf).ToList(),
});

Upvotes: 2

Related Questions