Is it safe to reuse Entity Framework Sqlite DBContext?

Question

I do something like:

  public class MyDbContext : DbContext {
    public MyDbContext(bool readOnlyFlag) {
      // Monitor.Enter(theLock); // needed ??
      this.readOnlyFlag = readOnlyFlag; 
      // Database.EnsureCreated(); // needed ??
    }

    public DbSet<MyData> MyData { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
      string connectionString = "Data Source=C:\\mydb.db;";
      if (readOnlyFlag) connectionString += "Mode=ReadOnly;";
      optionsBuilder.UseSqlite(connectionString);
    }

    public override void Dispose() {
      // Database.CloseConnection(); // needed ??
      base.Dispose();
      // Monitor.Exit(theLock); // needed ??
    }

    readonly bool readOnlyFlag;
    // readonly object theLock = new object(); // needed ??
  }

and then:

using (var dbc = new MyDbContext(true)) {
  dbc.MyData.Where( ... code
}

I call such code this from multiple concurrent threads to run different queries.. (in a .Net Core 3.0 console App)

Questions:

1. If I understand correctly the database file will be opened when the using block starts and closed when it ends. Closing and opening a file on each query seems really inefficient but I could not find any reference to whether or not it's OK to keep a singleton MyDbContext (ie in the Program class) and reuse it ?

2. If I can reuse MyDbContext should I then use a lock around queries ?

3. In general do I need to use ie the Monitor remarked above to make sure queries don't run concurrently ? I've seen posts saying Sqlite needs this ?

4. Do I need to call Database.CloseConnection() ? seems to work fine without it but I've seen posts where it was called like above remarked ?

5. is Database.EnsureCreated() needed for Sqlite ?

Thanks!

Answer 1

You can use DbContext with Sqlite multithreading. Normally, you should use DbContext as instance per request, because of DbContext is not thread safe, one commit should not affect the others.

As mentioned on sqlite's site, it supports mutltithreading:

SQLite supports three different threading modes:

Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.

The threading mode can be selected at compile-time (when the SQLite library is being compiled from source code) or at start-time (when the application that intends to use SQLite is initializing) or at run-time (when a new SQLite database connection is being created). Generally speaking, run-time overrides start-time and start-time overrides compile-time. Except, single-thread mode cannot be overridden once selected.

The default mode is serialized.

https://www.sqlite.org/threadsafe.html

Also I suggest you to take a look at this SQLite Concurrent Access and this Can I read and write to a SQLite database concurrently from multiple connections? .

According to above posts, sqlite writes locks the entire file even for reads. And in the internet some of the users suggests to taking locks in code explicitly for writes.

But new version of sqlite has a feature called WAL.

The second advantage of WAL-mode is that writers do not block readers and readers to do not block writers. This is mostly true. But there are some obscure cases where a query against a WAL-mode database can return SQLITE_BUSY, so applications should be prepared for that happenstance.

Sqlite itself says concurrent access even for multiple process can handled by sqlite.

And according to sqlite.org/faq

If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY.

It might be need to handled in application itself.

Answer 2

Are you sure that you are the only user of the data? In other words, are you sure that the data does not change between two usages of your dbContext?

Furthermore: are you sure that your dbContext will always be used this way, or might it be that in future this dbContext might be connected to a real database?

If your thread will be the one and only user, now and in future, there is not much harm in reusing the DbContext. However, keep in mind that it is not guaranteed that data is really written before you Dispose the dbContext. Furthermore: your dbContext will keep all fetched data in local memory, so after a while you will have your complete database in local memory.

Consider using a repository pattern, where you hide how the data is persisted, the repository pattern knows a bit more about what your repository is used for and can make smarter decisions about what data to keep in memory and what data to query from your database by a fresh dbContext.

For instance, if you have a database with Schools, Students, and Teachers, and you frequently query their data, but seldom query data of retired Teachers and data of graduated students, your repository could keep all fetched non-retired/graduated Teachers / Students in memory and only create a fresh dbContext to fetch unknown data, fetch retired / graduated data or update the database

interface IRepositorySet<Tentity> : IEnumerable<Tentity>
     where Tentity : class, new()
{
     Tentity Add(Tentity entity);
     Tentity Update(Tentity entity);
     Tentity Delete(Tentity entity);
}
interface ISchoolsRepository
{
     // for simple queries / add / update / remove only
     IRepositorySet<School> Schools {get;}
     IRepositorySet<Teacher> Teachers {get;}
     IRepositorySet<Student> Students {get;}
}

The RepositorySet knows which dbContext to create when it needs data. All frequently fetched items will be kept in memory in a Dictionary with primary Key.

Upon creation the Dictionary is filled with all primary keys, and value null, indicating that the item is not fetched yet.

When data is requested, the RepositorySet first fetches data from the dictionary. All items that have still a null value will be fetched from a fresh dbContext and put in the dictionary.

Note that this won't work for huge amounts of data. Only consider this solution if you think you can keep all fetched data in memory. But then again: keeping your dbContext open will also keep all fetched data in memory.