Why can't I use file extensions in Entity Framework Core Sqlite files?

Question

When I try to use Entity Framework Core with Sqlite and a file extension of .db or .sqlite, I get an error

SqliteException: SQLite Error 14: 'unable to open database file'

If I remove the file extension, then it works fine.

I'm not very familiar with Entity Framework Core, and have been using things like npoco in the past. I'm working on a DataStorage and DataAggregator and I'm experiencing issues that require implementation of some hackery. I don't like it and it makes me feel dirty.

If I remove the .db from the connection string, then it works just fine, but if I leave any file extension on (I've only tried .db and .sqlite), I get that error.

Steps to recreate:

1. Clone https://github.com/EdLichtman/DataStorageService

2. dotnet run this solution (or play with the Unit Tests) and go to AggregateResults ControllerAction in your browser. It will try to find all files that end in .db and all that end in _db_metadata.json and will find none and will return "0" to the browser page.

3. Now go to the DataStorageService\DataStorageService\TransmittedFiles repository and clear it out. Go to Endpoints\DataStorage\AggregateData\AggregateDataContext.cs. Comment out the lines

   if (OperatingSystemHelpers.IsOSWindows)...
   

   up until

   .Remove(connectionStringFileLocation.Length - 3);
   

   Try to do exactly what you did before and you will see that 1) the AggregateData.db file is still created in TransmittedFiles, and 2) you should see the SqliteError 14.

If you clear the TransmittedFiles folder out again and try it with the OperatingSystemHelpers Clause back in, you'll see that it works. Meaning it isn't a matter of the file not existing.

Answer 1

The issue results from the previous connection still having access to the file.

As mentioned in the thread below Sqlite's C# implementation has a quirk where after a .Close() the connection is not truly cleaned up until a garbage collect runs

System.Data.SQLite Close() not releasing database file

To fix it you could add IDisposable to your IAggregateDataRepository and implement something similar like the following dispose method:

public void Dispose()
{
   _database.Dispose();
   GC.Collect();
   GC.WaitForPendingFinalizers();
}

You will need to call the disposers in your tests, this could be done by adding the following to the top of your TearDown method:

if (_aggregateDataRepository != null) 
{
   _aggregateDataRepository.Dispose();
}

As an extra bit of advice if you add this line at the end of the app context constructor it will ensure you are always in "Write Ahead Log" mode which is more happy with multiple connections. This isnt needed to fix the issue but as you look to be creating a restful web api interface on sqlite it will assist with concurrent calls.

Database.ExecuteSqlCommand("PRAGMA journal_mode=WAL;");

Further details https://www.sqlite.org/wal.html