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?
SaveChangesis 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?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.