SQLite + EntityFramework 6 setup

Question

I've been trying to get EF 6 and SQLite to play nicely in a WinForms app for over a week. I seem to be running into multiple random exceptions - which leads me to believe I'm not doing something correctly. I can't seem to find a suitable set up guide or tutorial.

I'm working with SQLite 1.0.93 and EF 6.1.0 straight from Nuget.

This is my Context class. I don't believe there's an issue with the DbSet classes.

public class MyDbContext: DbContext
{        
    public MyDbContext() : base("name=dbConnection")
    {
        System.Data.Entity.Database.SetInitializer<MyDbContext>(null);
    }

    public DbSet<Object1> Objects1 { get; set; }
    public DbSet<Object2> Objects2 { get; set; }
    public DbSet<Object3> Objects3 { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions
            .Remove<PluralizingTableNameConvention>();
    }
}

With this I'm also trying to populate two separate DataGridViews with Db data.

    public static BindingList<Objects1> Objects1BindingList
    {
        get
        {
            return MyDbContext.Objects1.Local.ToBindingList();
        }
    }

    public static BindingList<Objects3> Objects3BindingList
    {
        get { return MyDbContext.Objects3.Local.ToBindingList(); }
    }

    private static MyDbContext _data;

    public static MyDbContext  Data
    {
        get
        {
            return _data ?? (_data = new MyDbContext());
        }
    }

My App.config

  <connectionStrings>
    <add name="dbConnection"
      connectionString="Data Source=.\data.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <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.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" />
    -->
    </DbProviderFactories>
  </system.data>
  <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>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
    <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

Could somebody point me to a more recent and proven tutorial or point me in a better direction?

Thanks.

Answer 1

I have been trying to use sqlite with asp.net mvc5 and EF 6.1 and so far nothing but I have learnt some things that I can suggest you do looking at your app.config file:

    <connectionStrings>
    <add name="dbConnection"
      connectionString="Data Source=.\data.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description="Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    <!--  Leave this in there if you want to have EF working with your project -->    
      <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" />

    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
  <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.93.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
  <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" />
</providers>
    <!-- This only works for sql server localdb edition 
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
    <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory> -->
  </entityFramework>

The above is what I had in my web.config that got me going with my project (since web.config and app.config are basically the same) The only problem is that according to http://hintdesk.com/sqlite-with-entity-framework-code-first-and-migration/ you have to create the database with SQLite manager plugin in Mozilla before anything.

Even when I did I still got some exceptions from this "An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code" to other kinds of exceptions. It really sucks but maybe this can set you on your way.