Viacheslav Eremin | How to use SQLite with EF6.

(MVC) MVC (2018)

How to use SQLite with EF6.

All needed information about SQLite has assembly in site https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki, and this is another instruction from developer https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider.

But understanding all of that information is little bit challenge, therefore I try to enumerate main point of this site:

I

There are amazing free GUI for SQLlite - https://sqlitestudio.pl/index.rvt. This software is independent from any another software below.
For any version of Visual is exist amazing extension - SQLite/SQL Server Compact Toolbox. This Toolbox not related to Entity Framework plugins to Visual Studio and working in any version of Visual Studio (not only in VS2015).
In 2018 year still nothing Entity Framework design time component further then Visual Studio 2015. This is main point. Because Visual Studio is 32-bit version of program (not X64), fot design time you must to use 32-bit installation. And moreover design-time component must be set to GAC. Component of EF wisard and showing EDMX file not related to SQLite Toolbox SQLite or runtime component.

Any attempt to open EF component in VS2017-VS2019 is finished by crash
Also there SQLite windows runtime component SQLite for Windows Runtime

There four Nuget packages you need to install to each project as runtime component https://www.nuget.org/profiles/SQLite. This is my application.config file:

   1:  <?xml version="1.0" encoding="utf-8"?>

   2:  <packages>

   3:    <package id="EntityFramework" version="6.2.0" targetFramework="net452" />

   4:    <package id="System.Data.SQLite" version="1.0.111.0" targetFramework="net452" />

   5:    <package id="System.Data.SQLite.Core" version="1.0.111.0" targetFramework="net452" />

   6:    <package id="System.Data.SQLite.EF6" version="1.0.111.0" targetFramework="net452" />

   7:    <package id="System.Data.SQLite.Linq" version="1.0.111.0" targetFramework="net452" />

   8:  </packages>

II

This is result of SQLite installation to my computer

This is my working config of EF6 (SQLite) application.

   1:  <?xml version="1.0" encoding="utf-8"?>

   2:  <configuration>

   3:    <configSections>

   4:      <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->

   5:      <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

   6:    </configSections>

   7:    <startup>

   8:      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />

   9:    </startup>

  10:    <entityFramework>

  11:      <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />

  12:      <providers>

  13:        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />

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

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

  16:      </providers>

  17:    </entityFramework>

  18:    <system.data>

  19:      <DbProviderFactories>

  20:        <remove invariant="System.Data.SQLite.EF6" />

  21:        <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" />

  22:        <remove invariant="System.Data.SQLite" />

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

  24:      </DbProviderFactories>

  25:    </system.data>

  26:    <connectionStrings>

  27:      <add name="Url_DB" connectionString="metadata=res://*/UrlDB.csdl|res://*/UrlDB.ssdl|res://*/UrlDB.msl;provider=System.Data.SQLite.EF6;provider connection string=&quot;data source=E:\Projects\Resume\Resume\Data\ResumeDB.db&quot;" providerName="System.Data.EntityClient" />

  28:    </connectionStrings>

  29:  </configuration>

But after I done EF Wizard it still do not working. One string I add manually.

This is Toolbox diagnostic of my development computer.

   1:  Version 4.7.645.0

2:

   3:  SQL Server Compact 4.0 in GAC - Yes - 4.0.8876.1

   4:  SQL Server Compact 4.0 DbProvider - Yes

   5:  SQL Server Compact 4.0 DDEX provider - No

   6:  SQL Server Compact 4.0 Simple DDEX provider - Yes

   7:  SQL Server Compact 3.5 in GAC - Yes - 3.5.8080.0

   8:  SQL Server Compact 3.5 DbProvider - Yes

   9:  SQL Server Compact 3.5 DDEX provider - No

  10:  Sync Framework 2.1 SqlCe 3.5 provider - No

  11:  SQLite ADO.NET Provider included: 1.0.110.0

  12:  SQLite EF6 DbProvider in GAC - Yes

  13:  System.Data.SQLite DDEX provider - Yes

  14:  SQLite Simple DDEX provider - Yes

This is fragment of my machine.config provider, related to SQLite (Net framework 4, 32-bit).

 168:    <system.data>

 169:      <DbProviderFactories>

 170:        <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

 171:        <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

 172:        <remove invariant="MySql.Data.MySqlClient" />

 173:        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.15.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

 174:      <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.111.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" /></DbProviderFactories>

 175:    </system.data>

And this is list of my GAC, related to SQLite.

   1:  Microsoft (R) .NET Global Assembly Cache Utility.  Version 4.0.30319.0

   2:  Copyright (c) Microsoft Corporation.  All rights reserved.

3:

   4:  The Global Assembly Cache contains the following assemblies:

1077:    System.Data.SQLite, Version=1.0.111.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=AMD64

1183:    System.Data.SQLite, Version=1.0.111.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=x86

3058:    System.Data.SQLite.EF6, Version=1.0.111.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=MSIL

3059:    System.Data.SQLite.Linq, Version=1.0.111.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=MSIL

3310:  Number of items = 3304

III

Also there are some another cunning to use SQLite with EF6.

Don't include Database to project, therefore if DB includes, Visual Studio will be create a DB copy in project working directory. How to avoid this situation I've describe in page Template to save SQLiteDB (EF6) to Temporary Location..

I recommend this function in any use of EF6, because error message of EF6 is unclear.

 206:          Dim ID As Integer = CurRow.I

 207:          Dim R As Site = Rows.Where(Function(X) X.I = ID).FirstOrDefault

 208:          R.LastVisit = Now

 209:          DbSaveChange()

 ....

 224:      Public Sub DbSaveChange()

 225:          Try

 226:              db1.SaveChanges()

 227:          Catch ex As DbEntityValidationException

 228:              Dim Str1 As New System.Text.StringBuilder

 229:              For Each eve In ex.EntityValidationErrors

 230:                  Str1.Append(String.Format("Entity of type ""{0}"" in state ""{1}"" has the following validation errors:", eve.Entry.Entity.[GetType]().Name, eve.Entry.State))

 231:                  For Each ve In eve.ValidationErrors

 232:                      Str1.Append(String.Format("- Property: ""{0}"", Error: ""{1}""", ve.PropertyName, ve.ErrorMessage))

 233:                  Next

 234:              Next

 235:              MsgBox(Str1.ToString)

 236:          End Try

 237:      End Sub

(

)

<00> <01> <02> <03> <04> <05> <06> <07> <08> <09> <10> <11> <12> <13> <14> <15> <16> <17> <18> <19> <20> <21> <22> <23>

Link to this page: //www.vb-net.com/SQLite_EF6/index.htm