RaelB RaelB - 2 years ago 1079
C# Question

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

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

This is what my App.Config looks like this:

<add name="MyContext" connectionString="Data Source=C:\Temp\Test.sqlite" providerName="System.Data.SQLite" />
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<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" />
<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>

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"
<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 Source

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);
        public DbSet<Person> Persons { get; set; }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download