Cannot INSERT NULL into Column on Remove

Question

I'm executing a Remove() using Entity Framework. When I try to run SaveChanges(), I'm told that I can't insert NULL into a column that doesn't allow it. This is strange to me, since I'm not doing any INSERT, and I checked each of the 30 existing entries to find that it shouldn't be trying to save the table with null in that column.

Here is the code in question:

var user = db.AspNetUsers.FirstOrDefault(u => u.Id == userId);
if (user != null)
{
    var itemsToRemove = user.ItemXrefs.Where(i => !itemIDs.Contains(i.ItemID)).ToList();

    foreach (var xref in itemsToRemove)
    {
        user.ItemXrefs.Remove(xref);
    }

    db.SaveChanges();
    //...
}

Answer 1

Try this instead:

itemXrefSet = db.Set<ItemXref>();
foreach (var xref in itemsToRemove)
{
    itemXrefSet.Remove(xref);
}
db.SaveChanges();

This should delete the cross-referencing entity from the gerund table as well as the relationship between the two entities it links together.

The reason you ran into an error the way you tried to do it was because EntityFramework thought you just wanted to remove the relationship without removing the related entity. When EF does this it tries to set the foreign key column in the dependent table to NULL. The way around this is to either relate the table row with another user by changing the UserId column value, or deleting the table row, since you can't set a required column value to NULL.