Cascade on delete not cascading with EF

I have a simple sqlite database with two tables. When I manually delete (using SQLite Expert)an entry in table DataSets, the coresponding entry in OneD is deleted as expected. When I delete an entry in DataSets from Entity Framework it does not cause the coresponsing entry in One D to be deleted. There is no error generated.

Any idea why?

Regards

Here is the database definition:

CREATE开发者_如何学C 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);

Here is how I delete the 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;

---

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

data source=mydb.db;foreign keys=true

---

here's my solution to that 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;");            
}

---

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

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

Could this be your problem? I don't know if your Entity Framework turns it on by default with:

  sqlite> PRAGMA foreign_keys = ON;

Edit: Looking a bit further i stumbled across this: http://nitoprograms.blogspot.com/2010_06_01_archive.html

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.