接上一篇 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