Entity Framework 6 Code First series: Enables Sqlite.codefirst to support Dropcreatedatabaseifmodelchanges and rowversion

Nothing to say, can support dropcreatedatabaseifmodelchanges and RowVersion SQLite who all want. EntityFramework7 is adding support for SQLite, although EF7 do not know se years to complete the official version, but also do not know when the third-party providers such as MySQL will follow up support, but EF7 did appear in the relevant code of SQLite. SQLite supports EF6 's codefirst, but does not support generating databases from entities, and it is estimated that many people have given up using it for this reason. Now the simple implementation of Sqlite.codefirst allows us to generate the database, so we can use the Sqlite.codefirst for a long time to wait for EF7, after all, we just use Dropcreatedatabaseifmodelchang when we develop it. Es,release doesn't use a simple implementation that doesn't have to worry about Sqlite.codefirst.

Support for 1.RowVersion:

From my previous article: Use the same strategy in MySQL with rowversion concurrency control consistent with SQL Server. I have tested the feasibility in SQLite.

The first is the configuration of the 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));
        }

Then there is the rewrite of 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();
        }

2.DropCreateDatabaseIfModelChanges Support

(1) Generate __migrationhistory:

Dropcreatedatabaseifmodelchanges need to modify Sqlite.codefirst code, Sqlite.codefirst generated database does not contain __migrationhistory information, So we first modify the Sqliteinitializerbase add __migrationhistory,__migrationhistory table is through the Historyrow entity mapping, we directly in the EF source code to find the relevant section as a reference. Modify the Sqliteinitializerbase Sqliteinitializerbase method to configure mappings for Historyrow entities.

 
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();
        }

(2) Initialize __migrationhistory:

Continue modifying the Initializedatabase method to initialize the __migrationhistory information after the database is created. Although Historyrow is used, the initialization information simply uses the generated SQL statement as the basis for determining whether the entity and configuration are changed, since the latter Initializedatabase method is also our own to determine whether the entity and configuration have changed.

 
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;
                }
            }
        }

(3) Add dropcreatedatabaseifmodelchanges support

Add the Sqlitedropcreatedatabaseifmodelchanges class to interpret whether entities and configurations change in the Initializedatabase method. It is important to note that when you delete an SQLite file, you must make multiple attempts, even if you close connection and call Gc.collect () and still cannot delete the file for the first time.

 
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);
        }

The core code has been posted, the implementation of Sqlite.codefirst itself is relatively simple, I added the code is relatively simple, so there is no reference value in code, only the use and practical value. After all, the support for these features is only required for debug development and has no effect on SQLite itself and the EF provider. It's finally a relief, we can now use: SQL Server (CE), SQLite and MySQL for code first development, with the same entity definitions and configurations, and with the same concurrency control. Non-SQL Server (CE) concurrency control and SQLite do not support the generation of databases from code these two points have finally been overcome.

I hope you didn't find this solution for a long time.

Entity Framework 6 Code First series: Enables Sqlite.codefirst to support Dropcreatedatabaseifmodelchanges and rowversion