SQLite Foreign key Is Not getting Activated

.net, c#, entity-framework-4, sqlite, system.data.sqlite

Solution

Turning foreign keys on only work during the lifetime of the database connection. By default, `ObjectContext` will open a connection and close it back whenever you interact with the database would it be by invoking `SaveChanges()`, `ExecuteStoreCommand()`, or otherwise. If you do not want this behavior, then you have to manually open the connection, and ideally close it back when you are done with the `ObjectContext` object.

Myself I prefer to turn on foreign keys in the connection string, so that I don't need to worry about any of the aforementioned.

For example, in your app.config file you would have something like this (look for `foreign keys=True` towards 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 can't make promises. If you upgrade to the latest version available on SQLite's website, you increase your odds that it'll work properly.

Problem

i am using Entity Frame work and the fallowing is my create context method, i have enabled 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 cascade on delete functionality, but i am not able to get it as the foreign key constraint is not getting set i am using SQLite 3.6.23.1 with the ado.net provider

Original source