EF - Error updating Many to Many relationship for a specific boundary case

Question

I get the error message 'The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable.' when using Entity Framework 6.1. What I don't understand is why I only get it for one specific record.

Three tables are involved: User, Role, UserRoles. UserRoles has FK to User and to Role (linking table).

EF (DB to Entities) constructed two entities: User and Role. User entity has a collection property Roles and Role entity has a collection property Users. I am trying to update a user with existing roles being added/removed from the user's Roles property.

Lazy Loading Enabled is defaulted to false. Working in a disconnected fashion.

userWithUpdatedData is sent as a parameter into this code:

using (entities = new Entities())
{
    try
    {
       var userQuery = entities.Users.AsQueryable();

       userQuery = userQuery.Include(u => u.Roles);

       User userInDB = (from u in userQuery
                      where u.id == userBDO.id
                      select u).FirstOrDefault();

       if (userInDB == null) return false;

       entities.Users.Remove(userInDB); // rowversion requirement

       entities.Users.Attach(userInDB);
       entities.Entry(userInDB).State = System.Data.Entity.EntityState.Modified;                    

       userInDB.Roles.Clear();  // remove all current roles

       // paint with new Roles
       List<int> roleIds = new List<int>();

       if (userWithUpdatedData.Roles != null)
       {
           foreach (Role r in userWithUpdatedData.Roles)
           {
                roleIds.Add(r.id);
           }
       }

       var roles = (from r in entities.Roles
                    where roleIds.Contains(r.id)
                    select r);

       foreach (Role role in roles)
       {
           userInDB.Roles.Add(role);
           entities.Entry(role).State = EntityState.Unchanged;
       }

       entities.SaveChanges();
   }
   catch(Exception e) {}
}

For all other users in the system this code works fine but for a single user with id = 1 (and with most activity in the system) this code fails. When I remove the bit of code that updates the Roles user with id 1 is updated correctly (without the roles of course).

What is wrong with this code and/or why would it fail for a single entity only and work fine for all the rest?

UPDATE So this happens when the user is trying to update self and user is author of one or more Roles (Roles has a FK link to User Created/Updated).

J. Reynolds J. Reynolds · Accepted Answer · 2016-07-29T08:01:13

At this line:

entities.Users.Remove(userInDB); // rowversion requirement

all Roles are marked as deleted which causes the problem.

I needed to move this line:

userInDB.Roles.Clear();

above that.

EF - Error updating Many to Many relationship for a specific boundary case

1 Answers