Setting journal mode in SQLite for Entity Framework Core code-first

Question

I'm using DBContext on Entity Framework, using a process like in this tutorial to create the DB.

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Filename=blog.db");
    }
}

and saving using something like:

using (var context = new BloggingContext())
{
    context.Add(blog);
    await context.SaveChangesAsync();
}

How would I go about setting the Journal Mode to something like WAL?

Ary

Answer 1

The Sqlite provider for EF7 support only a small subset off connection string option, therefore you will need to execute some commands manually :

var context = new BloggingContext();
var connection = context.Database.GetDbConnection();
connection.Open();
using (var command = connection.CreateCommand())
{
    command.Text= "PRAGMA journal_mode=WAL;";
    command.ExecuteNonQuery();
}

You could wrap that in your constructor or in a factory.

Related post and other one.

Matchlock

Does this have to be done for every connection or can it just be done once? – Gusgusba

journal_mode is persitet in the SQLite database (in contrast to most other PRAGMAs) on set. So you don't need to do this at all in your code (only once after database creation, i.e. by using the sqlite3 cmdline client). See sqlite.org/pragma.html#pragma_journal_mode – Whaleboat