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.

Just out of curiosity, what did you end up doing? did you use the devart provider or find another solution? — Justin Dalrymple
– Justin Dalrymple, Commented Jun 27, 2013 at 2:42
@StellaMusik I ended up using SQLExpress for the time being, but you can always use model or database first with SQLlite — Pierluc SS
– Pierluc SS, Commented Jun 27, 2013 at 10:53
@PierlucSS facing same issue. I don't want to use Devart's so apparently its gonna be model-first — Haikal Nashuha
– Haikal Nashuha, Commented Nov 10, 2013 at 6:45

Ladislav Mrnka · Accepted Answer · 2011-12-14 19:06:17Z

26

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.

edited Dec 14, 2011 at 19:06

answered Dec 14, 2011 at 19:00

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

default Over a year ago

Since this is an old post, do you know if this is still a valid answer? I am currently trying to find information about this and in my research progress, it does seem like this still holds true (but I am not sure)

user3141326 Over a year ago

I can verify that I even still cannot create database with current sqlite provider.

Ladislav Mrnka Over a year ago

@user3141326: Can you try EF Core (aka EF 7)? It should have build in provider for SQLite.

Jerther · Accepted Answer · 2023-01-31 17:25:28Z

16

Check this out:

https://github.com/msallin/SQLiteCodeFirst

Also available as a NuGet

It adds some DB Initializers to your ptoject, such as SqliteCreateDatabaseIfNotExists

Here's the example from the web site:

public class MyDbContext : DbContext
{
    public MyDbContext()
        : base("ConnectionStringName") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<MyDbContext>(
            Database.Connection.ConnectionString, modelBuilder);
        Database.SetInitializer(sqliteConnectionInitializer);
    }
}

edited Jan 31, 2023 at 17:25

answered Aug 19, 2015 at 20:18

Jerther

6,0559 gold badges44 silver badges63 bronze badges

1 Comment

Mark Feldman Over a year ago

Works fine. Current version (1.5.1.25) doesn't need the connection string passed into the SqliteCreateDatabaseIfNotExists constructor though, just the model builder.

Collectives™ on Stack Overflow

HOWTO: SQLite with EntityFramework and Code-First

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related