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
– magicandre1981, Commented Dec 9, 2013 at 5:17

Community · Accepted Answer · 2017-05-23 12:32:17Z

65

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.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Apr 23, 2014 at 7:31

kjbartel

10.6k7 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sly Over a year ago

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.

kjbartel Over a year ago

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

Florian K · Accepted Answer · 2017-03-09 08:57:45Z

33

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>

edited Mar 9, 2017 at 8:57

Florian K

62210 silver badges31 bronze badges

answered Jul 20, 2016 at 21:13

CleverPatrick

9,5715 gold badges69 silver badges88 bronze badges

2 Comments

ZZZ Over a year ago

this is the correct answer as of EF 6.1.3 and Sqlite .net library 1.0.102.0.

denver Over a year ago

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.

Xcelled · Accepted Answer · 2014-11-19 02:47:31Z

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, 2014 at 2:47

answered Dec 9, 2013 at 7:50

Xcelled

2,1044 gold badges23 silver badges40 bronze badges

11 Comments

Dean Over a year ago

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

Xcelled Over a year ago

@DeanK. Not necessarily, If you jump through some (not insignificant hoops), it is possible to do a copy-based deployment. See 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...

InteXX Over a year ago

@Xcelled194 - Also, here's a good tutorial on SQLCE/EF6 Private Deployment: [link]. 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.

Candy Chiu Over a year ago

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.

Buda Florin Over a year ago

@Xcelled194, 3 years later, in Visual Studio 2017 installing SQLite from nugget in still painfully

|

Reyn · Accepted Answer · 2014-07-24 13:52:04Z

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

Collectives™ on Stack Overflow

Problems using Entity Framework 6 and SQLite

4 Answers 4

2 Comments

2 Comments

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related