Many-to-many relationship between the same class

Question

I'm trying to implement what seems like a pretty common case - a Friendship relationship between 2 users. I think the models are self-explanatory. Friendship needs to have 2 users on it and also some data about the relationship, and users have a Friendships property, which is populated by any friendship they are a part of, as either User1 or User2. I couldn't figure out a nicer way to name the 2 users. Here are my models:

public class Friendship : Entity
{
    public ApplicationUser User1 { get; set; }
    public ApplicationUser User2 { get; set; }
    ...
}

public class ApplicationUser : IdentityUser
{
    public virtual List<Friendship> Friendships { get; set; }
    ...
}

Here's my attempt at configuring the relationship in OnModelCreating:

modelBuilder.Entity<ApplicationUser>()
    .HasMany(x => x.Friendships)
    .WithRequired()
    .Map(t => t.MapKey("User1_Id", "User2_Id"));

I don't think I'm doing the configuration right. Here's the error I'm getting when trying to create a migration from this:

The specified association foreign key columns 'User1_Id, User2_Id' are invalid. The number of columns specified must match the number of primary key columns.

Is it possible to accomplish this using ef6? A special thanks to anyone that can help.

Without testing it, I would think that the "WithRequired(...)" would not work, since it is not satisfied when not associating two users to each other. In your case it's one-way: only one is the friend of the other, the other one is only "befriended" (not sure if that words exists). — Peter Branforn, Jan 29 '15 at 16:23
I'm not sure what you mean. Do you mean the addition of this line `requestedUser.IncomingFriendRequests.Add(friendRequest);`? I had that in there before and took it out, but it didn't make a difference. — bcstrawn, Jan 29 '15 at 19:32
The many to many relationships I am used to do not use the "WithRequired()", instead it uses a direct declaration of a mapping table without the "WithRequired()": Sample (this will look funny in a comment block): modelBuilder.Entity() .HasMany(x => x.IncomingFriendRequests) .WithMany(x => x. OutgoingFriendRequests) .Map(x => { x.ToTable("FriendRequest"); x.MapLeftKey("RequestingUserId"); x.MapRightKey("RequestedUserId"); }); — Peter Branforn, Jan 30 '15 at 09:03
Possible duplicate [EntityFramework Same Table Many to Many Relationship](http://stackoverflow.com/a/12331031/580951). — Dustin Kingen, Feb 04 '15 at 19:44
Thanks for the suggestion, @Romoku, but this doesn't quite solve my problem. This implementation says I can't add any other columns to the relationship table, as there is no intermediary class to represent the relationship. It's very close, but I do need other pieces of information about the relationship beyond simply who the 2 users are that are friends. — bcstrawn, Feb 04 '15 at 19:57

score 2 · Accepted Answer · edited May 23 '17 at 11:44

You are running into a multiplicity constraint. The Friendship class has two users which creates a cycle from ApplicationUser -> Friendship -> ApplicationUser. To fix this remove the User1 and User2 property and add a collection ICollection<ApplicationUser> Users.

DTO:

public class ApplicationContext : DbContext
{
    public ApplicationContext()
        : base("ApplicationContext")
    {
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Relationship> Relationships { get; set; }
}

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

public class User : Entity
{
    public string Name { get; set; }
    public virtual ICollection<Relationship> Relationships { get; set; }
}

public class Relationship : Entity
{
    public virtual ICollection<User> Users { get; set; }
}

Sample:

var bob = new User
{
    Name = "Bob",
    Relationships = new List<Relationship>()
};

var fred = new User
{
    Name = "Fred",
    Relationships = new List<Relationship>()
};

var relationship = new Relationship
{
    Users = new List<User>
    {
        bob,
        fred
    }
};

bob.Relationships.Add(relationship);
fred.Relationships.Add(relationship);

using(var context = new ApplicationContext())
{
    context.Users.Add(bob);
    context.Users.Add(fred);
    context.Relationships.Add(relationship);

    context.SaveChanges();
}

Many-to-many relationship between the same class

1 Answers1