How to use ON DELETE CASCADE with SQLite and Entity Framework 6

Question

I can't get ON DELETE CASCADE work with SQLite and Entity Framework 6 (EF6). Is there anything I'm missing?

I created a Windows Forms application in C# with Visual Studio 2015. EF6 and SQLite were well working together in my application, I could insert and retrieve data. But when it comes to delete stuff, deleting one row/entity works fine but the cascade doesn't work at all. With DB Browser for SQLite, I deleted the exact same row, and the cascade worked.

This is my table which represents nodes:

CREATE TABLE `Nodes` (
    `NodeId`    INTEGER,
    `ParentId`  INTEGER,
    `Label` TEXT,
    PRIMARY KEY(`NodeId`),
    FOREIGN KEY(`ParentId`) REFERENCES `Nodes`(`NodeId`) ON DELETE CASCADE
);

And this is how I'm deleting the "root" node (supposed to delete every child nodes with cascade):

var noParentItems = entities.Nodes.Where(n => n.ParentId == null);
foreach (var root in noParentItems)
{
     //entities.Entry(root).State = EntityState.Deleted;
     entities.Nodes.Remove(root);
}
entities.SaveChanges();

Notes: I also tried the commented version to delete "root". Here you see that I am looping to catch every root nodes, but this is not important.

I am using the database-first approach for EF6. Please note that I don't want to modify any generated file to fix this.

Also, these solutions didn't work for me:

Entity Framework On Delete Cascade when entity is loaded : nothing changed
Put "cascade" in the designer ( How to delete a row with data with its parent row in another table ) : nothing changed
Use "entities.Nodes.Attach(root);" as suggested in one of the answer below didn't work either

Please feel free to ask any additional information you may need.

Thanks.

Well, I don't want to modify the generated files, and OnModelCreating() is overriden in a generated file. Is there a workaround? — Sheep, Nov 29 '17 at 14:20

Sheep · Accepted Answer · 2017-11-30T09:36:43.473

Thank to comments, I found out how to solve this issue.

First I looked at this answer: Cascade on delete not cascading with EF

The important part is this quote:

The Entity Framework is actually an ADO.NET data provider that is itself wrapping an ADO.NET data provider (SQLite, to be specific). Normally, the Entity Framework will open a database connection whenever it needs one; these automatically-opened connections are automatically closed when the Entity Framework is finished with it. This default behavior works well with SQL Server due to its ADO.NET provider's connection pooling. However, it does not work well with SQLite, due to various "properties" existing on the SQLite connection itself. One example is "PRAGMA foreign_keys = ON", which enforces foreign keys only for that SQLite database connection. If the Entity Framework opens and closes its connections at will, then SQLite PRAGMAs such as these are lost.

So, if there is a place to put instructions, it would be only in the connection string.

Consequently I added this:

foreign keys=True

Now it works fine !

score 0 · Answer 2 · answered Nov 29 '17 at 14:27

0

You are not using AsNoTracking in your query, I know but even so you can try like this;

foreach (var root in noParentItems)
{
     entities.Nodes.Attach(root);
     entities.Entry(root).State = EntityState.Deleted;
}

or like this;

foreach (var root in noParentItems)
{
     var newRoot = new Node{ NodeId = root.NodeId };
     entities.Nodes.Attach(newRoot);
     entities.Entry(newRoot).State = EntityState.Deleted;
}

answered Nov 29 '17 at 14:27

lucky

12,734
4
24
46

Thanks for your answer. The first version didn't work. The version with var newRoot = new Nodes { NodeId = root.NodeId }; raised an exception probably because it creates an element with the same Primary Key. Here is the message: "An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key." – Sheep Nov 29 '17 at 14:36
Ohh my mistake. For second example would you add AsNoTracking() in your query and try again ? – lucky Nov 29 '17 at 14:38
To be honest I didn't hear about AsNoTracking() before. How should I use it? – Sheep Nov 29 '17 at 14:41
Like this var noParentItems = entities.Nodes.AsNoTracking().Where(n => n.ParentId == null); – lucky Nov 29 '17 at 14:44
I wrote: `var noParentItems = entities.Nodes.AsNoTracking().Where(n => n.ParentId == null); foreach (var root in noParentItems) { var newRoot = new Nodes { NodeId = root.NodeId }; entities.Nodes.Attach(newRoot); entities.Entry(newRoot).State = EntityState.Deleted; }` but it didn't work (a single row was deleted). – Sheep Nov 29 '17 at 14:47
I am sorry my friend. Unfortunately I don't have another suggestion :( but, I have found an article here. Did you read this; https://stackoverflow.com/questions/7674318/cascade-on-delete-not-cascading-with-ef – lucky Nov 29 '17 at 17:45

How to use ON DELETE CASCADE with SQLite and Entity Framework 6

2 Answers2