Cannot update many-to-many relationships in Entity Framework

Question

I am using Entity Framework 4.3 Code First, and I have problem with updating many-to-many relationships.

I defined the following classes:

public abstract class Entity
{
    [Column(Order = 0)]
    [Key]
    public int Id { get; set; }

    [Timestamp]
    [Column(Order = 1)]
    public byte[] Version { get; set; }
}


public class Video : Entity
{
    public string Title { get; set; }
    public string Description { get; set; }
    public TimeSpan Length { get; set; }

    public virtual ICollection<Coworker> Coworkers { get; set; }
}


public class Coworker : Entity
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<Video> Videos { get; set; }
}

When the database is created, the schema look right: There is a Videos, Coworkers and VideoCoworkers table too, without

I use repository pattern in an N-Tier application to access database, my Insert and Update method looks like this:

public T Insert(T entity)
{
    //Creates database context. When it disposes, it calls context.SaveChanges()
    using (var session = new DatabaseSession())
    {
        session.Context.Set<T>().Add(entity);
    }
}

public T Update(T entity)
{
    //Creates database context. When it disposes, it calls context.SaveChanges()
    using (var session = new DatabaseSession())
    {
        entity = session.Context.Set<T>().Attach(entity);
        session.Context.Entry(entity).State = EntityState.Modified;
    }
    return entity;
}

When I update an entity, I create the entity object from a DTO, that's why use DbSet.Attach instead of selecting it and updating the properties one-by-one.

When I initialize the database, I add some test data:

1. Create 3 Coworkers, where I set first and last name. (A, B, C)
2. Create 3 Videos, where I set title, description and length, and also set some coworkers. First video has A,B, second has B,C and third has A,C.

When I list the Videos from code, I can see that Video.Coworkers collection is filled with good values, and when I query the link table (VideoCoworkers) in SQL Server Management Studio, it also looks good.

My problem is when I update for example the title of the Video, it works. But when I try to delete from Video2 the existing coworkers (B and C), and try to add coworker A, then the relationship is not updated. It also does not work when I only try to add new coworker, or only try to delete one. I create the entity which is used as the parameter of the Update() method by creating a new Video entity with a new collection of Coworkers (which are selected from the database with Find() method by Id).

What is the correct way to update many-to-many relationships?

Answer 1

But when I try to delete from Video2 the existing coworkers (B and C), and try to add coworker A, then the relationship is not updated.

Without using a generic repository the correct procedure would be:

using (var session = new DatabaseSession())
{
    video2 = session.Context.Set<Video>().Include(v => v.Coworkers)
        .Single(v => v.Id == video2Id);

    coworkerA = new Coworker { Id = coworkerAId };
    session.Context.Set<Coworker>().Attach(coworkerA);

    video2.Coworkers.Clear();
    video2.Coworkers.Add(coworkerA)

    session.Context.SaveChanges();
}

The essential part is that you must load or attach the entity in its original state, change the entity, i.e. remove and add children, and then save the changes. EF's change detection will create the necessary INSERT and DELETE statements for the link table entries. The simple procedure to set the state to Modified you are trying in your generic Update method is suited only for updating scalar properties - like changing the video title - but won't work for updating relationships between entities.

Answer 2

For solve this problem:

1. attach the entity to context
2. load the collection(the collection is not loaded, because )
3. change the state of entity to modified
4. save changes

So your code for update should be like this:

public Video Update(Video entity)
{
    //Creates database context. When it disposes, it calls context.SaveChanges()
    using (var session = new DatabaseSession())
    {
        entity = session.Context.Set<Video>().Attach(entity);
        session.Context.Entry(entity).Collection(p => p.Coworkers).Load();
        session.Context.Entry(entity).State = EntityState.Modified;
    }
    return entity;
}