Problems using Entity Framework 6 and SQLite

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

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

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

Problems using Entity Framework 6 and SQLite

Solution 1:

Solution 2:

Solution 3:

Related

Recent Posts