你的位置:首页 > ASP.net教程

[ASP.net教程]EF6 Code First 系列 (四):SQLite的DropCreateDatabaseIfModelChanges


没什么好说的,能支持DropCreateDatabaseIfModelChanges和RowVersion的Sqlite谁都想要。EntityFramework7正在添加对Sqlite的支持,虽然EF7不知道猴年马月才能完成正式版,更不知道MySql等第三方提供程序会在什么时候跟进支持,但是EF7中的确出现了Sqlite的相关代码。Sqlite支持EF6的CodeFirst,只是不支持从实体生成数据库,估计有很多人因为这个原因放弃了使用它。现在SQLite.CodeFirst的简单实现可以让我们生成数据库,因此在等待EF7的可以预见的长时间等待中,我们可以使用SQLite.CodeFirst,毕竟我们只是开发的时候使用DropCreateDatabaseIfModelChanges,Release时不会使用更不用担心SQLite.CodeFirst的简单实现会带来什么问题。可以直接修改源码,也可以参考最后面通过反射实现自定义DropCreateDatabaseIfModelChanges的方式。

1.RowVersion的支持:

可以从我的上一篇:在MySql中使用和SqlServer一致的RowVersion并发控制中采用相同的策略即可。我已经测试过在Sqlite中的可行性。

1.首先是RowVersion的配置:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)    {      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();      modelBuilder.Configurations.AddFromAssembly(typeof(SqliteDbContext).Assembly);      modelBuilder.Properties()              .Where(o => o.Name == "RowVersion")              .Configure(o => o.IsConcurrencyToken()              .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None));      Database.SetInitializer(new SqliteDbInitializer(Database.Connection.ConnectionString, modelBuilder));    }

2.然后是SaveChanges的重写:

public override int SaveChanges()    {      this.ChangeTracker.DetectChanges();      var objectContext = ((IObjectContextAdapter)this).ObjectContext;      foreach (ObjectStateEntry entry in objectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Modified | EntityState.Added))      {        var v = entry.Entity as IRowVersion;        if (v != null)        {          v.RowVersion = System.Text.Encoding.UTF8.GetBytes(Guid.NewGuid().ToString());        }      }      return base.SaveChanges();    }

3.生成__MigrationHistory:

DropCreateDatabaseIfModelChanges则需要修改SQLite.CodeFirst的代码,SQLite.CodeFirst生成的数据库不包含__MigrationHistory信息,所以我们首先修改SqliteInitializerBase添加__MigrationHistory,__MigrationHistory表是通过HistoryRow实体的映射,我们直接在EF源代码中找到相关部分作为参考。修改SqliteInitializerBase的SqliteInitializerBase方法,配置HistoryRow实体的映射。

public const string DefaultTableName = "__MigrationHistory";    internal const int ContextKeyMaxLength = 300;    internal const int MigrationIdMaxLength = 150;    protected SqliteInitializerBase(string connectionString, DbModelBuilder modelBuilder)    {      DatabaseFilePath = SqliteConnectionStringParser.GetDataSource(connectionString);      ModelBuilder = modelBuilder;      // This convention will crash the SQLite Provider before "InitializeDatabase" gets called.      // See https://github.com/msallin/SQLiteCodeFirst/issues/7 for details.      modelBuilder.Conventions.Remove<TimestampAttributeConvention>();      modelBuilder.Entity<HistoryRow>().ToTable(DefaultTableName);      modelBuilder.Entity<HistoryRow>().HasKey(        h => new        {          h.MigrationId,          h.ContextKey        });      modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(MigrationIdMaxLength).IsRequired();      modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(ContextKeyMaxLength).IsRequired();      modelBuilder.Entity<HistoryRow>().Property(h => h.Model).IsRequired().IsMaxLength();      modelBuilder.Entity<HistoryRow>().Property(h => h.ProductVersion).HasMaxLength(32).IsRequired();    }

4.初始化__MigrationHistory:

继续修改InitializeDatabase方法,在创建数据库后,初始化__MigrationHistory的信息。虽然采用了HistoryRow,但初始化信息我们只简单的使用生成的SQL语句作为判定实体和配置是否改变的依据,因为后面的InitializeDatabase方法中也是我们自己来判定实体和配置是否改变。

public virtual void InitializeDatabase(TContext context)    {      var model = ModelBuilder.Build(context.Database.Connection);      using (var transaction = context.Database.BeginTransaction())      {        try        {          var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);          sqliteDatabaseCreator.Create();          /*start*/          context.Set<HistoryRow>().Add(            new HistoryRow            {              MigrationId = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffffff"),              ContextKey = context.GetType().FullName,              Model = System.Text.Encoding.UTF8.GetBytes(sqliteDatabaseCreator.GetSql().ToCharArray()),              ProductVersion = "6.1.2"            });          /*end*/          transaction.Commit();        }        catch (Exception)        {          transaction.Rollback();          throw;        }      }      using (var transaction = context.Database.BeginTransaction())      {        try        {          Seed(context);          context.SaveChanges();          transaction.Commit();        }        catch (Exception)        {          transaction.Rollback();          throw;        }      }    }

5.添加DropCreateDatabaseIfModelChanges支持

添加SqliteDropCreateDatabaseIfModelChanges类,在InitializeDatabase方法中判读实体和配置是否改变。需要注意的是,删除sqlite文件时,即使关闭Connection和调用GC.Collect()仍然在第一次无法删除文件,所以必须进行多次尝试。

public override void InitializeDatabase(TContext context)    {      bool dbExists = File.Exists(DatabaseFilePath);      if (dbExists)      {        var model = ModelBuilder.Build(context.Database.Connection);        var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);        var newSql = sqliteDatabaseCreator.GetSql();        var oldSql = "";        oldSql = System.Text.Encoding.UTF8.GetString(context.Set<System.Data.Entity.Migrations.History.HistoryRow>().AsNoTracking().FirstOrDefault().Model);        context.Database.Connection.Close();        GC.Collect();         if (oldSql == newSql)        {          return;        }        for (int i = 0; i < 10; i++)        {          try          {            File.Delete(DatabaseFilePath);            break;          }          catch (Exception)          {            System.Threading.Thread.Sleep(1);          }        }      }      base.InitializeDatabase(context);    }

 

核心的代码已经贴出来,SQLite.CodeFirst本身的实现就比较简易,我添加的代码也比较简陋,因此在代码上没什么参考价值,只有使用和实用价值。毕竟只是在Debug开发时才需要这些功能的支持,对Sqlite本身和EF的提供程序没有任何影响。到这里终于松了口气,我们现在可以使用:Sql Server(CE)、Sqlite和Mysql进行Code First开发,采用相同的实体定义和配置,并且采用相同的并发控制。非Sql Server(CE)的并发控制和Sqlite不支持从代码生成数据库这两点终于克服了。

6.不修改源代码,使用反射实现

修改源码是由于SQLite.CodeFirst的内部类无法调用,考虑到可以使用反射,于是有了下面不需要修改源码,通过反射实现直接自定义DropCreateDatabaseIfModelChanges的方式:

using SQLite.CodeFirst.Statement;using System;using System.Data.Entity;using System.Data.Entity.Core.Metadata.Edm;using System.Data.Entity.Infrastructure;using System.Data.Entity.Migrations.History;using System.Data.Entity.ModelConfiguration.Conventions;using System.IO;using System.Linq;using System.Reflection;namespace SQLite.CodeFirst{  public class SqliteDropCreateDatabaseIfModelChanges<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext  {    protected readonly DbModelBuilder ModelBuilder;    protected readonly string DatabaseFilePath;    public const string DefaultTableName = "__MigrationHistory";    private const string DataDirectoryToken = "|datadirectory|";    internal const int ContextKeyMaxLength = 300;    internal const int MigrationIdMaxLength = 150;    public SqliteDropCreateDatabaseIfModelChanges(string connectionString, DbModelBuilder modelBuilder)    {      DatabaseFilePath = ConnectionStringParse(connectionString);      ModelBuilder = modelBuilder;      // This convention will crash the SQLite Provider before "InitializeDatabase" gets called.      // See https://github.com/msallin/SQLiteCodeFirst/issues/7 for details.      modelBuilder.Conventions.Remove<TimestampAttributeConvention>();      ConfigMigrationHistory(modelBuilder);    }    private string ConnectionStringParse(string connectionString)    {      var path = connectionString.Trim(' ', ';').Split(';').FirstOrDefault(o => o.StartsWith("data source", StringComparison.OrdinalIgnoreCase)).Split('=').Last().Trim();      if (!path.StartsWith("|datadirectory|", StringComparison.OrdinalIgnoreCase))      {        return path;      }      string fullPath;      // find the replacement path      object rootFolderObject = AppDomain.CurrentDomain.GetData("DataDirectory");      string rootFolderPath = (rootFolderObject as string);      if (rootFolderObject != null && rootFolderPath == null)      {        throw new InvalidOperationException("The value stored in the AppDomains 'DataDirectory' variable has to be a string!");      }      if (string.IsNullOrEmpty(rootFolderPath))      {        rootFolderPath = AppDomain.CurrentDomain.BaseDirectory;      }      // We don't know if rootFolderpath ends with '\', and we don't know if the given name starts with onw      int fileNamePosition = DataDirectoryToken.Length;  // filename starts right after the '|datadirectory|' keyword      bool rootFolderEndsWith = (0 < rootFolderPath.Length) && rootFolderPath[rootFolderPath.Length - 1] == '\\';      bool fileNameStartsWith = (fileNamePosition < path.Length) && path[fileNamePosition] == '\\';      // replace |datadirectory| with root folder path      if (!rootFolderEndsWith && !fileNameStartsWith)      {        // need to insert '\'        fullPath = rootFolderPath + '\\' + path.Substring(fileNamePosition);      }      else if (rootFolderEndsWith && fileNameStartsWith)      {        // need to strip one out        fullPath = rootFolderPath + path.Substring(fileNamePosition + 1);      }      else      {        // simply concatenate the strings        fullPath = rootFolderPath + path.Substring(fileNamePosition);      }      return fullPath;    }    private void ConfigMigrationHistory(DbModelBuilder modelBuilder)    {      modelBuilder.Entity<HistoryRow>().ToTable(DefaultTableName);      modelBuilder.Entity<HistoryRow>().HasKey(        h => new        {          h.MigrationId,          h.ContextKey        });      modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(MigrationIdMaxLength).IsRequired();      modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(ContextKeyMaxLength).IsRequired();      modelBuilder.Entity<HistoryRow>().Property(h => h.Model).IsRequired().IsMaxLength();      modelBuilder.Entity<HistoryRow>().Property(h => h.ProductVersion).HasMaxLength(32).IsRequired();    }    public string GetSql(DbModel model)    {      Assembly asm = Assembly.GetAssembly(typeof(SqliteInitializerBase<>));      Type builderType = asm.GetType("SQLite.CodeFirst.Builder.CreateDatabaseStatementBuilder");      ConstructorInfo builderConstructor = builderType.GetConstructor(new Type[] { typeof(EdmModel) });      Object builder = builderConstructor.Invoke(new Object[] { model.StoreModel });      MethodInfo method = builderType.GetMethod("BuildStatement", BindingFlags.Instance | BindingFlags.InvokeMethod | BindingFlags.Public);      var statement = (IStatement)method.Invoke(builder, new Object[] { });      string sql = statement.CreateStatement();      return sql;    }    public void InitializeDatabase(TContext context)    {      var model = ModelBuilder.Build(context.Database.Connection);      var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);      var newSql = this.GetSql(model);      bool dbExists = File.Exists(DatabaseFilePath);      if (dbExists)      {        var oldSql = System.Text.Encoding.UTF8.GetString(context.Set<System.Data.Entity.Migrations.History.HistoryRow>().AsNoTracking().FirstOrDefault().Model);        context.Database.Connection.Close();        GC.Collect();        if (oldSql == newSql)        {          return;        }        for (int i = 0; i < 10; i++)        {          try          {            File.Delete(DatabaseFilePath);            break;          }          catch (Exception)          {            System.Threading.Thread.Sleep(1);          }        }      }      using (var transaction = context.Database.BeginTransaction())      {        try        {          sqliteDatabaseCreator.Create();          transaction.Commit();        }        catch (Exception)        {          transaction.Rollback();          throw;        }      }      using (var transaction = context.Database.BeginTransaction())      {        try        {          context.Set<HistoryRow>().Add(          new HistoryRow          {            MigrationId = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffffff"),            ContextKey = context.GetType().FullName,            Model = System.Text.Encoding.UTF8.GetBytes(newSql.ToCharArray()),            ProductVersion = "6.1.3"          });          Seed(context);          context.SaveChanges();          transaction.Commit();        }        catch (Exception)        {          transaction.Rollback();          throw;        }      }    }    protected virtual void Seed(TContext context)    {    }  }}

View Code

 

希望你不是找了好久才找到这个解决方案。