Efcore: Release and/or Delete SQLite database

[johnmurphy01]

Steps to reproduce

I have a sqlite database that I create locally. Whenever the app goes online and a user signs in an identical in structure, already populated sqlite database is retrieved from a server. I also have an "offline" database that stores local changes that eventually get synced to the cloud. My app uses the offline database as the main database and attaches the online database. The problem I am having is when I try to retrieve this online database and overwrite the existing online database. I have tried closing the connection, detaching the database and calling EnsureDeleted but every time I try to save the file data to the existing database file, I am notified that the file is already being used by another process. Which makes sense, because the SQLite file is being used.

The issue

I need a way to release the file from the process either by detaching the database and/or closing the connection or deleting the existing file. Database.CloseConnection(), Database.EnsureDeleted() does not appear to work. When I call Database.EnsureDeleted(), I also get the error so the attempts to detach and close the connection beforehand also did not work.

Further technical details

EF Core version: 1.0.1
Operating system: Windows 10
Visual Studio version: VS2015

[divega]

Possibly related to https://github.com/aspnet/Microsoft.Data.Sqlite/issues/299.

[johnmurphy01]

I don't _think_ that's the issue(at least I hope not because there's no current solution other than adding an exception to Defender which would require user interaction). I turned off Windows Defender and I'm still getting the exception about the file still being in use. I also tried exluding *.db files and also excluding the file path to my database files as well.

To me, it seems like my app still has a handle on the file and I'm either not releasing it properly from EF or there is an issue with releasing when you have two databases, one attached to the other or something(just guessing).

Some more information:
I have two Contexts: one is the offline database context and whenever it's used(via a using statement), it opens the connection and attaches the online database. This is the context I use for all of my querying since both databases are connected after attachment. The other context is the online database context and doesn't do much other than make sure it's created via Database.EnsureCreated()

Whenever the online database is downloaded from the cloud, I try to detach the existing one(from the offline database context) by:
Database.ExecuteSqlCommand(string.Format("detach database '{0}'", alias));

Then I attempt to close and dispose of the old online database file(trying anything I can think of) inside the online database context) by:
Database.CloseConnection();
Database.EnsureDeleted(); // throws error here if this is un-commented
var db = Database.GetDbConnection();
db.Close();
db.Dispose();
Dispose();

[johnmurphy01]

@jnm2 Does SQLite have the concept of taking a database offline? I couldn't find any references to this concept with regards to SQLite. I think it may just be a SQL Server idea.

[jnm2]

@johnmurphy01 My bad, I was thinking LocalDB.

[ErikEJ]

@johnmurphy01 Make the file readonly?

[johnmurphy01]

@ErikEJ setting the file to read only also does not appear to work. I confirmed that read only was set by going to the file and checking the properties in Windows Explorer. I also rebooted my machine just to make sure there wasn't some other process outside of my application that had a handle on the file.

[rowanmiller]

There isn't anything obvious from the description that should prevent this from working. I think we're going to need something we can run in order to debug this. Are you able to provide a project that demonstrates the scenario so that we can debug it?

[johnmurphy01]

Sure I'll put something together

[johnmurphy01]

@rowanmiller I was able to replicate with a stripped down project that contains two empty sqlite databases. When the app launches it creates the db files if they do not exist and then attaches the secondary database. When you click the Detach button, it will detach the secondary database and attempt to close the connection and then delete the file.

https://drive.google.com/file/d/0B7s2r8lGfsBiU3VnT2dRWk5ITDg/view?usp=sharing

[johnmurphy01]

Anyone have a chance to look at the project I posted? If this is not something that can be fixed, I need to move away from EF Core and go back to some other SQLite Wrapper like SQLite.NET

[johnmurphy01]

@rowanmiller forgot to tag you on the above posts

[rowanmiller]

Thanks for the app to reproduce it. We'll get someone assigned to debug it. We've had a bit of a wave of issues come in, so it may take a little bit to get to it.

[johnmurphy01]

@divega is there an ETA on a solution? I'm ready to start testing my application but this issue is preventing me from doing so effectively. If it's going to be a while, I will need to consider other options.

[ajcvickers]

@johnmurphy01 I haven't been able to reproduce what you are seeing, but I have noticed a few things about the solution that look suspicious. Some things to consider:

• OnlineContext has a singleton instance of the DbContext. This is not recommended. DbContextis designed to represent a relatively short-lived session; usually a single unit-of-work. It should be disposed when the session is done and a new context instance created for the next session.
• OfflineContext also seems to have an OnlineContext singleton, although it is different from the singleton managed by OnlineContext itself. It's not clear how these are designed to interact.
• The ContextWrapper.Context gets a new OfflineContext instance every time it is called, but sometimes that context instance is never disposed--for example, when it is used for the InitializeContext call.

It would probably be a good idea to update the design to ensure that DbContext instances are being managed and disposed appropriately. I suspect that what you are seeing is a consequence of something not being disposed properly.

[johnmurphy01]

@ajcvickers thanks for the input. I have struggled to wrap my head around the best way to structure my context, that's certain. It appears that removing the singleton pattern from the context creation was the issue. After re-instantiating the context for each section of work, I was able to delete the db file with no problems.