How can I read from and write to a many-to-many association in Entity Framework?

Question

This seems like it should be so simple and yet I have been stuck on this for ages unable to find a clear/good solution.

Quite simply I am building a chat program using Entity Framework to store my user data.

I have a class called User. A User has a list of friends (other users) as shown below

public class User
{
    [Key]
    public int ID { get; set; }   

    //whole bunch of other properties like 'username' etc.

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

    public User()
    {
        this.Friends = new List<User>();
    }
}

EF quite happily creates my users table, but I can't for the life of me work out how to write/read the list of users.

One thing I did find whilst looking for answers was this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().
            HasMany(m => m.Friends).
            WithMany()
            .Map(m =>
            {
                m.ToTable("UsersFriends");
                m.MapLeftKey("UserId");
                m.MapRightKey("FriendId");
            });
}

Which has created a nice table for me with the ID of the user and the ID of the user's friend, but no idea how to get the list to this table. I'm guessing its because the New List() doesn't related to the DBContext..

Please show code of how you are attempting to load the list. It sounds to me like you don't have eager loading enabled, and/or are not including the user list. — Brendan Green, Nov 13 '14 at 12:20
possible duplicate of [Mapping association tables in EF 4.1 code first](http://stackoverflow.com/questions/7965972/mapping-association-tables-in-ef-4-1-code-first) — Jodrell, Nov 13 '14 at 12:26
For another reason I had to disable lazy loading using this: this.Configuration.LazyLoadingEnabled = false; I load a user like this: using (Context DB = new Context()) { return DB.Users.Single(User => User.Username == Username); } I haven't worked out how to load the list yet, or write it, this is where I am lost. Sorry if this is a stupid question. I am new to EF. — Benjamin Watkins, Nov 13 '14 at 12:26

Kristof Claes · Answer 1 · 2014-11-14T10:44:52.677

You should make your list of friends virtual:

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

I'm not quite sure if using an ICollection<T> instead of a List<T> is required or not, but it's what I see in most EF examples and what I have been using for years myself.

To add friends, you can do this:

var john = new User { Username = "John" };
var bob = new User { Username = "Bob" };

john.Friends.Add(bob);

context.Users.Add(john);
context.Users.Add(bob);

context.SaveChanges();

To get a list of friends for a user, you can do this:

// Eager load the friends
var userWithFriends = context.Users.Include(x => x.Friends).SingleOrDefault(x => x.Username == "john");

// Lazy loading
var user = context.Users.SingleOrDefault(x => x.Username == "John");
var friends = user.Friends.ToList();

Update

Adding a new friend to an existing user

var dave = new User { Username = "Dave" };
var john = context.Users.Single(x => x.Username == "John");

john.Friends.Add(dave);

context.SaveChanges();

Adding an existing user as a friend to an existing user

var john = context.Users.Single(x => x.Username == "John");
var walter = context.Users.Single(x => x.Username == "Walter");

john.Friends.Add(walter);

context.SaveChanges();

// OR

var bob = new User { UserId = 2 };
var walter = new User { UserId = 4 };

context.Users.Attach(bob);
context.Users.Attach(walter);

bob.Friends.Add(walter);

context.SaveChanges();

Removing a friend

var john = context.Users.Single(x => x.Username == "John");
var bob = context.Users.Single(x => x.Username == "Bob");

john.Friends.Remove(bob);

context.SaveChanges();

// OR

var john = context.Users.Single(x => x.Username == "John");
var walter = new User { UserId = 4 };

context.Users.Attach(walter);

john.Friends.Remove(walter);

context.SaveChanges();

It's required , EF doesn't support anything more than ICollection — tchrikch, Nov 13 '14 at 12:38
Thanks for your response! That seems to work during run time but it doesn't save it for some reason. Here is my code to update the user: public static void UpdateUser(User user) { using (Context DB = new Context()) { DB.Entry(user).State = EntityState.Modified; DB.SaveChanges(); } } This code works for other updates like change of username ect. — Benjamin Watkins, Nov 13 '14 at 13:14
I believe that trick doesn't work for association properties. I've never used that approach before, so I don't really know how to fix that one. — Kristof Claes, Nov 13 '14 at 14:42
OK is there another approach I should use? How would one save a list of Icollection? Or a list of any kind? — Benjamin Watkins, Nov 13 '14 at 16:04
Cool thanks! Will this work if the user entity, and the users in any given users 'friends' list are offline? Currently I am using GraphDiff to update them. — Benjamin Watkins, Nov 14 '14 at 10:51
I have no idea. In my second "Adding an existing user as a friend to an existing user" example, both users are "offline" (if that is what you mean when you say offline) and that part works. But you'll have to test it to be sure. — Kristof Claes, Nov 14 '14 at 10:56

Benjamin Watkins · Accepted Answer · 2014-11-14T10:54:01.480

With thanks to Kristof and others, I have managed to solve my issue and achieved the ability to create/save/update list<t> to my database. Here is what I did:

User Class:

public class User
{
    [Key]
    public int ID { get; set; }   

    //whole bunch of other properties like 'username' etc.

    //I have switched back to List<T> after using IEnumerable because despite what I keep reading
    //you can use them, and I ran into problems using .ToList() where the results were empty.
    public List<User> Friends { get; set; }

    public User()
    {
        //Removed this line because the instance is created when we get the user using DbContext 
        //this.Friends = new List<User>();
    }
}

Creating the relationship table:

This creates the relationship table which stores the contents of the 'Friends' lists.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<User>().
         HasMany(m => m.Friends).
         WithMany()
         .Map(m =>
         {
             m.ToTable("UsersFriends");
             m.MapLeftKey("UserId");
             m.MapRightKey("FriendId");
         });
}

Getting the List from the DB

public static User GetUser(string Username)
{
    using (Context DB = new Context())
    {               
        //This creates the instance of List<User> and gets the data (again, thanks Kristof!)
        return DB.Users.Include(x => x.Friends).SingleOrDefault(x => x.Username == Username);                   
    }
}

Saving/Updating the List to the DB

Previous versions of code using DB.Entry(user).State = EntityState.Modified; DB.SaveChanges(); would update the users properties just fine but the 'friends' list were being ignored, and the relationship table was not updating, OR the relationship table was updated but I ended up with duplicate entries in my users table. This was due to the fact that the user entity was offline when trying to update it.

The workaround for this was to use the GraphDiff package which add's extension methods to EF for updating offline entities. The the code was as follows:

public static void UpdateUser(User user)
{
    using (Context DB = new Context())
    {
        DB.UpdateGraph(user, map => map.AssociatedCollection(u => u.Friends));                
        DB.SaveChanges();
    }
}

There is probably a more sophisticated answer out there but I am new to EF and this is working for me.

How can I read from and write to a many-to-many association in Entity Framework?

2 Answers2

Update

Adding a new friend to an existing user

Adding an existing user as a friend to an existing user

Removing a friend