Cascade on deletion does not cascade with EF

Question

I have a simple sqlite database with two tables. When I manually delete (using SQLite Expert) the record in the DataSets table, the corresponding record in OneD is deleted as expected. When I delete a record in DataSets from the Entity Framework, this does not delete the control record in One D. Failure is not generated.

Any idea why?

Hello

Here is the database definition:

CREATE TABLE [DataSets] ( [DataSetId] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY AUTOINCREMENT, [Description] TEXT(128)); CREATE TABLE [OneD] ( [OneDId] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, [DataSetId] INTEGER NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT ABORT REFERENCES [DataSets]([DataSetId]) ON DELETE CASCADE, [StockSheetLength] INTEGER NOT NULL ON CONFLICT FAIL); 

This is how I delete an entry from EF

  var dataSets = from ds in context.DataSets select ds; foreach (var ds in dataSets) context.DataSets.DeleteObject(ds); context.SaveChanges(); return true; 

Answer 1

From the SQLite documentation: http://www.sqlite.org/foreignkeys.html

Foreign key restrictions are disabled by default (for backward compatibility), so they must be enabled separately for each database connection separately.

Could this be your problem? I don't know if your Entity Framework will enable it by default:

  sqlite> PRAGMA foreign_keys = ON; 

Edit: Looking a little further, I came across this: http://nitoprograms.blogspot.com/2010_06_01_archive.html

The Entity Framework is actually an ADO.NET data provider, which itself is a wrapper for the ADO.NET data provider (specifically SQLite). Typically, the Entity Framework opens a database connection when necessary; these automatically open connections are automatically closed when the Entity Framework terminates with it. This default behavior works well with SQL Server because of its ADO.NET provider connection pool. However, this does not work well with SQLite due to various "properties" existing on the SQLite connection itself. One example is PRAGMA foreign_keys = ON, which provides foreign keys only for connecting to an SQLite database. If the Framework object opens and closes its connections as it sees fit, then SQLite Such PRAGMAs are lost.

Answer 2

The problem can be solved by including foreign keys in the connection string:

 data source=mydb.db;foreign keys=true 

Answer 3

here is my solution to this problem:

 db.Connection.StateChange += ConnectionStateChange; void ConnectionStateChange(object sender, System.Data.StateChangeEventArgs e) { if (e.CurrentState == System.Data.ConnectionState.Open) db.ExecuteStoreCommand("PRAGMA foreign_keys = true;"); }