Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqLite in memory database : CREATE TABLE does not work?

When using this code on a SqLite file database, it works fine.

using (var ctx = new Test2010Entities())
{
    string s = "CREATE TABLE 'Company' ([Id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, varchar(50) NOT NULL);";

    ctx.ExecuteStoreCommand(s);

    ctx.Companies.AddObject(new Company { Code = "_1" });
    ctx.Companies.AddObject(new Company { Code = "_2" });

    ctx.SaveChanges();

    foreach (var c in ctx.Companies.ToList())
    {
        Console.WriteLine(c.Code);
    }
}


But when runnning this code on a SqLite 'In Memory' database (Data Source=:memory:;Version=3;New=True;) , I get this exception:

Unhandled Exception: System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SQLite.SQLiteException: SQL logic error or missing database no such table: Company

Note this is tested with VS 2010, EF 4.4.0.0 and sqlite-netFx40-setup-bundle-x86-2010-1.0.84.0


::: UPDATE :::
As Simon Svensson suggested, opening the connection before any other commands does do the trick: ctx.Connection.Open();

like image 779
Stef Heyenrath Avatar asked Oct 11 '25 19:10

Stef Heyenrath


1 Answers

This happens when your ORM closes your connection, and reopens it. That will reset the sqlite in-memory database to its default state; i.e. empty.

The same thing happens with NHibernate unless you set connection.release_mode = close (the default is after_transaction.

I'm not familiar with Entity Framework, but I expect a similar setting or using the DataContext(IDbConnection) constructor which is documented as "If you provide an open connection, the DataContext will not close it."

The same documentation also states "In a System.Transactions transaction, a DataContext will not open or close a connection to avoid promotion." which may be a cleaner solution.

Using some Reflector magic shows that it's SQLiteConnection.Open that calls (via SQLite3.Open) sqlite3_open_interop (if you're using the NuGet sqlite package). This shows that you get a new empty in-memory database everytime you call SQLiteConnection.Open.

like image 182
sisve Avatar answered Oct 14 '25 11:10

sisve