Unable to INSERT NULL to column when deleting

Question

I am performing Remove() using the Entity Framework. When I try to run SaveChanges() , they tell me that I cannot insert NULL into a column that does not allow it. This is strange for me, since I am not doing any INSERT , and I checked each of the 30 existing records to find that it should not try to save a table with a null value in this 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 remove the cross-reference object from the gerund table, as well as the relationship between the two related objects.

The reason you ran into the error, as you tried to make it, was because EntityFramework thought you just needed to delete the relationships without deleting the related object. 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 link the table row to another user by changing the value of the UserId column, or by deleting the table row, since you cannot set the required column value to NULL.