Problems using Entity Framework 6 and SQLite

Question

I'm trying to use Entity Framework with SQLite. I had issues integrating it into my main application, so I started a little test from scratch, exactly following the directions on http://brice-lambson.blogspot.com/2012/10/entity-framework-on-sqlite.html

After all is said and done, I get the following error when running the project:

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SQLite'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

My app.config looks like this:

<?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" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <add name="SQLite Data Provider"
            invariant="System.Data.SQLite"
            description="Data Provider for SQLite"
            type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="ChinookContext"
          connectionString=
"Data Source=|DataDirectory|Chinook_Sqlite_AutoIncrementPKs.sqlite"
          providerName="System.Data.SQLite" />
  </connectionStrings>
</configuration>

Then I saw his post about Entity Framework 6. While it wasn't the exact error I was getting, I tried installing his updated provider via NuGet. The error was gone, but replaced with this one:

Could not load file or assembly 'System.Data.SQLite.Linq, Version=2.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Additionally, my app.config got changed (slightly) to this:

<?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" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <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" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq, Version=2.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description="Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <remove invariant="System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="ChinookContext" connectionString="Data Source=|DataDirectory|Chinook_Sqlite_AutoIncrementPKs.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
</configuration>

I've tried everything I can think of to address these errors, nothing has worked. I've tried using the other SQLite binaries; I've tried manually editing the SQLite project to use the EF version 6; I've changed the architectures, I've added and removed the nuget packages over and over, etc.

I have no idea where to go from here.

Answer 1

Just thought I'd share another way to configure EF6 with SQLite without using app.config / web.config. EF6 now supports code based configurations as outlined here on msdn. I used the following code (updated to remove reflection thanks to Sly):

public class SQLiteConfiguration : DbConfiguration
{
    public SQLiteConfiguration()
    {
        SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
        SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
        SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
    }
}

I use this so I can inject the correct DbContext and hence DbProvider at runtime and don't need everything configured in the main assembly.

Edit:

As Reyn said you will also need to add an IDbConnectionFactory for SQLite if you wish to have your connection string in your web.config / app.config file. Another approach is to call a different base constructor from your DbContext which passes in a new SQLiteConnection rather than the connection string, as shown in this answer.

Answer 2

I know this is an old question, but no one seems to have provided an answer that actually uses the .config file. Here is the system.data and entityframework sections of my web.config that allows connections to both SQLServer and Sqlite databases:

<system.data>
    <DbProviderFactories>
      <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" />
      <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" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <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" />
      <provider invariantName="System.Data.SQLite" 
          type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>

Answer 3

Based on magicandre1981's comment, I began to look more closely at the syntax of the provider node. I found that my assembly was a different version than what was specified in the type attribute, though I had not inserted or touched that particular line. By deleting the strong naming, I got .Net to load the library. For reference, here's the new line:

<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq" />

That put me back on track and I was able to match my results with the ones on the blog.

I feel compelled to note, however, that I have decided that SQLite is not a good fit for the Entity Framework, as too many critical functions are missing. I switched over to SQL Server Compact Edition, which I installed via NuGet. A simple tweak to my Connection String and I was running with the full power of Entity Framework. It took less than a minute, compared to the multi-hour slog that was SQLite. I'd recommend switching databases if possible, System.Data.SQLite just isn't ready for the Entity Framework.

Answer 4

For anyone using the code based setup and still getting the same exception: I needed to additionally set the connection factory in kjbartel's answer.

public class SQLiteConnectionFactory : IDbConnectionFactory
{
    public DbConnection CreateConnection(string nameOrConnectionString)
    {
        return new SQLiteConnection(nameOrConnectionString);
    }
}

Then set the default connection factory in SQLiteConfiguration

public class SQLiteConfiguration : DbConfiguration
{        
    public SQLiteConfiguration()
    {
        SetDefaultConnectionFactory(new SQLiteConnectionFactory());
        SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
        SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
        Type t = Type.GetType( "System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6");
        FieldInfo fi = t.GetField("Instance", BindingFlags.NonPublic | BindingFlags.Static);
        SetProviderServices("System.Data.SQLite", (DbProviderServices)fi.GetValue(null));
    }
}