DbContext does not release SQLite database

Question

Firstly, these are my intentions:

• Create DbContext for SQLite
• Reading and writing from / to it
• Close context
• Move file to another location

Points 1-3 work fine. The problem starts when I try to move the database. I get an error message:

'The process cannot access the file because it is being used by another process.' 

How can i solve this?

First I create a context. I have to use it in several ways, and I do not want to create it every time I need it. Therefore, I keep it as a participant.

 _sqliteContext = new SqlLiteContext(sqliteContextName); 

Then I want to access a table called sync and get its latest record.

 var sync = _sqliteContext.Syncs.OrderByDescending(s => s.Date); _lastSync = sync.Any() ? sync.First().Date : new DateTime(0); 

What is it. Then I close the context.

 _sqliteContext.Dispose(); 

And try moving the file.

 File.Move(sqliteUploadLocation, sqliteDownloadLocation); 

Here I get an exception.

When I replace the selection with an insert, for example:

 var sync = new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now }; _sqliteContext.Syncs.Add(sync); _sqliteContext.SaveChanges(); 

This works and I can move the database. Any ideas why my choice does not release its lock?

Update

---

 // Start synchronisation. new SyncManager(mssqlString, sqliteUploadLocation).Start(); // Move file from upload to download location. try { File.Move(sqliteUploadLocation, sqliteDownloadLocation); } catch (Exception ex) { Console.WriteLine("Moving failed!"); Console.WriteLine(ex.Message); } public void Start() { // Create connection string for the sqlite database. const string sqliteContextName = "SqLiteContext"; var sqliteConnStringSettings = new ConnectionStringSettings { Name = sqliteContextName, ConnectionString = "Data Source=" + _sqliteUploadLocation + ";Version=3;BinaryGUID=False;", ProviderName = "System.Data.SQLite" }; // Read configuration, delete available connection strings and add ours. var conf = ConfigurationManager.OpenMachineConfiguration(); var connStrings = conf.ConnectionStrings; connStrings.ConnectionStrings.Remove(sqliteContextName); connStrings.ConnectionStrings.Add(sqliteConnStringSettings); try { conf.Save(ConfigurationSaveMode.Minimal); } catch (Exception) { // Insufficient rights to save. return; } ConfigurationManager.RefreshSection("connectionStrings"); // Create connection to the sqlite database. _sqliteContext = new SqlLiteContext(sqliteContextName); // Create connection to the mssql database. _mssqlContext = new MsSqlContext(_mssqlConnString); // Read last sync date. var sync = _sqliteContext.Syncs.OrderByDescending(s => s.Date); _lastSync = sync.Any() ? sync.First().Date : new DateTime(0); // Synchronize tables. //SyncTablePerson(); //SyncTableAddressAllocation(); // Creates an entry for this synchronisation. CreateSyncEntry(); // Release resources. _sqliteContext.Dispose(); _mssqlContext.Dispose(); } private void CreateSyncEntry() { var sync = new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now }; _sqliteContext.Syncs.Add(sync); _sqliteContext.SaveChanges(); } 

Update 2

---

 public class SqlLiteContext : Context { public DbSet<Sync> Syncs { get; set; } public SqlLiteContext(string connectionString) : base(connectionString) { Database.SetInitializer(new NoOperationStrategy<SqlLiteContext>()); } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new PersonConfig()); modelBuilder.Configurations.Add(new AddressAllocationConfig()); modelBuilder.Configurations.Add(new AddressConfig()); modelBuilder.Configurations.Add(new SyncConfig()); } } public class NoOperationStrategy<T> : IDatabaseInitializer<T> where T : DbContext { public void InitializeDatabase(T context) { } } public abstract class Context : DbContext { public DbSet<Person> Persons { get; set; } public DbSet<AddressAllocation> AddressAllocations { get; set; } public DbSet<Address> Addresses { get; set; } protected Context(string connectionString) : base(connectionString) { } } 

Refactoring Using

---

 using (var sqliteContext = new SqlLiteContext(_sqliteContextName)) { // Read last sync date. var sync = sqliteContext.Syncs.Select(s => s).OrderByDescending(s => s.Date); var lastSync = sync.Any() ? sync.First().Date : new DateTime(1900, 1, 1); using (var mssqlContext = new MsSqlContext(_mssqlConnString)) { SyncTablePerson(sqliteContext, mssqlContext, lastSync); SyncTableAddressAllocation(sqliteContext, mssqlContext, lastSync); // Save server changes. mssqlContext.SaveChanges(); } // Creates an entry for this synchronisation. sqliteContext.Syncs.Add(new Sync { Id = Guid.NewGuid().ToString(), Date = DateTime.Now }); // Save local changes. sqliteContext.SaveChanges(); } 

Answer 1

I found another topic with the same problem. After I reorganized my code, I added

 GC.Collect(); 

This removed the file lock, and I could move the file.

see below: fooobar.com/questions/104321 / ...

Answer 2

Two things come to mind:

• Verify that Visual Studio is not blocking the database file. Open the server explorer and if there is a connection to the file, make sure that it is completely closed or deleted.
• It is likely that pooling is what keeps the connection open. Disable the join in the connection string as follows:

Data Source = e: \ mydb.db; Version = 3; Union = False ;

As Matt noted, you really should use a statement using , rather than calling it manually. Thus, if there is an exception, resources are always released properly.