SQLite Entity Framework Code First: which Nuget package?

Question

I want to install SQLite, using entity framework, code first.

This is not answered in this question: What SQLite Nuget Package do I need? That is about SQLite without entity framework.

There are a lot of Nuget SQLite packages, some from MicroSoft, some from SQLite, and there is also a SQLite.Org

In visual studio, using the Nuget Package Manager, I started with Nuget package Sqlite.CodeFirst. That gave me SqlIte.CodeFirst + Entity framework.

const string dbConnectionString = "Data Source=..."  // full path to db file name
using (var dbContext = new SchoolDbContext(dbConnectionString)
{
    bool dbCreated = dbContext.Database.CreateIfNotExists();
}

Alas, that does not work, the program does not return from this function. Perhaps I should install a different package.

Try: nuget System.Data.SQLite.EF6. Compiles, but also does not return from CreateIfNotExist.

OnModelCreating is called:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
}

However after I return from this method, nothing happens. I also noticed that SQLiteConnectionStringBuilder is only available in the MicroSoft SQLite version.

So what package(s) should I download from nuget, to be able to use SQLite with a fairly modern EF version, code first?

Normally the tables are created automatically. Should I create them her in OnModelCreating, because SQLite doesn't do that for me?

Answer 1

See answer https://stackoverflow.com/a/29460684/3784642 (thanks to msallin) for which the relevant NuGet-Package is SQLite.CodeFirst (links and how-to in the original answer). Yet, that applies to SQLlite 3 with EF6 (in ASP.NET web API 2 in my case) with .NET Framework 4.5+. Because .NET Core seems now to have built-in support for code first with SQLite.

Remind that SQLite only accepts one column as primary key and this works like a breeze. I do also recommend explicit [Table("tableName")] [Key, Column(Order=1)] attribute markings to avoid any surprises with the implicit naming conventions.

here is some sample code (just to get inspired):

using SQLite.CodeFirst;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace DocGenWebApp.Models
{
    [Table("DocLog")]
    public class DocLogEntry
    {
        [Key, Column(Order = 1)]
        public int id { get; set; }
        [Index]
        public string PdfFileURI { get; set; }
        public DocStatus Status { get; set; }
        [Index]
        public DateTime StatusDateTime { get; set; }
        public string FileNameOrURI { get; set; }
        [Index]
        public string PdfJobUID { get; set; }
        public string Details { get; set; }

        public DocLogEntry(string fileURI, DocStatus status, string details)
        {
            PdfFileURI = fileURI;
            Status = status;
            StatusDateTime = DateTime.Now;
            Details = details;
            id = PdfFileURI.GetHashCode() ^ Status.GetHashCode();
        }

        public DocLogEntry()
        {
            PdfFileURI = "";
            Status = DocStatus.UNKNOWN;
            StatusDateTime = DateTime.Now;
            Details = "???";
            id = PdfFileURI.GetHashCode() ^ Status.GetHashCode();
        }
    }

    [Table("JobLog")]
    public class JobLogEntry
    {
        [Key, Column(Order = 1)]
        public int id { get; set; }
        [Index]
        public string PdfJobUID { get; set; }
        public JobStatusCode Status { get; set; }
        [Index]
        public DateTime StatusDateTime { get; set; }
        public string DocumentName { get; set; }
    public string Details { get; set; }

    public JobLogEntry()
    {
        PdfJobUID = "";
        Status = JobStatusCode.UNKNOWN;
        StatusDateTime = DateTime.Now;
        Details = "???";
        id = PdfJobUID.GetHashCode() ^ Status.GetHashCode();
    }
}

public class LogEntryDBContext : DbContext
{
    public LogEntryDBContext() : base("LogEntryDBContext") { }

    public DbSet<DocLogEntry> DocLogEntries { get; set; }
    public DbSet<JobLogEntry> JobLogEntries { get; set; }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            var sqliteConnectionInitializer = new SqliteDropCreateDatabaseWhenModelChanges<LogEntryDBContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
        }
    }
}

and the web.config sections in my case (partly generated when importing the Nugets System.Data.SQLite and SQLite.CodeFirst, then amended):

<connectionStrings>
    <add name="LogEntryDBContext" connectionString="Data Source=C:\myPath\LogsDB.sqlite;Version=3;Pooling=True;Max Pool Size=10;Journal Mode=Off;" providerName="System.Data.SQLite.EF6" />
</connectionStrings>
<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.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>