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.

remove the System.Data.SQLite.Linq from the provider section. — magicandre1981, Dec 09 '13 at 05:17

score 64 · Answer 1 · edited May 23 '17 at 12:32

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.

Great answer. It's slightly annoying that the SQLiteProviderServices class is internal and needs to be accessed via reflection, though. I had a look at the source code, and found an alternative: SetProviderServices( "System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService( typeof( DbProviderServices ) ) ); The GetService() call returns SQLiteProviderServices.Instance. — Sly, Mar 28 '15 at 15:20
@Sly Thanks for that, it's a good pick up. It is still a very very strange design decision by the System.Data.SQLite developers. Even the call to `GetService` makes you think that they're using some sort of DI/IoC but they aren't. — kjbartel, Apr 15 '15 at 06:48

score 33 · Answer 2 · edited Mar 09 '17 at 08:57

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>

this is the correct answer as of EF 6.1.3 and Sqlite .net library 1.0.102.0. — ZZZ, Aug 15 '16 at 11:47
Using entity framework 6 and the nuget package for Sqlite resulted in the .config file missing the third provider entry "System.Data.SQLite". The rest were all added correctly. Not sure why this would be, but adding the one line resolves the issue. — denver, Mar 21 '19 at 16:19

Xcelled · Accepted Answer · 2014-11-19T02:47:31.773

23

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.

edited Nov 19 '14 at 02:47

answered Dec 09 '13 at 07:50

Xcelled

2,084
3
22
40

1

Nice, but how will you distribute SQL Server Compact with your app? You will not be able to just copy the dll files, your users will have to install SQL Server Compact on their machines requiring admin rights... – Dean Kuga Mar 05 '14 at 06:58
@DeanK. Not necessarily, If you jump through some (not insignificant hoops), it is possible to do a copy-based deployment. See http://stackoverflow.com/a/10800781/1163742 for details. One thing to note is that the Nuget package seems to collect the dlls and stuff for you, and place them in the appropriate subdirectories... – Xcelled Mar 05 '14 at 07:25
@Xcelled194 - Also, here's a good tutorial on SQLCE/EF6 Private Deployment: [[link](http://erikej.blogspot.sk/2013/11/entity-framework-6-sql-server-compact-4_25.html)]. I was able to do this without anything more than linking to the 6 assemblies and 3 VC++ files in the Private X86/AMD64 folders of the SQLCE installation on my machine. I ClickOnce deployed it to a clean VM without SQLCE installed and it ran just fine. – InteXX Mar 24 '14 at 22:16
I tried this connector recently. It's disappointing. It is confusing when I explicitly specify to use EF6 but System.Data.SQLite still needs to be around. More confusing is when it's not, the connector throws an exception. Makes me wonder which version it's using under the hood. Moving on. – Candy Chiu May 23 '14 at 14:45
@CandyChiu: I found that's because of the DefaultConnectionFactory. Had to change it manually like here https://stackoverflow.com/questions/19025177/system-data-sqlite-entity-framework-code-first-programmatic-providername-specifi – Roman Plášil Mar 30 '16 at 15:13
@Xcelled194 I would say the SQL Server Compact isn't a good solution anymore. It is discontinued and it won't be supported going forward with Entity Framework 7. You maybe should look over your recommended solution again to make sure it works for people in the future. Microsoft has been pointing people instead to SQLite. – DotNet Programmer Mar 30 '17 at 13:42
@DotNetProgrammer is there documentation of that somewhere? I can't seem to find anything. – Xcelled Mar 30 '17 at 14:11
@Xcelled194 documentation about Microsoft has discontinued SQL Server Compact? – DotNet Programmer Mar 30 '17 at 15:20
@DotNetProgrammer and that they're steering people towards SQLite. I haven't used EF in years so maybe I'm missing something obvious! – Xcelled Mar 30 '17 at 16:37
1

@Xcelled194, 3 years later, in Visual Studio 2017 installing SQLite from nugget in still painfully – Buda Florin Apr 19 '17 at 20:20
@Buda Florin, so true. SQLite support in EF6 is just awful. If you want to have something else than just a code-first model (e.g. migrations) - it's pain. What makes it worse is EFCore and SQLite seems to work just great - which implies that they not going to improve EF6-SQLite combination. I started to look at Compact even though it is abandoned. – aderesh Jan 02 '18 at 01:57

score 18 · Answer 4 · answered Jul 24 '14 at 13:52

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));
    }
}

Problems using Entity Framework 6 and SQLite

4 Answers4

Linked