System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: d18dd1ce91b299a8ba997555c55251009b8543f9
Title: can't create tables with In-Memory database using EntityFramework 6
Status: Closed Type: Incident
Severity: Important Priority: Blocker
Subsystem: LINQ Resolution: Works_As_Designed
Last Modified: 2013-12-19 04:56:56
Version Found In: 1.0.89
User Comments:
anonymous added on 2013-12-10 15:25:35:
I'm trying to use the In-Memory feature of SQLite for my database integration testing. This is a part of the code I am trying to make work:

using (context = new DatabaseContext())
using (var file = new StreamReader("TableDefinitions.sql"))
{
    var sqlCommand = file.ReadToEnd();
    context.Database.ExecuteSqlCommand(sqlCommand);

    Element singleElement = null;
    singleElement = (from elements in context.Elements
                  where elements.Name == "Something"
                  select elements).FirstOrDefault();

}

As soon as the LINQ query is execute, I get the following error
 "SQL logic error or missing database 
no such table: Element"

If instead I use the SQLiteCommand and SQLiteConnection objects, passing only straight and pure SQL queries, it appears to work. But it's useless to me: I want to test my LINQ queries against the EntityFramework, possibly using an InMemory instance of SQLite, so I need to be able to create the tables in a way that can be accessed by the EntityFramework's DbContext.

mistachkin added on 2013-12-11 06:38:24:
I have a couple questions:

1. What is the schema of the database you are trying to query?
2. What version of the .NET Framework and Visual Studio are being used?

mistachkin added on 2013-12-11 07:56:42:
Also, is the same connection being used for both creating the data and reading
it?  By default, in-memory databases cannot be shared by multiple connections.

mistachkin added on 2013-12-11 08:01:40:

In order to share an in-memory database between connections, the following connection string must be used:

    "FullUri=file::memory:?cache=shared;"

The "FullUri" property of the above connection string must be used instead specifying a value for the "Data Source" property.


anonymous added on 2013-12-11 09:43:16:
In order to single out the issue I had simplified the schema of my database to a single table, this:

CREATE TABLE Element ( 
    ElementID      INTEGER        PRIMARY KEY AUTOINCREMENT
                                  NOT NULL,
    Name           VARCHAR( 60 ),
    Description    TEXT,
    Effect         TEXT,
    Price          REAL           NOT NULL
                                  DEFAULT 0,
    Picture        TEXT,
    AvailabilityID INTEGER        NOT NULL,
    ExpirationDate DATE
);

I do use the connection string as you posted it to share the in-memory database between connections: that's necessary, because Microsoft's EntityFramework closes the database connection automatically, without the programmer's control (the DbContext takes care of opening and closing connections "as needed", just to make things more difficult).

I am using Visual Studio 2012 and .NET 4.5
I started by using VS2013 and .NET 4.5.1 but seeing as the binaries don't officially support them yet I "downgraded" to VS2012 and .NET 4.5

mistachkin added on 2013-12-11 22:17:47:
Do you mean "clones" instead of "closes"?  If the Entity Framework is closing one
or more (or all?) connections automatically, that might be causing the content in
the in-memory database to disappear (i.e. since it is not saved to disk).

anonymous added on 2013-12-12 10:29:51:
No, I mean exactly that: "closes". I haven't explored the details, but according to MSDN the DbContext object (consider that it uses the UnitOfWork pattern) manages connections "automatically" without the programmer's intervention, and "opens and closes the connection to the database 'as needed', usually the connection gets closed right after the collected results are returned or displayed"

Unfortunately my project requires the EF6, but I thought that by using cache=shared in the connection string I could still make the InMemory DB work for integration testing.

As a side note: if I change the connection string to use a file in the filesystem as database, the code works (obviously).

mistachkin added on 2013-12-12 22:50:39:
If the Entity Framework manages to close all connections to the in-memory
database, all the data contained within it will be gone (i.e. even if shared
cache is enabled).

One possible solution would be to manually create a database connection to the
shared-cache in-memory database that the Entity Framework is completely unaware
of and simply hold it open, thereby preserving the changes you make to it.

mistachkin added on 2013-12-17 10:11:02:
Absent further comments, this ticket will be closed at some point on or after
Thursday, December 19th, 2013.

anonymous added on 2013-12-18 13:47:48:
Sorry for the long silence, I've been experimenting in these days to see if I could find a workaround.

I created a test project using NUnit, EntityFramework 5.0 (because System.Data.SQLite doesn't implement the new provider signature required for version 6.0), and the latest System.Data.SQLite dlls, with the following structure (I'm sorry I don't have the code with me, will attach a zip with the project later on):

On TestFixtureSetup, create a SQLiteConnection and open it to an in-memory, cache:shared database, then run a SQL script to create ONE table with an autoinc column and an nvarchar column; EntityFramework is set to map one object to that table.

On TestMethod, open the DBcontext, use LinQ to query how many rows are in that table, and Assert that number of rows is 0;

On TestFixtureTearDown, close the SQLiteConnection and dispose.

Unfortunately, despite the creation of the table (apparently) succeeded, when I hit the Linq query I get the usual exception: "database doesn't exist or sql error". I can't really tell if the problem lies in the EntityFramework or not, at this point.

anonymous added on 2013-12-18 14:57:01:
I uploaded my simplified test project in this zip:
https://www.dropbox.com/s/ztxrly7f4blh9dq/TestEntityFramework.zip

anonymous added on 2013-12-18 15:36:37:
I think I found where the issue is (kinda)...

I changed the code in the zip file above to reflect what I found: if I create the tables in the in-memory database using the SQLiteConnection that I keep open and separate from the EntityFramework (let's call it the "keepalive" connection), things seem to work as expected; If I use the ExecuteSQLCommand method of the DbContext.Database object, it isn't (probably) actually creating the tables, which would explain the Exception I was getting.
So it looks like DbContext.Database.ExecuteSQLCommand() isn't implemented or isn't working properly or something like that, but only when using System.Data.SQLite as a provider. But I'm happy with using the SQLiteCommand object for now.

Another thing I noticed is that if I create and dispose the "keepalive" connection at each SetUp and TearDown of the tests, I can't run tests in bulk but only one at a time, otherwise the tests fail (probably something to do with garbage collection or resource management in .NET); if I only open the connection in TestFixtureSetup and dispose it in TestFixtureTeardown (implementing a SetUp method to clean the tables before each test), it works fine.

mistachkin added on 2013-12-19 04:56:56:
Per my previous comment, I agree with your analysis.  If you create and retain a
SQLite database connection that the Entity Framework knows nothing about, that
will prevent the shared-cache in-memory database from going away prematurely.

Since both the SQLite and System.Data.SQLite components appear to be working as
designed, I'm going to close this ticket now.