System.Data.SQLite

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.

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?

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.

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

<pre>
    "FullUri=file::memory:?cache=shared;"
</pre>

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

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

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).

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).

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.

Absent further comments, this ticket will be closed at some point on or after
Thursday, December 19th, 2013.

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.

I uploaded my simplified test project in this zip:
https://www.dropbox.com/s/ztxrly7f4blh9dq/TestEntityFramework.zip

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.

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.