Entity Framework 6 does not create tables in SQLite database

Question

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

I looked through and tried to follow the instructions in the following places (among other things), but they seem deprecated for different dbms and not for First code or any combination 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 finally installed the App.config file correctly, as shown below:

<?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: /questions/211239/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: /questions/211239/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> 

The definition of My DbContext 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 am trying to call the context as follows:

 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 expected, when I create an instance of MyDataContext , the myDatabase.sqlite file is created in my bin\debug\DataFile . However, when I try to call db.SaveChanges() , an exception is thrown.

InnerException for this exception 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 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, myDatabase.sqlite is zero. This tells me that although DbContext found the correct file name for the new database file from the app.config file, it did not create its first lines of code in the database, as intended.

Is there something obvious I'm missing here? All examples assume that this (extremely critical) step works, and everything in EF6 results from a working, properly configured database.

Thanks in advance for any help you can offer me.

Answer 1

According to Entity Framework 6 with SQLite 3 Code First - will not create tables , EF6 does not create tables when used with SQLite, so I had to do it myself.

I managed to create a SQLite database using DB Browser for SQLite and create tables in it myself. I had to be careful that the structure of the created tables matches the properties in my Model classes and use the [Key] annotation in the Model classes to indicate which field is the primary key field.

It is important to note that I had to add an existing element in Visual Studio to capture this file and make sure that Visual Studio knew about the file.

In addition, which is important, I had to go to the properties of this file and set its “Copy to output directory” property to “Copy if new” so that the database crashes into bin / debug or bin / release when I launch the application. If I did not, the database would not exist at run time, which could lead to a crash at run time.