HOWTO: SQLite with EntityFramework and Code-First

Question

I am trying to create an embedded SQLite database on the fly with the EF however, I can't get it to work, the database file is never getting created.

I have EF 4.2 and latest version SQLite

Here is what I have

app.config

<?xml version="1.0"?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <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>
  <connectionStrings>
    <add name="DataContext"
         connectionString="Data Source=test.db;Version=3;New=True;"
         providerName="System.Data.SQLite" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" />
  </startup>
</configuration>

DB initializer (to put some content in)

class PageDbInitializer : DropCreateDatabaseAlways<PageDB>
{
    protected override void Seed(PageDB context)
    {
        for (int i = 0; i < 10; i++)
        {
            WebPage page = new WebPage() { Name = "Page" + (i + 1) };
            context.Pages.Add(page);
        }
        base.Seed(context);
    }
}

DbContext:

class PageDB : DbContext
{
        public DbSet<WebPage> Pages { get; set; }
}

And finally in the main()

Database.SetInitializer( new PageDbInitializer() );

I believe I have some steps missing, but can't find them out.

Answer 1

System.Data.SQLite is in current version only EFv1 compatible provider (1, 2). It means it doesn't have EFv4 (EFv4.2 is wrapper around EFv4) features including database creation and deletion (check DbProviderServices differences for .NET 3.5 and .NET 4.0 in MSDN). Because of that you cannot use automatic database creation with code first when working with SQLite. You must create database and all tables manually.

As an alternative you can use different provider for SQLite. For example Devart's SQLite provider claims it supports EFv4 and 4.1 and thus database creation.