Entity Framework many-to-many relationship values not saved in database

Question

I've set up a many-to-many relationship using Code First and the Entity Framework 5. The join table has been created but the field data does not save to the database.

The workers are being added to the Project object successfully and the db.SaveChanges() method executes without error but when the GET request for the /Project/Edit/x action is called again the Workers list property is empty.

How do I make this work so the connected Worker data is persisted and retrieved along with the Project object in the GET request after it has been 'saved' via the POST?

Controller: ProjectController.vb

POST: /Project/Create/x

    For i As Integer = LBound(strWorkerIds) To UBound(strWorkerIds)
        Dim workerId As Integer
        If Integer.TryParse(Trim(strWorkerIds(i)), workerId) Then
            Dim worker As Worker = db.Workers.Find(workerId)
            If Not worker Is Nothing Then
                project.Workers.Add(worker)
            End If
        End If
    Next

    If ModelState.IsValid Then
        db.Projects.Add(project)
        db.SaveChanges() ' The project is saved along with the correct project.Workers list
        Return RedirectToAction("Index", "Home")
    End If

POST: /Project/Edit/x

    For i As Integer = LBound(strWorkerIds) To UBound(strWorkerIds)
        Dim workerId As Integer
        If Integer.TryParse(Trim(strWorkerIds(i)), workerId) Then
            Dim worker As Worker = db.Workers.Find(workerId)
            If Not worker Is Nothing Then
                project.Workers.Add(worker)
            End If
        End If
    Next

    If ModelState.IsValid Then
        db.Entry(project).State = EntityState.Modified
        db.SaveChanges() 'The changes to project.Workers list are NOT being saved?
        Return RedirectToAction("Index", "Home")
    End If

Model: Project.vb

Public Class Project

    Public Sub New()
        Me.Workers = New List(Of Worker)()
    End Sub

    Public Property ProjectID As Integer
    'other properties...
    Public Overridable Property Workers As ICollection(Of Worker)

End Class

Model: Worker.vb

Public Class Worker

    Public Sub New()
        Me.Projects = New List(Of Project)()
    End Sub

    Public Property WorkerID As Integer
    'other properties...
    Public Overridable Property Projects As ICollection(Of Project)

End Class

DB Context: ProjectLogContext.vb

Imports System.Data.Entity
Imports System.Data.Entity.ModelConfiguration.Conventions

Public Class ProjectLogContext

    Inherits DbContext

    Public Property Projects As DbSet(Of Project)
    Public Property Workers As DbSet(Of Worker)

    Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
        MyBase.OnModelCreating(modelBuilder)

        modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()

        modelBuilder.Entity(Of Worker)().HasMany(Function(w) w.Projects) _
            .WithMany(Function(p) p.Workers) _
            .Map(Function(x) x.MapLeftKey("WorkerID").MapRightKey("ProjectID") _
                 .ToTable("WorkerProject"))

    End Sub

End Class

Answer 1

Setting the state to Modified for the parent entity won't have any effect on relationships in a many-to-many association. To achieve a correct update you must load the current entity graph (parent including its children) from the database and then either add a child if its ID is in your ID list or remove the child if its ID is not in the ID list. EF's change tracking will follow those modifications of the loaded graph and generate the necessary INSERT and DELETE statements to update the link table. It could look like this in your Edit POST action (in C#):

var projectInDb = db.Projects.Include(p => p.Workers)
    .SingleOrDefault(p => p.ProjectID == project.ProjectID);

if (projectInDb != null)
{
    // Convert string list to integer list
    var workerIds = new List<int>();
    foreach (string strWorkerId in strWorkerIds)
    {
        int workerId;
        if (int.TryParse(strWorkerId.Trim(), out workerId))
            workerIds.Add(workerId);
    }

    // Update scalar properties of the project
    db.Entry(projectInDb).CurrentValues.SetValues(project);

    // Remove workers
    foreach (var workerInDb in projectInDb.Workers.ToList())
    {
        if (!workerIds.Contains(workerInDb.WorkerID))
            projectInDb.Workers.Remove(workerInDb);
    }

    // Add workers
    foreach (int workerId in workerIds)
    {
        if (!projectInDb.Workers.Any(w => w.WorkerID == workerId))
        {
            var workerInDb = db.Workers.Find(workerId);
            if (workerInDb != null)
                projectInDb.Workers.Add(workerInDb);
        }
    }

    db.SaveChanges();
}