Skip to content

EF6 workflow with SQLite DDEX provider

Jump to bottom
Erik Ejlskov Jensen edited this page Jul 7, 2024 · 53 revisions

The System.Data.SQLite DDEX provider does not support Visual Studio 2017, 2019 and 2022. This post describes how to work with SQLite and Entity Framework 6 in Visual Studio 2017/2019/2022, using the "SQLite Toolbox" DDEX provider for EF6. Notice that this provider only supports the EF 6 Tools, and not other Data Source scenarios, for example Typed DataSets. This requires Visual Studio 2017 15.8 or later.

Notice special instructions for VS 2022, since it is a 64 bit application

  • Install Toolbox
  • Install SQLite in GAC
  • Install SQLite EF provider in project
  • Run EDM Wizard

Install latest Toolbox

Once per Visual Studio edition (daily build at https://github.com/ErikEJ/SqlCeToolbox/wiki/Release-notes )

Install SQLite in GAC

Once per machine. Download the latest sqlite-netFx46-setup-bundle-x86-2015-1.0.xxx.0.exe (from https://system.data.sqlite.org/index.html/doc/trunk/www/downloads-unsup.wiki)

VS 2022 File name is: sqlite-netFx46-setup-bundle-x64-2015-1.0.xxx.0.exe

Select "Full Installation"

Select: Install the assemblies into the global assembly cache - Install VS designer components

sqliteddex

Restart Visual Studio

Verify that the EF6 provider is installed in GAC from the Toolbox "About" dialog:

sqliteddex4

sqliteddex

If the EF6 provider is not in GAC, this may be due to an invalid entry in machine.config, located in the C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Config folder.

VS 2022 Copy the entry to the 64 bit machince.config located in C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\Config

The only SQLite related entry should look like this, with a version number matching the download version number (in this example .117):

<system.data>
    <DbProviderFactories> 
        <add name="SQLite Data Provider" 
             invariant="System.Data.SQLite.EF6" 
             description=".NET Framework Data Provider for SQLite" 
             type="System.Data.SQLite.EF6.SQLiteProviderFactory, 
                    System.Data.SQLite.EF6, 
                    Version=1.0.117.0, 
                    Culture=neutral, 
                    PublicKeyToken=db937bc2d44ff139" 
        />  
    </DbProviderFactories> 
</system.data>

Some users report that adding this to app.config solves some runtime issues.

<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />

Install System.Data.Sqlite NuGet package

Install using Package Manager Console or NuGet Manager in each project.

PM> Install-Package System.Data.SQLite

Make sure to install the same version as the tools package above.

Build project!

Packages.config should look similar to this after install:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.4.4" targetFramework="net461" />
  <package id="System.Data.SQLite" version="1.0.117.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Core" version="1.0.117.0" targetFramework="net461" />
  <package id="System.Data.SQLite.EF6" version="1.0.117.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Linq" version="1.0.117.0" targetFramework="net461" />
</packages>

App.config should look like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" 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" />
   </DbProviderFactories>
  </system.data>
</configuration>

Run Entity Data Model Wizard

Add, New Item, Data, ADO.NET Entity Data Model. Choose "EF Designer from Database" or "Code First from Database"

Use "SQLite Provider (Simple for EF6 by ErikEJ)" when creating a connection to your SQLite database file. Enter the full path to your database file in Data Source.

sqliteddex3

A reader of this wiki post has provided some additional tips here

Clone this wiki locally