EF Code First - Multiplicity constraint violated. The role '...' of the relationship '...' has multiplicity 1 or 0...1

Question

I'm trying to develop a project that includes "online friends" feature using EF Code First. I have to store all the users' friends in the database. I have "User" POCO class for the users and "FriendRelationship" POCO class for holding the friends of a user. These POCO classes are:

[Table("UserTable")]
public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    //other properties

    public int? FriendListID {get; set;}

    public virtual FriendRelationship FriendList { get; set; }
}

[Table("FriendRelationshipTable")]
public class FriendRelationship
{
    public FriendRelationship()
    {
        Friends = new List<User>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public virtual List<User> Friends { get; set; }
}

And my Fluent API is:

modelBuilder.Entity<User>()
            .HasOptional(o => o.FriendList)
            .WithMany(m => m.Friends)
            .HasForeignKey(fk => fk.FriendListID)
            .WillCascadeOnDelete(false);

I'm creating the Friend object in the User object at User object's first creation time(this User object added to database at controller.):

public class CreateAccountModelBinder : IModelBinder
{
    public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        HttpRequestBase request = controllerContext.HttpContext.Request;
        //other actions

        User _user = new User();
        _user.FriendList = new FriendRelationship();

       //other actions and return
    }
}

And lastly, my friend addition attempt:

static void AddFriendByManually()
    {
        using (var context = _liveCampusContext)
        {
            User Admin2 = context.Users.SingleOrDefault(w => w.Username == "Admin2");
            User Admin = context.Users.SingleOrDefault(w => w.Username == "Admin");
            User AdminVader= context.Users.SingleOrDefault(w => w.Username == "AdminVader");

            Admin.FriendList.Friends.Add(Admin2);
            Admin.FriendList.Friends.Add(AdminVader);

            Admin2.FriendList.Friends.Add(Admin);
            Admin2.FriendList.Friends.Add(AdminVader);

            AdminVader.FriendList.Friends.Add(Admin2);
            AdminVader.FriendList.Friends.Add(Admin);

            context.SaveChanges();
        }
    }

When context.SaveChanges() line executes, an exception is thrown:

Multiplicity constraint violated. The role 'User_FriendList_Target' of the relationship 'TeachLearnWeb.Data.DbContextFolder.User_FriendList' has multiplicity 1 or 0..1.

What am I missing? Thanks.

Answer 1

As per the example given in your code:

Admin.FriendList.Friends.Add(Admin2);
Admin.FriendList.Friends.Add(AdminVader);

*In the above code you are adding Admin2 and AdminVader.

Admin2.FriendList.Friends.Add(Admin);
Admin2.FriendList.Friends.Add(AdminVader);

* In the above 2 lines you are adding Admin and AdminVader. So AdminVader is being added twice which leads to Multiplicity constraint.

You might need to make changes to your table. IN the UserTable, you can store all users.

[Table("UserTable")]
public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name {get; set;}
}

In the FriendRelationshipTable, store the userID (user who will have set of friends) and FriendsUserId(this will store the id of the friend the user has) [Table("FriendRelationshipTable")] public class FriendRelationship {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public ID
    public int FriendUserID { get; set; }
    public int UserID {get; set;}

}

I have not added other properties. Check if this table configuration will work.

Answer 2

Judging by the usage example you have shown, this relationship should actually be many to many:

• Any User can have many friends.
• The same friend can show up for many users.

Also, the friend relationship is bidirectional (I strongly suspect).

So after Admin.FriendList.Friends.Add(AdminVader); the FriendList of AdminVader will already contain Admin and we can skip the line AdminVader.FriendList.Friends.Add(Admin);.

You only need the FriendRelationship class if you want to store additional data on the relationship itself (e.g. FriendSince timestamp). Else change your model to

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    //other properties

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

And change the config of the relationship as follows:

 ModelBuilder.Entity<User>()
     .HasMany(u => u.Friends)
     .WithMany()
     .Map(m => m.ToTable("FriendRelationshipTable"));