Saturday, March 31, 2018

Using Entity Framework 6 and SQLite with Visual Studio 2017

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

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:

<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net461" />
  <package id="System.Data.SQLite" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Core" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.EF6" version="1.0.108.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Linq" version="1.0.108.0" targetFramework="net461" />
</packages>
App.config should look like this:
<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.
sqliteddex3

No comments: