CODEWITHSUNDEEP
c#.netentity-framework-4sqlitesystem.data.sqlite
Raghunandan Ghagarvale
Raghunandan Ghagarvale

Reputation: 147

SQLite Foreign key Is Not getting Activated

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

Upvotes: 4

Views: 1949

Answers (1)

user610650
user610650

Reputation:

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.

Upvotes: 9

Related Questions