c# - System.Data.SQLite 1.0.91.0 and EF6.0.2

Question

Has anyone gotten the new System.Data.SQLite 1.0.91.0 to work with Entity Framework 6 in Visual Studio 201#? If you have, how did you do it?

Update - 20 Mar 2014: System.Data.SQLite 1.0.92.0 has been released but I had no luck creating an EDMX in VS2013 :( I ended up using using Package Manager (because EF6.#.# is a dependency in the new SQLite NuGet package):

uninstall-package entityframework -force

restart VS2013 and put the older EF5 on to get VS2013 to generate an EDMX from an existing database:

install-package entityframework -version 5.0.0

Note: This was not a complex, multi-table SQLite relational database test so I am not sure what other problems will arise if I do use anything with more than a couple Navigation (FK) relationships :/

ANSWER for EDMX/Model First: (Update - 2 Mar 2014) I found a work-around but it is not consistent enough and requires too many steps to consider it a valid solution. Basically:

• you make all the Class(es) file(s),

• make a connection to an existing SQLite database with tables,

• modify the web/app.config to as described by mistachkin in the still outstanding SQLite Trouble Ticket (http://system.data.sqlite.org/index.html/tktview?name=7b8fd3ef77), including faking the cdsl/.ssdl/.msl pieces, and

• then manually create all the Entity Models in the Designer in an Empty EDMX before rebuilding the project and then...

• your right click on an Entity and choose 'Update from database'...

Sometimes EF/VS2013 will add the .tt/DbContesxt and sometimes they don't. Way too 'hit or miss' :(

ANSWER for "Code First" with and without an existing SQLite database (based on PMCB, Drexter, and Jimi's suggestions). Note however that you cannot generate any EF models or EDMX files [sic - Conceptual Schema Definition Language (.CSDL), Store Schema Definition Language (.SSDL), and Mapping Specification Language (.MSL)] automatically in Visual Studio 2013. I did not try manually creating the EDMX files to see if they would be palatable to EF and even if I did, it seems to me that doing all the manual creation/mapping/changes/XML edits defeats the whole purpose/concept of a entity based framework...

<connectionStrings>
    <add name="DogsContext" connectionString="Data Source=|DataDirectory|dogs.s3db;" providerName="System.Data.SQLite" />
</connectionStrings>
<entityFramework>
    <providers>
       <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </providers>
</entityFramework>
<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
</system.data>

Here is the test Class (note that I had to change the DogID to Int64 to get it to work with SQLite...):

using System.Data.Entity;

namespace WebApplication1.Models
{
    public class Dog
    {
        public Dog() { }

        public Int64 DogID { get; set; }
        public string DogName { get; set; }
    }
}

and here is the test DbContext:

using System.Data.Entity;

namespace WebApplication1.Models
{
    public class DogsContext : DbContext
    {
        public DogsContext() : base() { }

        public DbSet<Dog> DogNames { get; set; }
    }
}

============= Original Details of Question ==================

SQLite 1.0.91.0 was released yesterday (http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki) with "Add support for Entity Framework 6". There is a caveat in the included 'Readme' that has you add the following to the following to web.config/app.config:

<configuration>
<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SQLite" />
        <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite"
             type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
</system.data>
</configuration>

If you use NuGet in VS2013 to add "System.Data.SQLite (x86/x64)", you now get this line added to web.config/app.config:

<entityFramework>
...
<providers>
<provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
</providers>
...
</entityFramework>

As a test, I made a copy of a working NET4.5.1/MVC4/EF5/System.Data.SQlite 1.0.90 application and ran PM: update-package on it. It successfully added the above line to my web.config and I added the required "DbProviderFactories" pieces. Rebuild and run... Fail with 'no provider found'. I try it without the "DbProviderFactories"... Fail with 'no provider found'. I remove the new "provider" piece... Fail with 'no provider found'. So I try a new project but with and without Identity (OWIN) in case that is the problem. Neither work... Pretty much run out of ideas, so asking here after Google/StackOverflow searches.

============ 14 Feb 2014 ===============

Unfortunately the changed entries and numerous variations did not work on my PC... I am using VS 2013 Pro on Win8.1 Pro x64. I reinstalled System.Data.SQLite. At least I am getting a new error with both the existing SQLite database and the new one that VS2013 created (which I can access and see it has a proper structure with zero tables):

"An error occurred connecting to the database. The database might be unavailable. An 
exception of type 'System.Data.Entity.Core.ProviderIncompatibleException' occurred. The 
error message is: Schema specified is not valid. Errors: StoreSchemaDefinition(2,64) : 
Error 0175: The ADO.NET provider with invariant name 'System.Data.SQLite.EF6' is either 
not registered in the machine or application config file, or could not be loaded. See the 
inner exception for details.'"

Both EF6 and System.Data.SQLite (x86/x64) where pulled in via NuGet into brand new MVC Web apps and a Windows Console app. Both produced the above error... I am beginning to suspect that maybe something is wrong with my VS3013 but I do not want to spend another 6 hours downloading and patching it when I can work with MVC4/SQLite/EF5...

===== 17 Feb 2014 ========

@Jimi - No luck here. I tried with NET 4.5 and 4.5.1 with a new MVC and Windows Application Project. I tried with EF6.0.0 and EF6.0.2. I tried replacing the 3x SQLite.Data.xxx.dll refs with local copies from the install of System.Data.SQLite after adding "System.Data.SQLite (x86/x64)" from NuGet . I also tried the NuGet “System.Data.SQLite.MSIL” package instead of adding DBFactories to the various versions of Web.Config and App.Config that I tried.

I also tried creating an NET 4.0 MVC4 Web Application but it appears NuGet’s "System.Data.SQLite (x86/x64)" now includes a requirement for EF6. Being hopeful, I went along with it and tried the various ‘save/create new connection/edit web.config/etc’ but it would not work. I have given up for now and gone back to SQLite with Datasets so I can use Linq with System.Data.DataSetExtensions by making the DataTables "AsEnumerable" (or Java (jdbc) for people that want an offline copy of the database on their Android OS devices with a minimal App interface).

========= 19 Feb 2014 =======

@Christian Sauer - I did not get EF6 and System.Data.SQLite (or System.Data.SQLite.EF6) to work together, yet... but

I just found an update on http://system.data.sqlite.org/index.html/tktview?name=7b8fd3ef77 that says there is a newer SQLite NuGet package:

mistachkin added on 2014-02-19 03:39:35:

All NuGet packages have been updated to 1.0.91.3.  Several fixes are included
that pertain to supporting Entity Framework 6.

I am testing it now with a new Project (MVC Web Project in VS2013 using EF6.0.2 and SQLite 1.0.91.3 that targets .NET 4.5.1)...

No luck again with the new SQLite 1.0.91.3. The web.config has changed to:

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
   </DbProviderFactories>
 </system.data>

I am going to try messing with the config after work (as suggested by Jimi and PCMB) and post any findings.

I just tried re-installing both the 32 and then the 64 bit versions of System.Data.SQLite (with and without adding to GAC) but EF6 will not work with SQLite. If I try to manually build the model and mappings, it fails any time I reference/try to use EF6. As for creating Entity Data Models (be it generate from existing SQLite or new SQLite database) it will fail/error out no matter what I do to configs, connections or providers.

=========== 22 Feb 2014 =============

They pulled/rolled-back the System.Data.SQLite 1.0.91.3 update to 1.0.91.0. There is still an outstanding ticket (http://system.data.sqlite.org/index.html/tktview?name=7b8fd3ef77) open that includes some suggestions by mistachkin. Here is the app.config mistachkin recommended trying (this config did not work for me either before or...):

<?xml version="1.0"?>
&l

Answer 1

I worked on this for several hours today before figuring it out. The NuGet package adds the appropriate entry "System.Data.SQLite.EF6" into the EntityFramework provider factories, but you have to remove or comment out the old "System.Data.SQLite" entry. The EF context builder blows up if any of the provider entries are invalid, and the old 1.0.91 SQLite provider is not supported in EF6.

EDIT: Cleaning up the config allowed my other context objects (SQL Server) to instantiate, but it still won't instantiate the SQLite context objects correctly. The inner exception is

Unable to find the requested .Net Framework Data Provider. It may not be installed.

EDIT 2/SOLUTION Finally figured it out! I think there's an issue in System.Data.SQLite that is forcing a reference to the System.Data.SQLite provider name rather than the value provided in the connection string. I was able to work around this by creating two entries in the EntityFramework provider section, one named "System.Data.SQlite", and one named "System.Data.SQLite.EF6", but both providers actually use the EF6 provider.

<entityFramework>
...
<providers>
    <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.91.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
</providers>