System.Data.SQLite 1.0.91.0 and EF6.0.2

Question

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>

Here is my whole config file. I am running EF 6.0.2 with SQLite 1.0.91. I haven't tried the model generator yet, but my context objects work fine, and I have tested insert/update/delete via the context as well as direct SQL commands via the dbcontext. The trick is in the entityFramework/providers section. I had to duplicate the SQLite provider entries for the EF6 provider, but use the old and new invariant names.

    <?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <section name="electra.common.configuration.electraConfiguration" type="Electra.Common.Configuration.ElectraConfiguration, Electra.Common, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" allowDefinition="Everywhere" allowExeDefinition="MachineToApplication" restartOnExternalChanges="true" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
  </startup>
  <connectionStrings>
    <add name="DBContext_Server" 
        connectionString="server=sunset\sql2012;Integrated Security=SSPI;database=Electra;Pooling=true;max pool size=1000;min pool size=5;Connection Lifetime=30;connection timeout=15" 
        providerName="System.Data.SqlClient" />
    <add name="DBContext_ElectraPOS" 
        connectionString="Data Source=D:\Electra\ElectraWeb\PosEngine.Repository\ElectraPOS.db" 
        providerName="System.Data.SQLite.EF6" />
  </connectionStrings>
    <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>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        <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>
  </entityFramework>
  <electra.common.configuration.electraConfiguration>
    <logging>
      <levels info="true" warning="true" error="true" debug="true" />
    </logging>
  </electra.common.configuration.electraConfiguration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Http" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-2.1.0.0" newVersion="2.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Net.Http.Formatting" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-2.0.2.0" newVersion="2.0.2.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

After 6 hours of trial/error attempts, head banging on the wall and other stuff we devs do figuring out how things work, I believe I managed to have it working on VS 2013.

Follow this steps.

1. Install System.Data.SQLite if you haven't done so already.
2. Fetch System.Data.SQLite in your project from NuGet (notice it adds some stuff in the app.config).
3. This is the important part. Save your solution and exit VS 2013.
4. Reload the solution and now you can add a ADO.NET Entity Data Model into your project.

Each time you want to add a model you will have to exit VS 2013 and must create the model using "New Connection..."

Why it works this way, you say? The universe works in mysterious ways, I say.

Edit: Working further on it I discovered that in order to open a connection to the model you have to change the provider in the config from <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" /> to <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" /> and DbProviderFactories has to be inside a system.data tag in not in already.

Just want to share with all my experience with the same problem

I tried to use System.Data.SQLite 1.0.94 and EF6 and have some issues with connection to database. I work in VS 2013.4. After installing all necessary packages and cheking web.config as pmbc suggested I still had the same problem with connection and exception of type System.Data.Entity.Core.ProviderIncompatibleException.

After some additional searching in the internet I found this add-on for VS2013 to work with SQL Compact and SQLite Databases which really hepled me: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

Now I can use exsiting SQLite db/create new one from VS/create EF model using existing db and all other stuff

Hope this helps

UPDATE

Just some more info for help. Here is my resulting .config file section

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.95.0, Culture=neutral" />
    <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.95.0, Culture=neutral" />
  </providers>
</entityFramework>
<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SQLite" />
    <remove invariant="System.Data.SQLite.EF6" />
    <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    <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>

Also check version number of System.Data.SQLite.dll library which you installed locally (by defaulr, it is installed in GAC) Your GAC version and version of package in your current project must be the same. Otherwise you'll catch an exception when you start your app

It worked for me on VS 2010 using a console app and doing code first. In any case here is my app.config, which is based on Brice's EF6 SQLite tutorial (http://www.bricelam.net/2012/10/entity-framework-on-sqlite.html):

<?xml version="1.0"?>
 <configuration>
 <configSections>
    <!-- For more information on Entity Framework configuration, visit     http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
 </configSections>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="v11.0"/>
  </parameters>
</defaultConnectionFactory>
<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" />
</DbProviderFactories>
</system.data>
<connectionStrings>
 <add name="ChinookContext" connectionString="Data Source=|DataDirectory|Chinook_Sqlite_AutoIncrementPKs.sqlite" providerName="System.Data.SQLite"/>
</connectionStrings>
</configuration>