How to implement an self referencing many to many relationship with Entity Framework Core 1.1?

Question

I'm following this tutorial to implement my friendship system with EF Core 1.1 : http://www.codedodle.com/2014/12/social-network-friends-database.html

Friendship.cs

public class Friendship
{
    public Guid ApplicationUserId { get; set; }
    public ApplicationUser ApplicationUser { get; set; }

    public Guid FriendId { get; set; }
    public ApplicationUser Friend { get; set; }

    public StatusCode Status { get; set; }

    public Guid ActionUserId { get; set; }
    public ApplicationUser ActionUser { get; set; }

    public byte[] Timestamp { get; set; }
}

public enum StatusCode
{
    Pending = 0,
    Accepted = 1,
    Declined = 2,
    Blocked = 3
}

ApplicationUser.cs

public class ApplicationUser : IdentityUser<Guid>
{
    ...

    public ICollection<Friendship> FriendRequestsMade { get; set; }

    public ICollection<Friendship> FriendRequestsAccepted { get; set; }

    public byte[] Timestamp { get; set; }
}

MyDbContext.cs

public class SocialCircleContext : IdentityDbContext<ApplicationUser, Role, Guid>
{

     builder.Entity<Friendship>()
        .HasIndex(x => new { x.ApplicationUserId, x.FriendId })
        .IsUnique();

     builder.Entity<Friendship>()
        .HasOne(x => x.ApplicationUser)
        .WithMany(y => y.FriendRequestsMade)
        .HasForeignKey(x => x.ApplicationUserId).OnDelete(DeleteBehavior.Restrict);

    builder.Entity<Friendship>()
        .HasOne(x => x.Friend)
        .WithMany(y => y.FriendRequestsAccepted)
        .HasForeignKey(x => x.FriendId);         
}

Result of Add-Migration InitialMigration

Unable to determine the relationship represented by navigation property 'Friendship.ActionUser' of type 'ApplicationUser'. Either manually configure the relationship, or ignore this property from the model.

Also, as EF Core is moving fast, I found many different ways to do this. I'm not sure of my implementation of the self-referencing many-to-many relationship, can someone give me some advice?

1. How can I define the relationship between Friendship.ActionUser and ApplicationUser ?
2. Any advice on what is the correct way to implement this self referencing many-to-many relationship? EF Core is moving fast, I found many different ways online but they seem out-of-date

Thank you! :)

Answer 1

Ideally once the ambiguity in relationship discovery is resolved EF should create rest of relationship by convention but it is not happening due to bug. (Filed Bug)

Your model classes are correct for what you are trying to do. To make EF build the model successfully, there are few missing pieces to fill in.

First, lets resolve exception you are seeing. Your ApplicationUser class has 2 collection navigation pointing to Friendship. And Friendship class has 3 reference navigation pointing ApplicationUser. While EF Core does a good job at creating relationships by convention, in this case, it does not know how the create navigation-inverse navigation pairs. So user input is needed through annotations/fluent API. In your case, you are creating 2 relationships using fluent API which uses up 2 navigations each side. This leaves us with only navigation Friendship.ActionUser without any relationship. At this point, EF Core does not have any confusion about how to create relationship out of it but due to the bug it is not doing so. That means you have to configure this relationship manually using fluent API.

builder.Entity<Friendship>().HasOne(e => e.ActionUser).WithOne().HasForeignKey<Friendship>(e => e.ActionUserId);

This would create one-to-one relationship. You can use HasOne(...).WithMany() to create one-to-many relationship.

This would get you past above error. Now you will see another error since the class Friendship does not have primary key defined. While article says to create a unique index, but for many-to-many join table, the join table is configured having composite PK so that it can represent unique connection. So instead of calling HasIndex like above, you should use following code.

builder.Entity<Friendship>().HasKey(e => new { e.ApplicationUserId, e.FriendId });

After above code, you can remove HasIndex call because PK are always unique and most databases have index defined for PK.

With above changes, your model should be good to work.

Other thing: Since relationship defined by Friendship.ActionUser is somewhat ambiguous for it to be one-to-one or one-to-many, perhaps it should not be a relationship at all. ActionUserId should take one of the value of ApplicationUserId or FriendId, you can easily access the ActionUser by picking one of those navigations. You can either make ActionUser [NotMapped] in EF and computed with value returned ApplicationUser/Friend based on ActionUserId. Though these are design choices. There is no right or wrong way. Whichever makes most sense and helps you most in the way you are consuming it should be used.