Every query on SQLite DB using EF Core in Docker is very slow, unless there is an explicit call to OpenConnection

Question

I am running a simple .NET Core 3.1 web application on localhost. The web application is running in a Docker Linux container (my host OS is Windows 10). The SQLite database used by the web application is connected using EF Core, is almost empty and very small at 60Kb, each table having 20 records or less, and is stored on the host and mounted for the container using following command:

--mount type=bind,source='SomeDummyHostPath',target=/mnt/hostFolder

The problem is that every query executed on DBContext runs very slow (~2s for reading 2 rows). When I run the application outside of the Docker container, everything runs smoothly (meaning that the same query is executed in ~1ms).

What is strange is, after invoking dbContext.Database.OpenConnectionAsync() before every call to EF's DBSets, every query in the Docker container runs quickly (a few milliseconds).

Why is the performance so bad in this scenario? Is there any way to improve it without the explicit call to OpenConnectionAsync before every query?

Answer 1

EF Core 3.x included one breaking change that affects this use-case, the connection is closed earlier:

Database connection is now closed if not used anymore before the TransactionScope has been completed

Mitigations:

If the connection needs to remain open explicit call to OpenConnection() will ensure that EF Core doesn't close it prematurely

The sqlite provider is still not using connection pooling: 13837;

With the new releases of EntityFrameworkCore.Sqlite (>3.0), there are some new optimizations regarding transactionality, and new .WAL file is created when the .db file is open; This, combined with lack of connection pooling results in creating and removing the .WAL file with every request; And docker volume/file mapping to windows file system gets to be very time consuming.