Using SQLite and EF6

09 Feb 2015

Created a sample console app using SQLite and EF6. The application uses the Chinook SQLite database and just displays the list of artists and albums that begin with A.

Below is the README:

SQLiteSample

Demonstrates usage of SQLite and EF6 in a console app

Prerequisites

  • System.Data.SQLite 1.0.94.1
  • Chinook SQLite Database
  • EF6

Setup

  • Create Blank Visual Studio Solution
  • Create Class Library Project
  • Create Console project
  • Install packages:
  • PM> Install-Package ChinookDatabase.Sqlite
  • PM> Install-Package System.Data.SQLite
  • Installing SQLite also installs EF6
  • Update-Package or Update-Package –reinstall <package name>
  • Leave <package name> blank to reinstall all

Errors

Column 'InvariantName' is constrained to be unique.  
Value 'System.Data.SQLite' is already present.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="mssqllocaldb" />
          </parameters>
        </defaultConnectionFactory>
        <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>
    </entityFramework>
    <system.data>
        <!--
            NOTE: The extra "remove" element below is to prevent the design-time
                  support components within EF6 from selecting the legacy ADO.NET
                  provider for SQLite (i.e. the one without any EF6 support).  It
                  appears to only consider the first ADO.NET provider in the list
                  within the resulting "app.config" or "web.config" file.
        -->
        <DbProviderFactories>
            <remove invariant="System.Data.SQLite" />
            <remove invariant="System.Data.SQLite.EF6" />
            <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
            <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>
</configuration>

Not sure why, but make sure to have the config for entityFramework and DbProviderFactories this way.

Reference to complex property in entity class requires specifying the property that references the complex property

SQL logic error or missing database
no such column: Extent1.Artist_ArtistId
namespace SQLiteSample.Entities
{
    public class Album
    {
        public long AlbumId { get; set; }

        public string Title { get; set; }

        // Required for sqlite to properly reference Artist
        public long ArtistId { get; set; } 

        public virtual Artist Artist { get; set; }
    }
}

Caveats

EF6 with SQLite still does not support Code First migrations.

Your migration code may need to use the SQLite api directly.

References