SQLite 的 CodeFirst 模式


目前 EF 是 .NET 平台下相当成熟的 ORM 框架,但是其最新发布的 6.x 版本依然不支持 SQLite 的 CodeFirst 模式,好在有大神已经在 Nuget 上发布的相应的 Package 来解决这个问题。笔者通过做一个小实验来验证一下。

问题描述

SQLite 本身不支持 CodeFirst 模式,当我们的数据模型因业务变化而需要修改的话,那对应的数据库表也要进行更改。这个时候,如果我们手动修改数据表的话就不太方便,因此我们需要想办法让其支持 CodeFirst 模式。笔者通过使用 SQLite.CodeFirst 来尝试解决上述问题。

解决方案

安装依赖包

首先我们创建一个控制台程序,安装如下 Package:

  • System.Data.SQLite
  • SQLite.CodeFirst

修改程序配置 App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>

  <!--新增结点-->
  <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" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
  </system.data>
  
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    
    <!--新增结点-->
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
    
  </entityFramework>

  <!--新增结点-->
  <connectionStrings>
    <add name="sampledb" connectionString="data source=.\sampledb.db" providerName="System.Data.SQLite" />
  </connectionStrings>
  
</configuration>

创建模型对象 Person.cs

[Table("person")]
public class Person
{
    [Column("id"), Key, Autoincrement]
    public int Id { get; set; }
    [Column("firstname")]
    public string FirstName { get; set; }
    [Column("lastname")]
    public string LastName { get; set; }
}

创建数据上下文 PersonDbContext.cs

public class PersonDbContext : DbContext
{
    public DbSet<Person> People { get; set; }

    /// <summary>
    /// 从配置文件读取链接字符串
    /// </summary>
    public PersonDbContext() :
        base("name = sampledb")
    {
        ConfigurationFunc();
    }

    /// <summary>
    /// 代码指定数据库连接
    /// </summary>
    /// <param name="existingConnection"></param>
    /// <param name="contextOwnsConnection"></param>
    public PersonDbContext(DbConnection existingConnection, bool contextOwnsConnection) :
        base(existingConnection, contextOwnsConnection)
    {
        ConfigurationFunc();
    }

    private void ConfigurationFunc()
    {
        Configuration.LazyLoadingEnabled = true;
        Configuration.ProxyCreationEnabled = true;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var initializer = new SqliteDropCreateDatabaseWhenModelChanges<PersonDbContext>(modelBuilder);
        Database.SetInitializer(initializer);
    }
}

此时,当我们修改数据模型时,不需要执行任何 migration 操作就可以将 数据表映射到新的模型上。下面我们在主程序中调用一下:

主程序调用 Program.cs

class Program
{
    static void Main(string[] args)
    {
        var ptah = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sampledb.db");
        var connection = SQLiteProviderFactory.Instance.CreateConnection();
        connection.ConnectionString = $"Data Source={ptah}";

        //using (var context = new PersonDbContext())
        using (var context = new PersonDbContext(connection, false))
        {
            #region 预热:针对数据表较多的情况下建议执行下述操作

            var objectContext = ((IObjectContextAdapter)context).ObjectContext;
            var mappingColection = (StorageMappingItemCollection)objectContext.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace);
            mappingColection.GenerateViews(new List<EdmSchemaError>());

            #endregion

            #region 插入数据

            Console.WriteLine("插入数据:");
            context.People.Add(new Person { FirstName = "hippie", LastName = "zhou" });
            context.SaveChanges();

            #endregion

            #region 查找数据

            Console.WriteLine("查找数据:");
            foreach (var people in context.People)
            {
                Console.WriteLine($"{people.Id} - {people.FirstName} - {people.LastName}");
            }

            #endregion

            #region 更改数据

            Console.WriteLine("更改数据:");
            Person person = context.People.Where(p => p.Id == 1)?.FirstOrDefault();
            person.LastName = "Puth";
            context.SaveChanges();

            #endregion

            #region 删除数据

            Console.WriteLine("删除数据:");
            Person person = context.People.Where(p => p.Id == 1)?.FirstOrDefault();
            context.People.Remove(person);
            context.SaveChanges();

            #endregion

            Console.ReadKey();
        }
    }
}

注意事项

由于 SQLite.CodeFirst 的 Package 需要依赖 EntityFramework,以及为了能保证数据操作支持相关 LINQ 操作,在上述两个 Pacakge 安装的过程中,会同时安装其它依赖包,所以最终的项目依赖包如下所示:

  • EntityFramework
  • SQLite.CodeFirst
  • System.Data.SQLite
  • System.Data.SQLite.Core
  • System.Data.SQLite.EF6
  • System.Data.SQLite.Linq

此外,需要注意一下 System.Data.SQLiteSystem.Data.SQLite.Core 的区别:

System.Data.SQLite:The official SQLite database engine for both x86 and x64 along with the ADO.NET provider. This package includes support for LINQ and Entity Framework 6.

System.Data.SQLite.Core:The official SQLite database engine for both x86 and x64 along with the ADO.NET provider.

所以,为了能让 SQLite 支持 CodeFirst ,还是一件挺曲折的事情啊。

相关参考

posted @ 2018-08-04 23:45 hippieZhou 阅读(...) 评论(...) 编辑 收藏