级联删除不与EF级联

Question

我有一个简单的sqlite数据库与两个表。当我手动删除（使用SQLite Expert）表DataSets中的条目时，OneD中的相应条目将按预期删除。当我从实体框架中删除数据集中的条目时，它不会导致一个D中的条形码条目被删除。没有产生错误。

任何想法为什么？

问候

这里是数据库定义：

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

这是我如何删除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

项从SQLite的文档：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.

这可能是你的问题吗？我不知道，如果你的实体框架与打开它默认为：

sqlite> PRAGMA foreign_keys = ON; 

编辑：看远一点我碰到这个偶然：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.

Answer 2

这里是我的解决这一问题：

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

Answer 3

的问题可以通过在连接字符串中启用外键来解决：

data source=mydb.db;foreign keys=true