enabling SQLite foreignkeys in nhibernate

Question

I've learned that the only way to enable FK constraints in sqlite is to turn on this pragma:

PRAGMA foreign_keys = ON;

But unfortunately, if I understood correctly, I will have to execute that query in the beginning of every session. Am I correct? How can I do it "automatically" with NHibernate? Is there a way to hook into NH and execute that right after opening session? I'm using NH 3.0.

score 7 · Accepted Answer · edited May 23 '17 at 12:00

Ok I've found the answer that works for me. Credits goes to James Kovacs (similar question)

I've created my own Driver based on SQLite20Driver and overloaded CreateConnection method (code is from James' GitHub)

public override IDbConnection CreateConnection()
    {
        DbConnection connection = (DbConnection)base.CreateConnection();
        connection.StateChange += Connection_StateChange;
        return connection;
    }

    private static void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if ((e.OriginalState == ConnectionState.Broken || e.OriginalState == ConnectionState.Closed || e.OriginalState == ConnectionState.Connecting) &&
            e.CurrentState == ConnectionState.Open)
        {
            DbConnection connection = (DbConnection)sender;
            using (DbCommand command = connection.CreateCommand())
            {
                // Activated foreign keys if supported by SQLite.  Unknown pragmas are ignored.
                command.CommandText = "PRAGMA foreign_keys = ON";
                command.ExecuteNonQuery();
            }
        }
    }

Works like a charm :).

Yes I should, and I will in two days when stackoverflow will allow me :). — ppiotrowicz, May 02 '11 at 12:50

enabling SQLite foreignkeys in nhibernate

1 Answers1