winform SQLite vs2019 EF ADO.net实体数据模型

VisualStudio2019  C#  EntityFramework6  SQLite的环境配置

我最近的开发都使用微软的EF6，这种把数据库关系到对象上的方式操作数据非常简单，不需要去记烦人的SQL，并且在对象操作中就实现了数据关联到数据库中，这会使代码更少更优雅。我喜欢用EntityFramework，很多需求，就开发一个小工具，也用不上专门的数据库系统，保存到本地的数据库文件中就可以了，SQLite是很好的选择。

ErikEJ  EntityFramework的专家，为我们带来了SQLite Toolbox来实现vs2015，vs2017，vs2019中使用C#，EntityFramework,  ADO.net实体数据模型，SQLite的技术组合。

以WINFORM为例，按下面的四步，我们在项目中添加“ADO.net实体数据模型”，关联到SQLite文件数据库，实现了EF6的LINQ语法操作文件数据库。

---------2022.2.17----安装日记------------

1、安装SQLite and SQL Server Compact Toolbox v4.8.740.vsix

链接：https://pan.baidu.com/s/1AryttkJCyxtguUfx9isKSw?pwd=dls3 
提取码：dls3

2、sqlite-netFx46-setup-bundle-x86-2015-1.0.115.5.exe 

链接：https://pan.baidu.com/s/1bNSZBnUcyiqiHB9dph9A0A?pwd=fu4w 
提取码：fu4w

3、PM> Install-Package System.Data.SQLite

      SQLite相关类库会自动更新到最新版

4、打开原来的程序发现实体模型中没有表，原来文件数据库需要指定全路径地址： 

 

---------2021.10.21----安装日记------------

1、安装SQLite and SQL Server Compact Toolbox v4.8.738.vsix

2、sqlite-netFx46-setup-bundle-x86-2015-1.0.115.0.exe 

这个文件下载地址有一点调整：System.Data.SQLite: Downloads Page - Unsupported

3、PM> Install-Package System.Data.SQLite

----------下文是作者ErikEJ的详细安装说明：----------

https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

需要配置的步骤如下：

一、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 System.Data.SQLite: Downloads Page)

Select "Full Installation"

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

Restart Visual Studio

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

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.113.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 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.113.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Core" version="1.0.113.0" targetFramework="net461" />
  <package id="System.Data.SQLite.EF6" version="1.0.113.0" targetFramework="net461" />
  <package id="System.Data.SQLite.Linq" version="1.0.113.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" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <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" />
    </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.

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