Unable to Set Foreign Key to Null

Question

I have an optional foreign key that I'm attempting to set to null. No matter what I've tried, on SaveChanges(), the update statement sets the foreign key to the previous value instead of null.

Simplified Child Class:

public class Child
{
    [Key, Column(Order = 0), ScaffoldColumn(false)]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("Parent")]
    public int? ParentId { get; set; }

    public virtual Parent Parent { get; set; }
}

Simplified Parent Class:

public class Parent
{
    [Key, Column(Order = 0), ScaffoldColumn(false)]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

Things I've tried:

1. Load the Child object and set the ParentId null and set the Parent to null
2. Load the Child object and set the ParentId null and force the entity state to be modified
3. Load the Child object including the Parent object, then setting the values to null and forcing the entity state to be modified
4. Load the Parent object, then the Child object and .Remove(child) from the Parent object
5. Load the Parent object, then the Child object and .Remove(child) from the Parent and set the Child.ParentId to null and the Child.Parent to null.

Currently I have:

public void RemoveChildFromParent(int childId, int parentId)
{
    Parent parent = _context.Parents.Include(x => x.Children).FirstOrDefault(u => u.Id == parentId);
    Child child = parent.Children.SingleOrDefault(u => u.Id == childId);
    parent.Children.Remove(child);
    child.ParentId = null;
    child.Parent = null;
    child.StateOfEntity = StateOfEntity.Modified;

    _context.ApplyStateChanges();
    _context.SaveChanges();
}

On Save Changes, the SQL Update Statement still sets the ParentId on the Child object to the old value and I get this error:

System.InvalidOperationException was unhandled by user code
  HResult=-2146233079
  Message=The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       at System.Data.Entity.Internal.InternalContext.SaveChanges()
       at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
       at System.Data.Entity.DbContext.SaveChanges()
       at Insight.DataLayer.InsightContext.SaveChanges()
       at Insight.DataLayer.ChildRepository.RemoveChildFromParent(Int32 childId, Int32 parentId)
       at Insight.BusinessLayer.ParentManager.RemoveChild(Int32 id, Int32 parentId)
       at Insight.PresentationLayer.Controllers.ParentController.RemoveChild(Int32 id, Int32 parentId)
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
  InnerException: 

Also, not sure if it matters, but I have LazyLoadingEnabled = false and AutoDetectChangesEnabled = false.

Answer 1

I'm not sure if there is an "elegant" solution to this (maybe changing the table structure?), but instead of spending more time on this small issue, I've decided to use DbContext.Database.ExecuteSqlCommand() and manually write the update statement.

It definitely feels like a work around in the Entity Framework methodology, but it's limited to this scenario, takes little time to do, and works as intended.

Answer 2

Also, not sure if it matters, but I have ... AutoDetectChangesEnabled = false.

Yes, it matters. Did you disable auto change detection by default (in your context constructor for example)? That is dangerous because you have to know and understand when you need to call change detection manually if you disable the automatic detection - which is not a trivial thing. Usually AutoDetectChangesEnabled = false should only be set if you are sure that it doesn't cause unexpected results and when you really need it (normally for performance reasons when running bulk operations with many entity updates, inserts or deletes). I would definitely leave in at true by default.

I don't know what _context.ApplyStateChanges exactly does (seems to be a custom method) but all other lines of your code after the query don't call any EF methods until SaveChanges (and the 5 described procedures neither) which is exactly one of the situations (as described in the linked blog post above) where disabling automatic change detection does not work without further care.

To fix the problem, you could try to call _context.DetectChanges(); in your code snippet before SaveChanges (or maybe before ApplyStateChanges). However, the procedure to load the parent with all children is far to expensive and the simplest solution would be just loading the child, set the FK to null and save the changes - all this with enabled automatic change detection:

using (var context = new MyContext())
{
    // as said, the following line should be your default
    context.Configuration.AutoDetectChangesEnabled = true;

    var child = context.Children.SingleOrDefault(c => c.Id == childId);
    if (child != null)
    {
        child.ParentId = null;
        context.SaveChanges();
    }
}