Pragma Settings for SQLite

[shlatchz]

The following code

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder() { DataSource = Constants.DataDBPath  };
    var connectionString = connectionStringBuilder.ToString();
    var connection = new SQLiteConnection() { ConnectionString = connectionString };
    optionsBuilder.UseSqlite(connection);
}

cannot receive pragma settings such as journalmode or syncmode.
How can I apply the pragma settings to the db?

I tried:

using (var context = new DataDBContext())
{
    var connection = context.Database.GetDbConnection();
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = "PRAGMA synchronous=OFF; PRAGMA journal_mode=OFF; PRAGMA count_changes=OFF; PRAGMA temp_store=OFF; PRAGMA page_size=65536; PRAGMA cache_size=-16777216;";
    command.ExecuteNonQuery();
}

but it didn't help.

[ErikEJ]

You need to do it in the same context as where you are doing the EF data access

[shlatchz]

Can you please elaborate?
Are you saying that I have to do it each time I modify the DB?

[rowanmiller]

@natemcmaster can you confirm?

[rowanmiller]

@natemcmaster ping 😄

[natemcmaster]

@shlatchz if you want to issue custom pragma statements, you need to do this every time you open a connection. Changing values via pragma statements do not persist across calls to sqlite_open_v2. At the moment, we don't have API in Microsoft.Data.Sqlite.SqlConnection to support executing something arbitrary (such as issuing pragma statements) everytime a connection opens.

Moving up a layer in the stack. EntityFramework Core has a class that wraps SqliteConnection. This wrapper handles opening/closing SqliteConnection and has its own logic for issuing pragma statements when connections open. See Microsoft.EntityFrameworkCore.Storage.Internal.SqliteRelationalConnection. EF lets you pass an connection object instead of a connection string if you want to manage the lifecycle of opening/closing a connection yourself. UseSqlite(DbConnection connection).

[natemcmaster]

Remarking for triage: what is the work here? Add API to Microsoft.Data.Sqlite or EF?