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

.net c# entity-framework-6 sqlite

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:

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

Thanks.

1
0
11/30/2017 9:13:08 AM

Accepted Answer

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 !

0
11/30/2017 9:36:43 AM

Popular Answer

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;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow