Vacuum Sqlite database with EntityFramework 6.1

Question

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

The application has a data synchronization process which over time become obsolete so I decided to delete them from the database. As expected the size of the data base is not reduced by removing table rows, so I decided to run the command VACUUM therein.

After reading this excellent blog SQLite, VACUUM, and auto_vacuum, everything became much clearer to me, especially the fact that the command can not be executed within a transaction.

Like Code First is not yet available, I have to create the tables in the database with scripts, so in the same place I execute 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 to receive the following exception:

Additional information: SQL logic error or missing database. Cannot VACUUM from within 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)

What it makes me think that all commands executed by the method ExecuteSqlCommand are processed within 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 about that? If so, is there a way of executing the command?

Answer 1

Try this to force EF to not use transaction because

A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "VACUUM;");