Skip to content

EF6 workflow with SQLite DDEX provider

Jump to bottom
Erik Ejlskov Jensen edited this page Jun 9, 2019 · 53 revisions

The System.Data.SQLite DDEX provider does not support Visual Studio 2017. This post describes how to work with SQLite and Entity Framework 6 in Visual Studio 2017, 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 15.6 and earlier)

  • 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 sqlite-netFx46-setup-bundle-x86-2015-1.0.110.0.exe (from https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)

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. The only SQLite related entry should look like this, with this exact version number:

<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.110.0, 
                    Culture=neutral, 
                    PublicKeyToken=db937bc2d44ff139" 
        />  
    </DbProviderFactories> 
</system.data>

If you are using Visual Studio 15.7, that includes the updated EF 6.2 Tools, generating an EDMX is broken. You can add the following entry (as the last entry in the list) to the DbProviderfactories section in your machine.config as a workaround until this issue has been fixed (remember to restart Visual Studio after making the change):

      <add name="SQLite Data Provider" 
           invariant="System.Data.SQLite" 
           description=".NET Framework Data Provider for SQLite" 
           type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

Some users report that adding this to app.config solves some runtime issues (related the the VS 15.7 issues mentioned above)

<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 like this after install:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net461" />
  <package id="System.Data.SQLite" version="1.0.110.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Core" version="1.0.110.0" targetFramework="net461" />
  <package id="System.Data.SQLite.EF6" version="1.0.110.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Linq" version="1.0.110.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