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
)

Omar · Accepted Answer · 2010-03-21 17:50:09Z

25

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.

edited Mar 21, 2010 at 17:50

answered Mar 21, 2010 at 6:06

Omar

40.3k46 gold badges150 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Garside Over a year ago

Is there a way to work out if you should update End1 or End2? Or is it just trial and error?

Omar Over a year ago

It depends on the way your database is modeled. If you have a one-to-many relationship, the one end should be set to Cascade

Keith G · Accepted Answer · 2010-10-19 16:46:05Z

5

I was having the same problem with SQL Server. When I tried to update mode from database, it didn't pick up the cascade rules. Note that the rules were added after the model was already created. I even tried deleting a table from the model and adding it back in. That had the same effect - no cascade rules.

However, when I created a brand new model with the same exact tables, it picked up the cascade rules. So my solution was just to delete the old model and create a new one with the same name, etc.

I guess is that there is something wrong with the update model from database process.

answered Oct 19, 2010 at 16:46

Keith G

4,5805 gold badges41 silver badges48 bronze badges

3 Comments

Phil Strong Over a year ago

I had this same issue with Sql Server

Andiih Over a year ago

I can also confirm the same issue

Craig Stuntz Over a year ago

The designer will never overwrite existing CSDL. Because you usually want to keep your customizations. In this case, you don't, but that's not the common case.

Craig Stuntz · Accepted Answer · 2010-03-19 14:38:41Z

1

Sounds like a provider bug to me. The cascade should be picked up from the DB. Try it with SQL Server; you'll see it works there. You should report this to whoever wrote your SQLite provider.

answered Mar 19, 2010 at 14:38

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

4 Comments

Andiih Over a year ago

I can verify that its not (just) a SQLite provider bug. Adding cascades is not reflected in the model in SQL Server unless you remove the entire model and recreate. Pain.

Craig Stuntz Over a year ago

@Andiih, only if the entity has been added without them (presumably incorrectly) in the first place. The designer does not overwrite your custom CSDL. That's actually a feature.

Andiih Over a year ago

Yes, I had messed up in the database design. I hadn't customized the CSDL. Is there any way of forcing a refresh without deleting and re-adding the entire model ?

Craig Stuntz Over a year ago

Adding a table is customizing CSDL. You don't need to recreate the entire model; just open in the XML editor, remove the mapping (in CSDL, MSL, & SSDL) for just that one table, and re-add it. Or just set up cascades on the associations manually.

Memo · Accepted Answer · 2013-08-19 01:19:21Z

0

You can use the navigation property only without using the foreign key property. The delete cascade does not solve the problem in code because your person object will not be marked as deleted.

answered Aug 19, 2013 at 1:19

Memo

3293 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related