EF Core – Custom Migrations (高级篇)

前言

会写这篇是因为最近开始大量使用 SQL Server Trigger 来维护冗余 (也不清楚这路对不对).

EF Core migrations 没有支持 Trigger Github Issue, 能找到相关的 Laraue.EfCoreTriggers, 但 star 太少, 不敢用.

于是计划自己实现一个简单版本符合自己用就好.

更新 09-11-2022: EF Core 7.0 breaking changes 提醒:

EF7 以后, 所有使用 Trigger 的 Table 都需要 Config 声明哦.

 

主要参考:

Add support for managing Triggers with EF migration

Laraue.EfCoreTriggers

Custom Migrations Operations

How to customize migration generation in EF Core Code First?

Design-time DbContext Creation 

EF Core Add Migration Debugging  

MigrationsModelDiffer.cs

 

How EF Core Migrations Work?

要搞底层东西, 首先要摸清楚它怎么 work 的. 

首先是 build model, 数据库表结构

然后运行 migrations command

dotnet ef migrations add init

Ef Core Design 会创建出 migrations file (我们熟悉的 Up, Down)

如果想做一些调整, 可以直接修改这个 file. 比如 migrationBuilder.Sql()

然后运行 update database command

dotnet ef database update

Ef Core Design 会依据不同的 SQL Provider 生产出对应的 SQL Command 去 update database.

 

 

The Official Way

在了解 migrations 的流程后, 下一步就是要知道如何扩展它.

Custom Migrations Operations

这一篇就教了如果去写自己的 Operations 来扩展 Migrations.

首先创建一个 migrationBuilder 扩展方法, 里头调用 migrationBuilder.Sql("SQL command here...");

然后在 migration file (就是那个 Up Down 的 class) 里调用

呃...这不就是直接修改 migrations file, 写上 SQL Command 吗... (也算扩展 ?)

文章里还说到, 要支持多个 SQL Provider 所以必须写多种 SQL Command.

除了上面这种直接的方法, 文章也给出另一种没有那么直接的方式

首先做一个 MigrationOperation

然后不直接调用 SQL Command, 只把 operation add 进去 builder

最后通过来扩展 SqlServerMigrationsSqlGenerator 来实现 operations to SQL command.

internal class MyMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public MyMigrationsSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        IRelationalAnnotationProvider migrationsAnnotations)
        : base(dependencies, migrationsAnnotations)
    {
    }

    protected override void Generate(
        MigrationOperation operation,
        IModel model,
        MigrationCommandListBuilder builder)
    {
        if (operation is CreateUserOperation createUserOperation)
        {
            Generate(createUserOperation, builder);
        }
        else
        {
            base.Generate(operation, model, builder);
        }
    }

    private void Generate(
        CreateUserOperation operation,
        MigrationCommandListBuilder builder)
    {
        var sqlHelper = Dependencies.SqlGenerationHelper;
        var stringMapping = Dependencies.TypeMappingSource.FindMapping(typeof(string));

        builder
            .Append("CREATE USER ")
            .Append(sqlHelper.DelimitIdentifier(operation.Name))
            .Append(" WITH PASSWORD = ")
            .Append(stringMapping.GenerateSqlLiteral(operation.Password))
            .AppendLine(sqlHelper.StatementTerminator)
            .EndCommand();
    }
}

记得要把原本的 SqlServerMigrationsSqlGenerator 替换掉哦

 

 

实现思路

Official way 并不能解决我们的问题, 我们需要从 modelBuilder 阶段开始去写 Trigger. 然后 generate 出正确的 migration file, 而不是直接修改 migration file.

至于 migration file 里头是直接写 SQL Command 或者使用 operation 在交由 SqlServerMigrationsSqlGenerator 去实现 SQL command, 这区别不大.

在参考了 Laraue.EfCoreTriggers 源码后, 发现它的扩展方式是 IMigrationsModelDiffer.

IMigrationsModelDiffer 是 modelBuilder to migration file 过程中会用到的一个功能. 它会判断之前和之后的区别, 来生产 migration file.

通过扩展它就可以分析 modelBuilder 的结构, 然后生产 migration file.

modelBuilder 有一个扩展的方式是 AddAnnotation, 可以任意加入 key-value

然后在 IMigrationsModelDiffer 里头通过识别加入的 Annotation, 就可以修改 migration file, migration file 能扩展的地方是 .Sql()

以上就是 Laraue.EfCoreTriggers 的扩展方式了.

还有一篇 How to customize migration generation in EF Core Code First? 也提到了如何扩展 EF Core Migrations.

答题人正是 MySQL provider for EF Core 的 Lead developer.

 

5 个步骤, 

1. 添加自己的 annotation. (上面讲过了. 没问题)

2. 自定义 MIgrationOperation (Official way 讲过了, 没问题)

3. IMigrationsModelDiffer (和 Laraue.EfCoreTriggers 一样, 没问题, 提醒: 这个是 internal class 哦, EF Core 并没有 public 让我们扩展的意思, 但也没有其它的 way 了)

4. ICSharpMigrationOperationGenerator 

这个是新东西, 它就是负责把 modelBuilder 做成 migration file 的幕后黑手. 负责 generate C# code, 所以扩展它的话, 几乎可以完全控制 migration file 里的所有代码了.

5. SqlServerMigrationsSqlGenerator (Official way 讲过了, 没问题)

 

小总结

到这里我们搞清楚了几个重要的东西.

modelBuilder 负责描述数据库结构, 它可以通过 AddAnnotation key-value 来添加自定义的表述信息. (所以它负责表达而已)

ICSharpMigrationOperationGenerator 负责把 modelBuilder 解析, 然后生产 C# migration file. 间中还会用到 IMigrationsModelDiffer 来对比之前的 model 和之后的 model 哪里不同了.

migration file 里的 C# code 主要就是做一堆的 operation, 我们也可以自定义自己的 C# code 去做 operation (Official way)

最后 migration file 做出的 operations 被 SqlServerMigrationsSqlGenerator (或者其它 Provider 的 generator) 解析编译成最终的 SQL Command. 

 

 

逐个测试

我们先过一圈, 感受一下, 最后才决定如何实现 Trigger.

Custom Annotation

modelBuilder.Entity<Color>().HasAnnotation("Trigger", "SQL Command");

IMigrationsModelDiffer

#pragma warning disable EF1001 // Internal EF Core API usage.
public class MyMigrationsModelDiffer : Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer
{
    public MyMigrationsModelDiffer(
      IRelationalTypeMappingSource typeMappingSource,
      IMigrationsAnnotationProvider migrationsAnnotationProvider,
      IRowIdentityMapFactory rowIdentityMapFactory,
      CommandBatchPreparerDependencies commandBatchPreparerDependencies) : base(typeMappingSource, migrationsAnnotationProvider, rowIdentityMapFactory, commandBatchPreparerDependencies)
    { }

    public override IReadOnlyList<MigrationOperation> GetDifferences(IRelationalModel? source, IRelationalModel? target)
    {
        var x = source?.GetAnnotations();
        var y = target?.GetAnnotations();
        return base.GetDifferences(source, target);
    }
}
#pragma warning restore EF1001 // Internal EF Core API usage.

 

还要 ReplaceService 哦

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer("Server=192.168.1.152;Database=TestEFCore;Trusted_Connection=True;MultipleActiveResultSets=true")
    .ReplaceService<IMigrationsModelDiffer, MyMigrationsModelDiffer>();
});

ICSharpMigrationOperationGenerator

public class MyCSharpMigrationOperationGenerator : CSharpMigrationOperationGenerator
{
    public MyCSharpMigrationOperationGenerator(CSharpMigrationOperationGeneratorDependencies dependencies) : base(dependencies)
    {
        Console.Write("hello world");
    }
    protected override void Generate(CreateTableOperation operation, IndentedStringBuilder builder)
    {
        base.Generate(operation, builder);
        var www = builder.ToString();
    }
}

这个 C# generator 是在 Design Time 时做的. 它不是用 ReplaceService 而是通过依赖注入去 override 的.

public class MyDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
        => services.AddSingleton<ICSharpMigrationOperationGenerator, MyCSharpMigrationOperationGenerator>();
}

顺便说一下,  如果是做测试 Console App 的话. Design Time 要另外写 Factory, 参考: Design-time DbContext Creation

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        Debugger.Launch();
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder
            .UseSqlServer("Server=192.168.1.152;Database=TestEFCore;Trusted_Connection=True;MultipleActiveResultSets=true")
            .ReplaceService<IMigrationsModelDiffer, MyMigrationsModelDiffer>();
        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

注: Debugger.Launch(); 是为了调试用的. 参考: EF Core Add Migration Debugging,

这特调试不是一般的 F5 启动那种, 而是要调试 ModelDiffer 这种 design time 的代码, 通常是 cmd dotnet ef migrations add WhateverName 启动的.

ISqlServerMigrationsSqlGenerator

public class MyMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public MyMigrationsSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        IRelationalAnnotationProvider migrationsAnnotations)
        : base(dependencies, migrationsAnnotations)
    {
    }

    protected override void Generate(MigrationOperation operation, IModel? model, MigrationCommandListBuilder builder)
    {
        base.Generate(operation, model, builder);
    }
}

这个也需要 ReplaceService

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer("Server=192.168.1.152;Database=TestEFCore;Trusted_Connection=True;MultipleActiveResultSets=true")
    .ReplaceService<IMigrationsSqlGenerator, MyMigrationsSqlGenerator>()
    .ReplaceService<IMigrationsModelDiffer, MyMigrationsModelDiffer>();
});

注: 所有 ReplaceService 只能一次哦, 之前在 Library use EF 的时候有讲过, 如果是封装 Library 的话要注意了.

 

我怎么做?

回到我最初的目的, 想让 migration 来维护 "我的 Trigger". 就目前看，一个非常完整的方案应该是 

定义好 modelBuilder 的扩展. 自定义 C# generator 编辑并调用自定义的 opration 函数, 然后由不同的 SQL Provider 去解析 operation 生成 SQL command.

所以需要 Custom Annotation, IMigrationsModelDiffer, ICSharpMigrationOperationGenerator, ISqlServerMigrationsSqlGenerator, 全部用上.

很显然我并不会这样折腾自己...所以最简单的方式就是像 Laraue.EfCoreTriggers 那样, 只要 add custom annotation, 然后扩展 IMigrationsModelDiffer 里头调用 build-in 的 SQL operation 函数 

也就是 .Sql() 啦, 这样就够我自己用了. 主要参考: MigrationsModelDiffer.cs

 

实战

关键就在 IMigrationsModelDiffer 如何解析自定义的 annotation.

source 是 previous, target 是 current. 通过各做对比就可以创建出不同的 SqlOperation, 比如 CREATE TRIGGER, DROP TRIGGER 等等.

#pragma warning disable EF1001 // Internal EF Core API usage.
public class MyMigrationsModelDiffer : Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer
{
    public MyMigrationsModelDiffer(
      IRelationalTypeMappingSource typeMappingSource,
      IMigrationsAnnotationProvider migrationsAnnotationProvider,
      IRowIdentityMapFactory rowIdentityMapFactory,
      CommandBatchPreparerDependencies commandBatchPreparerDependencies) : base(typeMappingSource, migrationsAnnotationProvider, rowIdentityMapFactory, commandBatchPreparerDependencies)
    { }

    public override IReadOnlyList<MigrationOperation> GetDifferences(IRelationalModel? source, IRelationalModel? target)
    {
        var sourceModel = source?.Model;
        var targetModel = target?.Model;
        var oldEntityTypeNames = sourceModel?.GetEntityTypes().Select(x => x.Name) ?? Enumerable.Empty<string>();
        var newEntityTypeNames = targetModel?.GetEntityTypes().Select(x => x.Name) ?? Enumerable.Empty<string>();
        var commonEntityTypeNames = oldEntityTypeNames.Intersect(newEntityTypeNames);
        if (targetModel != null)
        {
            // modelBuilder.Entity<Product>().Metadata.Model.AddAnnotation("n1", "n1");
            var annotations = targetModel.GetAnnotations().Select(a => a.Name);

            // modelBuilder.Entity<Product>().HasAnnotation("n2", "n2");
            var e = targetModel.GetEntityTypes().Single(e => e.Name == "TestEFCore.Product").GetAnnotations().Select(e => e.Name).ToList();

            // modelBuilder.Entity<Product>().Property(e => e.Name).HasAnnotation("n3", "n3");
            var p = targetModel.GetEntityTypes().Single(e => e.Name == "TestEFCore.Product").GetProperty(nameof(Product.Name)).GetAnnotations().Select(e => e.Name).ToList();

            // modelBuilder.Entity<Product>().HasMany(e => e.Colors).WithOne().HasAnnotation("n6", "n6").HasForeignKey(e => e.ProductId).HasAnnotation("n5", "n5")
            // .OnDelete(DeleteBehavior.Cascade).HasAnnotation("n4", "n4");
            var f = targetModel.GetEntityTypes().Single(e => e.Name == "TestEFCore.Product").GetReferencingForeignKeys().Select(k => k.GetAnnotations().Select(e => e.Name)).ToList(); // n4, n5, n6
        }

        IReadOnlyList<MigrationOperation> migrationOperations = base.GetDifferences(source, target);
        var finalMigrationOperations = migrationOperations.Concat(new List<MigrationOperation>
        {
            new SqlOperation
            {
                // 要支持 multiple provider 的话参考: Laraue.EfCoreTriggers, 它是在 AddAnnotation 阶段就已经 build 好 SQL command 了.
                Sql = "SQL command here ..."
            }
        }).ToList();
        return finalMigrationOperations;
    }
}
#pragma warning restore EF1001 // Internal EF Core API usage.

好了, 关键都有了，剩下的就各自发挥吧. 我就不写下去了.

 

目前遇到的局限

想在 IMigrationsModelDiffer | ISqlServerMigrationsSqlGenerator 注入 Service 是做不到的. 

因为 EF Core 有 internal 的 provider for 这 2 个 service, 外部是扩展不了的. 或者至少目前是没有 right way 去做到的. 

EF cannot resolve custom IMigrationsSqlGenerator