SQLite foreign key not activated

Question

I use Entity Frame work, and my context creation method is my creation context method, I included a foreign key whenever this is called, but for my surprise, the value variable is always 0

private static DataBaseEntity CreateContext() { var context = new DataBaseEntity("name=DataBaseEntity"); context.ExecuteStoreCommand("PRAGMA foreign_keys = ON"); var value = context.ExecuteStoreCommand("PRAGMA foreign_keys"); return context; } 

I want to have a cascade for the delete function, but I cannot get it because the foreign key constraint does not get set

I am using SQLite 3.6.23.1 with the ado.net provider

Answer 1

Enabling foreign keys only works for the entire life of the database connection. By default, the ObjectContext will open the connection and close it whenever you interact with the database if you called SaveChanges() , ExecuteStoreCommand() or otherwise. If you do not want this behavior, you need to manually open the connection and ideally close it when you are done with the ObjectContext .

I prefer to include foreign keys in the connection string, so I do not need to worry about one of the above.

For example, in your app.config file you will have something like this (look for foreign keys=True near the end of the connectionString attribute):

 <configuration> <connectionStrings> <add name="DataBaseEntity" connectionString="metadata=res://*/m.csdl|res://*/m.ssdl|res://*/m.msl;provider=System.Data.SQLite;provider connection string=&quot;data source=C:\foo.db;foreign keys=True&quot;" providerName="System.Data.EntityClient" /> </connectionStrings> </configuration> 

I think this should work with your ADO.Net provider, but I cannot do promises. If you upgrade to the latest version available on the SQLite website , you will increase your chances that it will work properly.