Entity Framework 6 not creating tables in SQLite database

Question

I'm trying to get System.Data.SQLite to work with Entity Framework 6, using the Code First approach in my C#.NET (4.5.2) WPF project.

I've looked over and tried to follow the instructions in the following places (among others), but they appear to be either out of date, for a different dbms, not for Code First, or some combination of the above.

https://msdn.microsoft.com/en-us/data/jj592674

https://msdn.microsoft.com/en-us/data/jj592674.aspx

http://nullskull.com/a/10476742/sqlite-in-wpf-with-entity-framework-6.aspx

Entity Framework 6 + SQLite

I think I've finally got my App.config file set up correctly, as follows:

<?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.2" />
  </startup>
  <entityFramework>
    <!-- from: https://stackoverflow.com/questions/14510096/entity-framework-6-sqlite -->
    <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" /> 
        <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
     </defaultConnectionFactory>
  </entityFramework>
  <connectionStrings>
    <add name="MyDatabase" connectionString="Data Source=.\DataFile\myDatabase.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
    <system.data>
    <!-- from: https://stackoverflow.com/questions/14510096/entity-framework-6-sqlite -->
    <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>
</configuration>

My DbContext definition is as follows:

public class MyDataContext : DbContext
{
    public MyDataContext() :  base("name=MyDatabase")
    {
    }
    public DbSet<DataItem> DataItems { get; set; }
}

My DataItem class is as follows:

public class DataItem
{
    [Key]
    public int MyInt { get; set; }
    public string MyString { get; set; }
}

And I'm trying to call the context like this:

using (var db = new MyDataContext())
{
    DataItem item = new DataItem { MyInt = 19, MyString = "nineteen" };
    db.DataItems.Add(item);
    try
    {
        db.SaveChanges();
    }
    catch (Exception ex)
    {
        var x = ex.InnerException;
        Debug.WriteLine("Inner Exception: {0}", x);
        throw;
    }

}

As I expect, when I instantiate MyDataContext, the myDatabase.sqlite file gets created in my bin\debug\DataFile directory. However, when I try to call db.SaveChanges(), an exception is caught.

The InnerException of this exception is 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: DataItems

When I look in the DataFile directory, the myDatabase.sqlite file is zero bytes. This tells me that though the DbContext found the proper filename for the new database file from the app.config file, it did not do its Code First creation of tables within the database as it is supposed to.

Is there something obvious I'm missing here? The examples all assume this (extremely critical) step works and everything in EF6 follows from a working properly set up database.

Thanks in advance for any help you can offer me on this.

Answer 1

According to Entity Framework 6 with SQLite 3 Code First - Won't create tables, EF6 doesn't create tables when used with SQLite, so I had to do so myself.

I was able to create a SQLite database using DB Browser for SQLite, and create the tables in it myself. I had to be careful to make sure the structure of the tables I created matched the properties in my Model classes, and to use the [Key] annotation on the Model classes to indicate which field is the primary key field.

Importantly, I had to Add Existing Item in Visual Studio to grab that file and make sure that Visual Studio knew about the file.

Also, importantly, I had to go to the Properties of that file and set its 'Copy to Output Directory' property to 'Copy if Newer', so that the database makes its way to the bin/debug or bin/release directory when I run the app. If I didn't do this, the database would not exist at runtime, which would cause a runtime crash.