0

I want to use

  • SQLite with SQLCipher
  • Entity Framework Core 3.1
  • Multiple Threads that call the Db (thread-safe)

The EF DbContext is not thread-safe natively. My approach was using a new DbContext for each db call, which is looking like that:

DbProvider

public Db GetDbContext()
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder()
    {
        DataSource = this.DbLocation,
        Mode = this.DbMode,
        Password = this.dbPassword,
    };
    return new Db(connectionStringBuilder.ConnectionString);
}

Default Db Call

using (var db = this.dbProvider.GetDbContext())
{
     var dataFromDb = db.DbSetXYZ.ToList()
}

this.dbProvider.GetDbContext() returns a new DbContext instance with a new SqliteConnection.

DbContext

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    this.sqliteConnection = new SqliteConnection(connectionString);
    this.sqliteConnection.Open();
    optionsBuilder
        .UseSqlite(this.sqliteConnection);
}

Without SQLCipher the performance is great. I see no big loading times ~10ms. With SQLCipher, the performace of this.sqliteConnection.Open(); changed to 2 seconds.

Is there any kind of best practice for this scenario?

5
  • A DbContext isn't a database connection. It's a client-side, disconnected Unit-of-Work and multi-entity repository. It tracks all changes made on the client side and only opens a connection when SaveChanges is called to persist all of them in a single transaction. Until then, there's no connection. Discard it, and the changes are gone. It doesn't need to be thread safe and definitely shouldn't be recycled for every operation. That breaks its transaction semantics - if you use a different one for every change, how are you going to roll back anything? Commented Mar 15, 2023 at 16:38
  • Multiple Threads that call the Db (thread-safe) why do you think you need that? That's where the problem starts. What the code shows is definitely a bad practice. Commented Mar 15, 2023 at 16:39
  • PS - SQLite is a single-user embedded database too, so even though you create the connections manually you still have problems. Even if you use WAL mode, what you read will lag behind writes. SQLite won't block readers until writes get committed, so even though your code won't crash, it will read stale data Commented Mar 15, 2023 at 16:40
  • The application is a wpf ui, which loads data from the db into a viewmodel, manipulate it by the user and save it back to the db. If the user do not want to save the changes, the view and the dbcontext get disposed. In the applications are multiple background threads, that polls different data and write them to the db. Yes, I know the issue of outdated data, caused by multiple threads read and write at the same time. That's another reason, why I search a better approach. Commented Mar 16, 2023 at 7:48
  • I had the idea to write some kind of dispatcher. It will take all db actions, and enqueue them into a separate db thread, which execute them. In this case, I have only one DbContext (not good) and only one SQLite open (good) command. Commented Mar 16, 2023 at 7:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.