.net - Problem with cascade delete using Entity Framework and System.Data.SQLite

Question

I have a SQLite DB that is set up so when I delete a Person the delete is cascaded. This works fine when I manually delete a Person (all records that reference the PersonID are deleted). But when I use Entity Framework to delete the Person I get an error:

System.InvalidOperationException: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

I don't understand why this is occurring. My trigger is set to clean up all related objects before deleting the object it was told to delete.

When I go into the model editor and check the properties of the relationship it shows no action for the OnDelete property. Why isn't this set correctly by pulling it from the DB? If I change this value to Cascade everything works properly, but I would rather not rely on this manual change because what if I refresh my model from the DB and it looses that.

Here's the relivent SQL for my tables.

CREATE TABLE [SomeTable] 
(
    [SomeTableID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    [PersonID] INTEGER NOT NULL REFERENCES [Person](PersonID) ON DELETE CASCADE
)
CREATE TABLE [Person]
(
    [PersonID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
)

Answer 1

I just dealt with this exact issue. It turned out that when I used EF to Update model from database... option, it didn't properly get the "Cascade" rule for on delete.

Try going to your EF database model, click the association that is causing the problem, then make sure that the End1 OnDelete (could be End2, depends on database scheme) is set to Cascade.