Clear an EntityCollection and then Add to it within the same transaction doesn't seem possible

Question

Hi

I have 2 entities a Department and an Employee. 1 Department can have many Employees. I would like to clear all the Employees from an existing Department, and also add a new Employee to that same department and then save the changes. It must be within a single transaction.

However when I try execute the code below I get a key violation error on the database. It seems that the clear is not deleting the items in the DepartmentEmployee table, and then inserting the new Employee.

Employee newEmployee = GetNewEmployee();
department.Employees.Clear();
department.Employees.Add(newEmployee);
EntityContext.ApplyPropertyChanges("SetName", department);
EntityContext.SaveChanges();

Any help with this would be greatly appreciated. Thanks.

Answer 1

I don't think you can do this in one call to SaveChanges. The Entity Framework does not guarantee any specific order of operations. So I don't think there is any way to force the DELETE to come before the INSERT without an additional call to SaveChanges.

On the other hand, you probably can do it in one database transaction. Just do it inside a new TransactionScope.

Try:

using (var t = new TransactionScope())
{
    Employee newEmployee = GetNewEmployee();
    department.Employees.Clear();
    EntityContext.SaveChanges();
    department.Employees.Add(newEmployee);
    EntityContext.ApplyPropertyChanges("SetName", department);
    EntityContext.SaveChanges();
    t.Complete();
}