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
Config related 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
- http://stackoverflow.com/questions/22174212/entity-framework-6-with-sqlite-3-code-first-wont-create-tables/23128288#23128288
- http://hintdesk.com/sqlite-with-entity-framework-code-first-and-migration/
- https://damienbod.wordpress.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/
- http://brice-lambson.blogspot.no/2012/10/entity-framework-on-sqlite.html