RaelB RaelB - 1 month ago 105
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" };
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

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