C# .Net ef(Entity Framework 6) SQLite配置使用(codefirst)

1、项目中需要安装SQLite相关Nuget包

项目名上右键=》点击"管理Nuget程序包"=》搜索"System.Data.SQLite"=》点击"System.Data.SQLite(x86/x64)"、"System.Data.SQLiteEF6"、"System.Data.SQLiteLINQ"这3个包进行安装。

搜索"SQLite.CodeFirst"=》点击安装

确保以下Nuget包都已安装：

System.Data.SQLite(x86/x64)
System.Data.SQLiteEF6
System.Data.SQLiteLINQ
SQLite.CodeFirst
EntityFramework

2、项目中代码

1）新建ORMContext类继承DbContext

using SQLite.CodeFirst;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UtilityWeb.ORM.Models;
namespace UtilityWeb.ORM
{
    public >ORMContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<ORMContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
        }
        public ORMContext() : base("ORMContext") { } //配置使用的连接名
        public DbSet<EmployInfo> EmployInfos { get; set; }
        public DbSet<DeptInfo> DeptInfos { get; set; }
    }
}

2）用到的Model类DeptInfo和EmployInfo

public>EmployInfo
{
publicstringDeptName{get;set;}
publicstringEmployName{get;set;}
publicstringGender{get;set;}
[Key]
publicstringUserCode{get;set;}
publicintRuleCode{get;set;}
publicintOrderCode{get;set;}
}
public>DeptInfo
{
publicstringSubDept{get;set;}
publicstringDeptName{get;set;}
publicstringDeptCode{get;set;}
publicstringRuleCode{get;set;}
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
publicintDeptId{get;set;}
}

说明：[key]是配置主键，[DatabaseGenerated(DatabaseGeneratedOption.Identity)]配置自增主键

3）使用操作示例代码

   public static void AddDeptInfo(DeptInfo m)
    {
      using (ORMContext db = new ORMContext())
      {
        var model = db.DeptInfos.Where(w => w.DeptName.Trim() == m.DeptName.Trim() && w.SubDept.Trim() == m.SubDept.Trim()).FirstOrDefault();
        if (model == null)
        {
          db.DeptInfos.Add(m);
          db.SaveChanges();
          return;
        }
        model.SubDept = m.SubDept.Trim();
        model.DeptName = m.DeptName.Trim();
        model.DeptCode = m.DeptCode.Trim();
        model.RuleCode = m.RuleCode.Trim();
        db.SaveChanges();
      }
    }
    public static void AddUserInfo(EmployInfo u)
    {
      using (ORMContext db = new ORMContext())
      {
        var model = db.EmployInfos.Where(w => w.UserCode == u.UserCode).FirstOrDefault();
        if (model == null)
        {
          db.EmployInfos.Add(u);
          db.SaveChanges();
          return;
        }
        model.UserCode = u.UserCode;
        model.Gender = u.Gender;
        model.EmployName = u.EmployName;
        model.RuleCode = u.RuleCode;
        db.SaveChanges();
      }
    }

3、项目中用到的配置文件

如果出现奇怪的问题，仔细检查一下配置文件中entityFramework下providers和DbProviderFactories，自动生成修改的配置文件，有时会有问题，我之前就被坑了。

1）控制台程序配置文件

<?xmlversion="1.0"encoding="utf-8"?>
<configuration>
<configSections>

<sectionname="entityFramework"type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection,EntityFramework,Version=6.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"requirePermission="false"/>
</configSections>
<connectionStrings>
<addname="ORMContext"connectionString="datasource=.\utilityweb.db"providerName="System.Data.SQLite.EF6"/>
</connectionStrings>
<startup>
<supportedRuntimeversion="v4.0"sku=".NETFramework,Version=v4.5"/>
</startup>
<entityFramework>
<defaultConnectionFactorytype="System.Data.Entity.Infrastructure.LocalDbConnectionFactory,EntityFramework">
<parameters>
<parametervalue="mssqllocaldb"/>
</parameters>
</defaultConnectionFactory>
<providers>
<providerinvariantName="System.Data.SQLite.EF6"type="System.Data.SQLite.EF6.SQLiteProviderServices,System.Data.SQLite.EF6"/>
<providerinvariantName="System.Data.SQLite"type="System.Data.SQLite.EF6.SQLiteProviderServices,System.Data.SQLite.EF6"/>
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<removeinvariant="System.Data.SQLite.EF6"/>
<addname="SQLiteDataProvider"invariant="System.Data.SQLite"description=".NetFrameworkDataProviderforSQLite"
type="System.Data.SQLite.SQLiteFactory,System.Data.SQLite"/>
<addname="SQLiteDataProvider(EntityFramework6)"invariant="System.Data.SQLite.EF6"
description=".NetFrameworkDataProviderforSQLite(EntityFramework6)"
type="System.Data.SQLite.EF6.SQLiteProviderFactory,System.Data.SQLite.EF6"/>
</DbProviderFactories>
</system.data>
</configuration>

2）Web网站用到的配置文件

<?xml version="1.0" encoding="utf-8"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=152368
  -->
<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>
  <connectionStrings>
    <add name="ORMContext" connectionString="data source=D:\dbpath\utilityweb.db" providerName="System.Data.SQLite.EF6" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="2.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="PreserveLoginUrl" value="true" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
    <add key="DbConnectString" value="User Id=BFCSJQ;Password=DHHZDHHZ;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.255.90)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=srvcbzb1)))" />
  </appSettings>
  <system.web>
    <httpRuntime targetFramework="4.5" />
    <compilation debug="true" targetFramework="4.5" />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" />
    </authentication>
    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Optimization" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
      </namespaces>
    </pages>
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </profile>
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    <roleManager defaultProvider="DefaultRoleProvider">
      <providers>
        <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </roleManager>
    <!--
            If you are deploying to a cloud environment that has multiple web server instances,
            you should change session state mode from "InProc" to "Custom". In addition,
            change the connection string named "DefaultConnection" to connect to an instance
            of SQL Server (including SQL Azure and SQL  Compact) instead of to SQL Server Express.
      -->
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />
      </providers>
    </sessionState>
  </system.web>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <handlers>
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="hljs-string">"0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="hljs-string">"0" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" />
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.3.0.0" newVersion="1.3.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v12.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite" 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" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <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" />
    </DbProviderFactories>
  </system.data>
</configuration>