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();
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With