Entity Framework 6 with SQLite 3 Code First - Won't create tables

Question

Using latest versions of EF6 and SQLite from NuGet. I finally got the app.config file to work after some useful posts on Stackoverflow. Now the problem is that the tables are not being created although the database is.

My app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite"
                type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <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" />
      <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" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="MyDBContext"
          connectionString="Data Source=|DataDirectory|MyDB.sqlite"
          providerName="System.Data.SQLite" />
  </connectionStrings>
</configuration>

My simple test program:

class Program
{
    static void Main(string[] args)
    {
        using (var db = new MyDBContext())
        {
            db.Notes.Add(new Note { Text = "Hello, world" });
            db.Notes.Add(new Note { Text = "A second note" });
            db.Notes.Add(new Note { Text = "F Sharp" });
            db.SaveChanges();
        }

        using (var db = new MyDBContext())
        {
            foreach (var note in db.Notes)
            {
                Console.WriteLine("Note {0} = {1}", note.NoteId, note.Text);
            }
        }

        Console.Write("Press any key . . . ");
        Console.ReadKey();
    }

    public class Note
    {
        public long NoteId { get; set; }
        public string Text { get; set; }
    }

    public class MyDBContext : DbContext
    {
        // default constructor should do this automatically but fails in this case
        public MyDBContext()
            : base("MyDBContext")
        {

        }
        public DbSet<Note> Notes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }
}

If I create the table manually the program works fine and the table is updated. If I delete the database, EF creates it but doesn't create the table and the program fails when it attempts to read back the data with an error message that the table does not exist.

Has anyone managed to get Code First working with EF6 yet? Would appreciate help/guidance on this as I'm now completely stuck!!!

Thanks all.

Solution 1

Unfortunately, the EF6 provider implementation in System.Data.SQLite.EF6 doesn't support creating tables. I downloaded the SQLite source code to have a look but couldn't find anything for creating tables and for migrations. The EF6 provider is basically the same as their Linq implementation so it's all aimed at querying the database rather than modifying it.

I currently do all of my work with SQL Server and generate sql scripts for SQLite using the SQL Server Compact & SQLite Toolbox. The scripts can then be run using an SQLiteCommand to simulate migrations.

Update

In EF7 support for SQL server compact has been dropped and a new provider for SQLite is being developed by the EF team. The provider will use Microsoft's managed SQLite wrapper project, Microsoft.Data.SQLite rather than the System.Data.SQLite project. This will also allow for using EF7 on iOS, Android, Windows Phone / Mobile, Linux, Mac etc. as Microsoft's wrapper is being developed as a portable library.

It's all still in beta but you can get nuget packages from the ASP.Net development feeds at MyGet (dev, master, release) if you wish to have a look. Look for the EntityFramework.SQLite package.

Solution 2

Edit (12.08.2021)

This post/lib is about EF 6 not EF Core. If you use EF Core, code first for SQLite is supported.

---

Initial (05.04.2015)

I started with the code from fried and created a project which lets you use CodeFirst. The project is available open source on GitHub or as NuGet Package.

If you encounter any problems or miss a feature, feel free to open a new issue on GitHub. Of course PRs are very welcome.

Edit (26.04.2016):

In the meantime I did a lot in this project.

The following (default EF) functionality is supported:

• Tables from classes (supported annotations: Table)
• Columns from properties (supported annotations: Column, Key, MaxLength, Required, NotMapped, DatabaseGenerated, Index)
• PrimaryKey constraint (Key annotation, key composites are supported)
• ForeignKey constraint (1-n relationships, support for 'Cascade on delete')
• Not Null constraint
• Auto increment (an int PrimaryKey will automatically be incremented)
• Index (Decorate columns with the Index attribute. Indices are automatically created for foreign keys by default. To prevent this you can remove the convetion ForeignKeyIndexConvention)

There are also some features exclusive for SQLite, which are not supported by default:

• Unique constraint (Decorate columns with the UniqueAttribute, which is part of this library)
• Collate constraint (Decorate columns with the CollateAttribute, which is part of this library)

There are two ways to use the functionality of this library.

1. Make use of the DbInitializers:

• SqliteCreateDatabaseIfNotExists
• SqliteDropCreateDatabaseAlways
• SqliteDropCreateDatabaseWhenModelChanges

2. Get more control by using one of the following two classes:

• SqliteSqlGenerator (creates the SQL based on a EdmModel)
• SqliteDatabaseCreator (creates a new SQLite database based on a Database and DbModel)

Edit (30.05.2020 & 20.03.2021): As EF 6 now supports .NET Core 3 and above, I adjusted the library to support .NET Standard 2.1 as well. The following .NET framework versions are supported:

• .NET 4.0 (uses net40)
• .NET 4.5-4.8 (uses net45)
• .NET Core 3.0-3.1 (uses netstandard2.1)
• .NET 5 (uses netstandard2.1)

Solution 3

I decided to write my own rudimentary database initializer to solve this problem.

You can check it out here: https://gist.github.com/flaub/1968486e1b3f2b9fddaf

It supports:

• Many-to-Many relationships
• Code-First data annotations like:
  • [Key]
  • [Required]
  • [Index]
  • [ForeignKey]

Solution 4

The same to you. I have used Compact 4.0 instead of Sqlite.

As a reference:

• http://hintdesk.com/sqlite-with-entity-framework-code-first-and-migration/
• http://damienbod.wordpress.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/

View more solutions

Comments

[user1192887]

Updae Mar 24: Still no progress just downloaded latest versions of SQLite (1.0.92) with EF6 (6.0.0) still no success. Tried EF 6.0.2 & 6.1.0 neither worked . Any suggestions how to resolve this problem? HELP!!

[user1192887]

Yes I've gone down the same route. I'm now moving app to Windows 8.1 store app which doesn't support EF at all!!! Wake up MS, we really need proper ORM support on Windows Store apps even if its only for SQLite. It's a real pain having to do all the change tracking and transaction support that EF provides.

[Spock]

@user1192887 I could not agree more. I'm having exactly the same issue. What's your thoughts on using the sql compact 4.0 instead of SQLite?

[Junle Li]

I need a single alone SQL database for my continuous integration test because my test environment have no SQL server. And I think this is a better choice for me. Thanks! nuget.org/packages/EntityFramework.SqlServerCompact/6.1.2

[fried]

I was fed up and wrote my own initializer, check out my answer below!

[kjbartel]

@user1192887 Read my update. MS has apparently "woken up" as they've been developing EF7 from the ground up and it will work in the Windows Store with SQLite.

[Mauro Sampietro]

it seems to me there's support for SqlServerCompact in EF7 github.com/ErikEJ/EntityFramework7.SqlServerCompact/wiki/…

[kjbartel]

@sam There's support in EF7 for any third-party database which someone writes a provider for. That's what ErikEJ has done. It's not part of the official EF7 code base though and thus not supported by the EF7 team / Microsoft.

[reduckted]

You sir, are awesome! I spent ages trying to get SQLite and EF6 to work, only to discover it won't create a database. This just made my day :) Thanks @fried as well!

[CAD bloke]

Using this in production and it works well. Awesome.

[The Senator]

Does this work with PCL / Xamarin based projects? I tried adding the package to a PCL and it complained about DataAnnotations. Would love to use it there if it can work.

[IngoB]

Very good! But not supporting migrations, unfortunately. Thanks anyway.

[Smartis]

This is a amazing solution. Thank you!

[Fabian]

Thanks a lot for your work! Should be the accepted answer.