Using entity framework to detect changes in related table and action appropriate inserts and deletes.

Question

Lets say i have a Person table, a Role table with a trel table PersonRoles linking them as many to many.

I create a new person and assign them to 2 roles (role 1, role 3).

I then want to edit this person; so i retrieve their data and bind their roles to a checkboxes. I change the values (Deselect role 1 and select role 2 instead) I then post this data back through a viewmodel.

Can i then get Entity Framework to update these roles for me, as in delete the entry in PersonRoles to role 1 and then add a new entry as role 2? Or do i have to do the logic for this myself?

Something like ** this ** i was hopeing but doesn't work obviously.

[HttpPost]
    public ActionResult Edit(int id, PersonViewModel model)
    {
        if (ModelState.IsValid)
        {
            var person= GetPerson(id);
            person.Name= model.Name;
            person.Age = model.Age;

           ICollection<Roles> personroles = new Collection<Roles>();
            foreach (string rId in model.Roles)
            {
                personroles.Add(this.RoleRepository.Single(r => r.Id.ToString() == rId));
            }

            **person.Roles = personroles;**

            this.PersonRepository.SaveChanges();
            return RedirectToIndex(personId);
        }

        return View();
    }

Cheers, Kohan

Answer 1

The devil is in the details, and you don't give any, but generally, yes, the EF can do this.

The best approach is to make PersonRoles have only two columns: The FKs to Person and Role, with cascading deletes on both, and make the PK a compound key on both. If you have no other columns, the EF will subsume this table into a many to many relationship which will behave the way you're expecting.

Update: Since you've now added source, let's fix it (untested; this is off the top of my head):

    if (ModelState.IsValid)
    {
        var person= GetPerson(id);
        person.Name= model.Name;
        person.Age = model.Age;

        var rolesToAdd = model.Roles.Where(mr => !person.Roles.Any(pr => pr.Id == mr);
        var rolesToRemove = person.Roles.Where(pr => !model.Roles.Any(mr => pr.Id == mr);

        foreach (string rId in rolesToAdd)
        {
            person.Roles.Add(this.RoleRepository.Single(r => r.Id.ToString() == rId));
        }
        foreach (string rrId in rolesToRemove)
        {
            var remove = person.Roles.Where(r => r.Id == rrId).Single();
            person.Roles.Remove(remove);
        }

        this.PersonRepository.SaveChanges();
        return RedirectToIndex(personId);
    }