Sqlite vacuum database with EntityFramework 6.1

Recently, for fun, I decided to develop a small project to test the benefits of SQlite with EntityFramework provided by the System.Data.SQLite library .

The app has a data sync process that gets out of date over time, so I decided to remove it from the database. As expected, the database is not reduced in size by deleting table rows, so I decided to run the VACUUM command on it.

After reading this excellent blog, SQLite, VACUUM and auto_vacuum, things became much clearer, especially the fact that the command cannot be executed within a transaction.

Like Code First is not yet available, I need to create tables in a scripted database, so in the same place I am executing the command.

using (var context = new Context())
{
    context.Database.CreateIfNotExists();

    context.Database.ExecuteSqlCommand(
        "CREATE TABLE IF NOT EXISTS \"main\".\"OutgoingMessages\" (\"Id\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\"  datetime NOT NULL ON CONFLICT ROLLBACK,\"Status\"  INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Content\"  BLOB NOT NULL ON CONFLICT ROLLBACK,\"Size\"  INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Hash\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Comment\"  TEXT);" +
        "CREATE TABLE IF NOT EXISTS \"main\".\"IncomingMessages\" (\"Id\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\"  datetime NOT NULL,\"Status\"  INTEGER NOT NULL,\"Comment\"  TEXT);");

    context.Database.ExecuteSqlCommand("VACUUM;");
}

      

        
        
        
      

    

I was surprised I got the following exception:

Additional information: SQL logic error or missing database. Unable to VACUUM from a transaction.

An exception of type 'System.Data.SQLite.SQLiteException' occurred in EntityFramework.dll but was not handled in user code
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)

      

        
        
        
      

    

Which makes me think that all commands executed by the ExecuteSqlCommand method are processed in a transaction. I am using EntityFramework 6.1.3 and System.Data.SQLite 1.0.97.0 with .NET Framework 4.5.

QUESTIONS

Am I wrong? If so, is there a way to execute the command?