CODEWITHSUNDEEP
sqliteentity-framework-core
shlatchz
shlatchz

Reputation: 1652

EF7 with SQLite

I'm trying to work on my SQLite DB with EF7. When I initialize the connection string with System.Data.SQLite.SQLiteConnectionStringBuilder() as below:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = Constants.DataDBPath };
    connectionStringBuilder.DefaultTimeout = 5000;
    connectionStringBuilder.SyncMode = SynchronizationModes.Off;
    connectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Memory;
    connectionStringBuilder.PageSize = 65536;
    connectionStringBuilder.CacheSize = 16777216;
    connectionStringBuilder.FailIfMissing = false;
    connectionStringBuilder.ReadOnly = false;
    connectionStringBuilder.Version = 3;
    var connectionString = connectionStringBuilder.ToString();
    var connection = new SQLiteConnection() { ConnectionString = connectionString };
    optionsBuilder.UseSqlite(connection);
}

I get the following error:

Specified cast is not valid.

On the command:

isExists = context.Blocks.Where(w => w.Hash == hash).Any();

And when I initialize a connection string with Microsoft.Data.Sqlite.SqliteConnectionStringBuilder() as below:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder() { DataSource = Constants.DataDBPath  };
    var connectionString = connectionStringBuilder.ToString();
    var connection = new SQLiteConnection() { ConnectionString = connectionString };
    optionsBuilder.UseSqlite(connection);
}

I don't get any errors. Everything works but the updates are slow. I don't know how to configure the database to increase performance as I did with the previous connection string builder. When I try to manually add parameters such as "synchronous=Off;pooling=True;" to the connection string, I get an exception that the parameters aren't recognized.

Please help.

UPDATE:

I tried:

using (var context = new DataDBContext())
{
    var connection = context.Database.GetDbConnection();
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = "PRAGMA synchronous=OFF; PRAGMA journal_mode=OFF; PRAGMA count_changes=OFF; PRAGMA temp_store=OFF; PRAGMA page_size=65536; PRAGMA cache_size=-16777216;";
    command.ExecuteNonQuery();
}

But it didn't help.

Upvotes: 1

Views: 1744

Answers (1)

ErikEJ
ErikEJ

Reputation: 41749

You have to use the Microsoft supplied provider with EF Core, and it only supports a small subset of the connection string options in System.Data.Sqlite. Many of the options you can set via pragma statements

Upvotes: 1

Related Questions