Simple example using System.Data.SQLite with Entity Framework 6

Question

I am trying to get a simple code first example to work in a console app using SQLite and EF6, however I am running into multiple errors: I created a new console project in VS 2015. Then install EF (6.1.3) and System.Data.SQLite (1.0.102) via NuGet.

Try to run a simple program:

namespace SQLiteConsole1 {     class Person     {         public int Id { get; set; }         public string Name { get; set; }     }      class MyContext : DbContext     {         public DbSet<Person> Persons { get; set; }     }      class Program     {         static void Main(string[] args)         {             using (var db = new MyContext())             {                 var person = new Person() { Name = "John" };                 db.Persons.Add(person);                 db.SaveChanges();             }         }     } } 

This is what my App.Config looks like this:

  <connectionStrings>     <add name="MyContext" connectionString="Data Source=C:\Temp\Test.sqlite" providerName="System.Data.SQLite" />   </connectionStrings>   <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.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />     </providers>   </entityFramework>   <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> 

When I first run this program I get the following error:

Unhandled Exception: System.InvalidOperationException: 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."

So I change <provider invariantName="System.Data.SQLite.EF6" to <provider invariantName="System.Data.SQLite", then I get this error:

Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. System.Data.SQLite.SQLiteException: SQL logic error or missing database no such table: People

What needs to be changed to get this simple example working?

Answer 1

A similar question is asked over here: Entity Framework 6 with SQLite 3 Code First - Won't create tables

kjbartel gives very useful explanation that table creation is not supported by the EF SQLite Driver.

Also see https://github.com/msallin/SQLiteCodeFirst, which provides an excellent solution. I installed the SQLite.CodeFirst NuGet package, and added the below code, then the app works fine:

    class MyContext : DbContext     {         protected override void OnModelCreating(DbModelBuilder modelBuilder)         {             var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<MyContext>(modelBuilder);             Database.SetInitializer(sqliteConnectionInitializer);         }         public DbSet<Person> Persons { get; set; }     }