• EntityFrameworkCore数据迁移(二)


      接上一篇 EntityFrameworkCore数据迁移(一)

      其实上一篇该写的都已经写完了,但是后来又想到两个问题,想了想还是也写出来吧

      

      问题一

      上一篇介绍的迁移过程,都是通过在程序包管理器控制台使用Add-Migration和Update-Database命令执行的,那问题来了,如果是在开发过程中,我可以在VS中这么做,但是在线上环境,我们总不至于在线上服务器也安装个VS吧,那怎么进行迁移呢?

      这个时候就需要我们自己用代码去执行生成的迁移文件了。

      接上篇,我们将实体模型和数据迁移部分分到两个项目中,而EFCoreDemo.EntityFrameworkCore.Host项目是控制台项目,正好可以去用代码去执行数据迁移:

      我们修改Program:  

        class Program
        {
            static void Main(string[] args)
            {
                #region 迁移
                using (var db = new DemoMigrationsDbContextFactory().CreateDbContext(args))
                {
                    var array = db.Database.GetPendingMigrations();
                    int count = array.Count();
                    if (count == 0)
                    {
                        Console.WriteLine("nothing to migrate,current migrations is up-to-date...");
                    }
                    else
                    {
                        Console.WriteLine($"Pending Migrations:{count}");
                        foreach (var a in array)
                        {
                            Console.WriteLine($"Migrations:{a}");
                        }
    
                        Console.WriteLine("Do you want to continue?(Y/N)");
                        if (Console.ReadLine().Trim().ToLower() == "y")
                        {
                            Console.WriteLine("Migrating...");
    
                            try
                            {
                                //执行迁移
                                db.Database.Migrate();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine(e);
                            }
                        }
                        Console.WriteLine("Completed!!!");
                    }
                }
                #endregion
    
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
        }

      注意,上面的程序仅仅只是执行迁移文件,并不是生成迁移,所以开发过程中我们还是需要使用Add-Migration命令生成迁移。

      另外,还有一个问题需要注意,上一篇中,迁移过程中,我们的数据库连接时写死在DemoMigrationsDbContextFactory类中的,线上库和开发测试库肯定不是同一个库,那怎么去区分?

      一个想法是将数据库连接写到配置文件中去,这里只是去读取!而这个配置文件可以是json文件,也可以是xml文件,比如是json文件:

      我们在EFCoreDemo.EntityFrameworkCore.Host中创建一个json文件,比如就叫appsettings.json,然后修改文件属性的输出配置为【如果较新则复制】

      

        然后在appsettings.json中添加内容:  

      {
        "ConnectionString": {
          "Default": "Server=192.168.209.128;Port=3306;Database=demodb;Uid=root;Pwd=123456"
        }
      }

      然后修改DemoMigrationsDbContextFactory:  

        public class DemoMigrationsDbContextFactory : IDesignTimeDbContextFactory<MigrationDbContext>
        {
            public MigrationDbContext CreateDbContext(string[] args)
            {
                var configuration = BuildConfiguration();
    
                var builder = new DbContextOptionsBuilder<MigrationDbContext>()
                    .UseMySql(configuration["ConnectionString:Default"]);
    
                return new MigrationDbContext(builder.Options);
            }
    
            private static IConfigurationRoot BuildConfiguration()
            {
                var builder = new ConfigurationBuilder()
                    .SetBasePath(Directory.GetCurrentDirectory())
                    .AddJsonFile("appsettings.json", optional: false);
    
                return builder.Build();
            }
        }

      最后EFCoreDemo.EntityFrameworkCore.Host的项目结构如下:

      

        这样,只需要保证我们上线和开发过程中的这个文件配置不一样就可以了

      测试一下,先清空数据库,因为先前已经生成了迁移文件,所以可以直接运行,运行后提示有两个迁移可以执行,输入y,回车即可:

      

       

      问题二

      上一篇中,我创建了两次迁移,第二迁移增加列,修改了列属性,还添加了外键约束,但是我忽略了索引。

      比如活动记录表中,一般的,一个用户参加活动只会有一条活动记录,因此活动Id和用户Id应该是可以作为一个唯一值索引的,于是乎我修改了ActivityRecordEntityTypeConfiguration:  

        public class ActivityRecordEntityTypeConfiguration : BaseEntityTypeConfiguration<ActivityRecord>
        {
            /// <summary>
            /// 配置实体类型
            /// </summary>
            /// <param name="builder"></param>
            public override void Configure(EntityTypeBuilder<ActivityRecord> builder)
            {
                base.Configure(builder);
    
                builder.HasIndex(p => new { p.ActivityId, p.AccountId }).IsUnique(true);
                builder.HasOne(p => p.Activity).WithMany().HasForeignKey(p => p.ActivityId);
                builder.HasOne(p => p.Account).WithMany().HasForeignKey(p => p.AccountId);
            }
        }

      然后执行Add-Migration和Update-Database,出乎我意料的是竟然报错了!

      

        看了一下,这里抛出异常时:Cannot drop index 'IX_demo_activityrecord_ActivityId': needed in a foreign key constraint

      这里是说,删除索引时,因为存在外键约束,所以需要先删除外键才能再删除索引!

      回想上一篇,我在再次迁移的时候,为ActivityRecord增加了外键约束,打开对应的迁移文件alter_20200727,很容易发现

        public partial class alter_20200727 : Migration
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                //以上省略...
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_AccountId",
                    table: "demo_activityrecord",
                    column: "AccountId");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId");
    
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_account_AccountId",
                    table: "demo_activityrecord",
                    column: "AccountId",
                    principalTable: "demo_account",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
    
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_activity_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId",
                    principalTable: "demo_activity",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            }
    
            protected override void Down(MigrationBuilder migrationBuilder)
            {
                //省略...
            }
        }

      原来,mysql创建外键约束的时候,默认会将约束列设置成索引(非唯一值索引),用以提高搜素速度,而这个索引是和外键绑定的!

      打开我们我们新建的迁移文件alter_20200728:  

        public partial class alter_20200728 : Migration
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.DropIndex(
                    name: "IX_demo_activityrecord_ActivityId",
                    table: "demo_activityrecord");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord",
                    columns: new[] { "ActivityId", "AccountId" },
                    unique: true);
            }
    
            protected override void Down(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.DropIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId");
            }
        }

      其中,Up方法中是先删除索引在创建新的索引,这就是迁移发生异常的原因!

      假如之前的迁移已经全部更新到线上,但是现在迁移又报错了,那怎办?

      我们的第一反应是,先到数据库自行把相关的外键约束和索引删除,在执行迁移,执行完成之后再去数据库把外键约束添加上!

      千万不到这么做!

      宁可以后开发放弃使用迁移也不要这么做,原因有二:

      第一、如果是要更新到线上,而删除约束,再执行迁移,这之间有时间差,如果有异常数据进来,可能导致约束加不回去了。如果这样,还不如直接全部使用SQL脚本执行!

      第二、本来我们只需要一个空的数据库,然后执行一次迁移就能初始化完成,然后系统就可以在这个库上运作起来,而手动改数据库后,可能导致迁移不能一次执行

      遇到这种问题,个人比较喜欢去修改迁移文件,比如这个问题,既然是删除索引时报错,那就可以把删除索引的语句注释掉:  

        public partial class alter_20200728 : Migration
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                //migrationBuilder.DropIndex(
                //    name: "IX_demo_activityrecord_ActivityId",
                //    table: "demo_activityrecord");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord",
                    columns: new[] { "ActivityId", "AccountId" },
                    unique: true);
            }
    
            protected override void Down(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.DropIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord");
    
                //migrationBuilder.CreateIndex(
                //    name: "IX_demo_activityrecord_ActivityId",
                //    table: "demo_activityrecord",
                //    column: "ActivityId");
            }
        }

      这里需要注意的是,Up和Down两个方法建议同步修改!

      修改后执行再执行Update-Database就通过了:

      

        这里只是一种处理方式,当然还可以根据提示,先删除外键约束,再删除索引,接着加上外键约束:  

        public partial class alter_20200728 : Migration
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                //删除外键约束
                migrationBuilder.DropForeignKey(
                    name: "FK_demo_activityrecord_demo_account_AccountId",
                    table: "demo_activityrecord"
                    );
    
                //删除外键约束
                migrationBuilder.DropForeignKey(
                    name: "FK_demo_activityrecord_demo_activity_ActivityId",
                    table: "demo_activityrecord"
                    );
    
                migrationBuilder.DropIndex(
                    name: "IX_demo_activityrecord_ActivityId",
                    table: "demo_activityrecord");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord",
                    columns: new[] { "ActivityId", "AccountId" },
                    unique: true);
    
                //添加外键约束
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_account_AccountId",
                    table: "demo_activityrecord",
                    column: "AccountId",
                    principalTable: "demo_account",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
    
                //添加外键约束
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_activity_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId",
                    principalTable: "demo_activity",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            }
    
            protected override void Down(MigrationBuilder migrationBuilder)
            {
                //删除外键约束
                migrationBuilder.DropForeignKey(
                    name: "FK_demo_activityrecord_demo_account_AccountId",
                    table: "demo_activityrecord"
                    );
    
                //删除外键约束
                migrationBuilder.DropForeignKey(
                    name: "FK_demo_activityrecord_demo_activity_ActivityId",
                    table: "demo_activityrecord"
                    );
    
                migrationBuilder.DropIndex(
                    name: "IX_demo_activityrecord_ActivityId_AccountId",
                    table: "demo_activityrecord");
    
                migrationBuilder.CreateIndex(
                    name: "IX_demo_activityrecord_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId");
    
                //添加外键约束
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_account_AccountId",
                    table: "demo_activityrecord",
                    column: "AccountId",
                    principalTable: "demo_account",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
    
                //添加外键约束
                migrationBuilder.AddForeignKey(
                    name: "FK_demo_activityrecord_demo_activity_ActivityId",
                    table: "demo_activityrecord",
                    column: "ActivityId",
                    principalTable: "demo_activity",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            }
        }

      

      总之,使用数据迁移时,要注意以下几点:

      1、可以手动去改数据库中数据,但是尽量不要手动去改数据库结构

      2、如果迁移过程抛出异常,应先通过修改迁移文件来解决问题

      3、对于多次生成的迁移,尽量确保对一个空库,只需要指定一个Update-Database将所有迁移更新至数据库,系统就能在这个库中运作起来

      

      最后,补上测试项目的代码: https://pan.baidu.com/s/1D1chtNWDEA2pZQqtvrqAGg 提取码: epwm

  • 相关阅读:
    CENTOS安装部署zabbix
    分解XML方法
    Git使用摘要
    POJ 1274 The Perfect Stall 水二分匹配
    [leetcode]Pascal&#39;s Triangle II
    swift学习笔记(六)析关闭过程和使用分配给属性的默认值
    Qt学习一门:直接使用QT具
    mybatis13 resultMap
    mybatis12 Usermapper.xml
    mybatis11 sqlMapConfig.xml文件说明
  • 原文地址:https://www.cnblogs.com/shanfeng1000/p/13387180.html
Copyright © 2020-2023  润新知