'How to customize migration generation in EF Core Code First?

There is a special base table type in my DbContext. And when inherited from it I need to generate an additional "SQL" migration operation to create a specific trigger for it. It makes sure table structure is consistent by checking overlapped ranges. Since there are no overlapping indexes or check constraints in SQL Server I have to use triggers (using functions in check constraints leads to same problems with migrations along with cluttering functions "namespace" in SQL).

Since I haven't found any way to create triggers during OnModelCreating I thought of altering generated migrations. But how to do that?

Tried using SqlServerMigrationsSqlGenerator and SqlServerMigrationsAnnotationProvider, but as their name suggests they are used only on a final stage, during generation of SQL commands. This makes them a bit "hidden" from sight when using migrations. Hard to customize when needed and maintain afterwards.

Thought about using CSharpMigrationOperationGenerator which seems to be perfect for my needs. But there is a problem - I can't access this class. Nor it's namespace.

According to source this class resides in Microsoft.EntityFrameworkCore.Migrations.Design namespace and is public. And in order to access it a Microsoft.EntityFrameworkCore.Design package has to be installed.

But it doesn't work.

What am I missing here? How to access and inherit this class? Or perhaps there is a much better and proper way to create triggers automatically during migrations for specific tables?

c++sharpsql-serverasp.net-coreentity-framework-coreentity-framework-migrations


Solution 1:[1]

How to provide your own ICSharpMigrationOperationGenerator implementation

Thought about using CSharpMigrationOperationGenerator which seems to be perfect for my needs. But there is a problem - I can't access this class. Nor it's namespace.

According to source this class resides in Microsoft.EntityFrameworkCore.Migrations.Design namespace and is public. And in order to access it a Microsoft.EntityFrameworkCore.Design package has to be installed.

But it doesn't work.

What am I missing here? How to access and inherit this class?

Let's assume you are calling the following CLI command to add a new migration at design time:

dotnet ef migrations add "SomeMigration"

Here is a fully working sample console program, that will use a custom ICSharpMigrationOperationGenerator implementation called MyCSharpMigrationOperationGenerator, inherited from CSharpMigrationOperationGenerator:

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Migrations.Design;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class MyCSharpMigrationOperationGenerator : CSharpMigrationOperationGenerator
    {
        public MyCSharpMigrationOperationGenerator(CSharpMigrationOperationGeneratorDependencies dependencies)
            : base(dependencies)
        {
        }

        protected override void Generate(CreateTableOperation operation, IndentedStringBuilder builder)
        {
            Console.WriteLine("\r\n\r\n---\r\nMyCSharpMigrationOperationGenerator was used\r\n---\r\n");
            base.Generate(operation, builder);
        }
    }
    
    public class MyDesignTimeServices : IDesignTimeServices
    {
        public void ConfigureDesignTimeServices(IServiceCollection services)
            => services.AddSingleton<ICSharpMigrationOperationGenerator, MyCSharpMigrationOperationGenerator>();
    }
    
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }
    
    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63575132")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    internal static class Program
    {
        private static void Main()
        {
        }
    }
}

The MyCSharpMigrationOperationGenerator class outputs the following lines for every added table as prove that it was called:

---
MyCSharpMigrationOperationGenerator was used
---

As @KasbolatKumakhov pointed out in his comment, it should also be mentinued that the way for referencing Microsoft.EntityFrameworkCore.Design has been changed from 2.2. to 3.0:

Starting with EF Core 3.0, it is a DevelopmentDependency package. This means that the dependency won't flow transitively into other projects, and that you can no longer, by default, reference its assembly. [...] If you need to reference this package to override EF Core's design-time behavior, then you can update PackageReference item metadata in your project.

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.0.0">
  <PrivateAssets>all</PrivateAssets>
  <!-- Remove IncludeAssets to allow compiling against the assembly -->
  <!--<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>-->
</PackageReference>

How to properly implement an additional MigrationOperation (e.g. for trigger creation)

Since I haven't found any way to create triggers during OnModelCreating I thought of altering generated migrations. But how to do that?

To do this properly, you would need to do the following:

  • Add your own annotation to the tables in question (e.g. MyPrefix:Trigger)
  • Implement your own MigrationOperation (e.g. CreateTriggerMigrationOperation)
  • Provide your own IMigrationsModelDiffer implementation (derived from MigrationsModelDiffer; this is internal) that returns your own MigrationOperation
  • Provide your own ICSharpMigrationOperationGenerator implementation (derived from CSharpMigrationOperationGenerator), that then generates the C# code for your own MigrationOperation
  • Provide your own IMigrationsSqlGenerator implementation (derived from SqlServerMigrationsSqlGenerator) that then handles translating your own MigrationOperation to SQL

Solution 2:[2]

It's not exactly what you asked for, but it does the similar job with low cost and it may come handy for someone.

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;

public static class MigrationBuilderExtensions
{
    public static void ConfigForOracle(this MigrationBuilder migrationBuilder)
    {
        //For each table registered in the builder, let's create a sequence and a trigger
        foreach (CreateTableOperation createTableOperation in migrationBuilder.Operations.ToArray().OfType<CreateTableOperation>())
        {
            string tableName = createTableOperation.Name;
            string primaryKey = createTableOperation.PrimaryKey.Columns[0];
            migrationBuilder.CreateSequence<int>(name: $"SQ_{tableName}", schema: createTableOperation.Schema);
            migrationBuilder.Sql($@"CREATE OR REPLACE TRIGGER ""TR_{tableName}""
                                    BEFORE INSERT ON ""{tableName}""
                                    FOR EACH ROW
                                    WHEN (new.""{primaryKey}"" IS NULL)
                                    BEGIN
                                        SELECT ""SQ_{tableName}"".NEXTVAL
                                        INTO   :new.""{primaryKey}""
                                        FROM   dual;
                                    END;");
        }
    }
}

You can do whatever you want in the extension method and then call it at the end of the Migration.Up() method. I use it to create sequences and triggers for Oracle 11g tables for identifier increment.

Solution 3:[3]

Open your migration file and make changes to your Up method.

Then apply the migration using Update-Database from package manager console.

Something like this:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("Some custom SQL statement");
        migrationBuilder.CreateTable(
            name: "Authors",
            columns: table => new
            {
                AuthorId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                FirstName = table.Column<string>(nullable: true),
                LastName = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Authors", x => x.AuthorId);
            });
    }
}

Solution 4:[4]

I think it is not intended to modify the ef core csharp code generation. But to generate custom migration statements (in my case triggers) i do following (shortened to relevant) using SqlOperation.

Implement a ModelDiffer

public class MyMigrationsModelDiffer : MigrationsModelDiffer {

  public MyMigrationsModelDiffer(IRelationalTypeMappingSource typeMappingSource,
    IMigrationsAnnotationProvider migrationsAnnotations,
    IChangeDetector changeDetector,
    IUpdateAdapterFactory updateAdapterFactory,
    CommandBatchPreparerDependencies commandBatchPreparerDependencies)
    : base(typeMappingSource, migrationsAnnotations, changeDetector, updateAdapterFactory, commandBatchPreparerDependencies) { }

  protected override IEnumerable<MigrationOperation> Diff(IModel source, IModel target, DiffContext diffContext) {
    return base.Diff(source, target, diffContext).Concat(GetTriggerTriggerDifferences(source, target));
  }

  public override Boolean HasDifferences(IModel source, IModel target) {
    return base.HasDifferences(source, target) || HasTriggerAnnotationDifferences(source, target);
  }

  public IEnumerable<MigrationOperation> GetTriggerTriggerDifferences(IModel source, IModel target) {
    if (source == null || target == null) {
      return new new List<MigrationOperation>(0);
    }

    Dictionary<String, IAnnotation> triggerAnnotationPerEntity = new Dictionary<String, IAnnotation>();
    foreach (var entityType in source.GetEntityTypes()) {
      triggerAnnotationPerEntity[entityType.Name] = GetTableAnnotation(entityType);
    }
    var operations = new List<MigrationOperation>();
    foreach (var entityType in target.GetEntityTypes()) {
      triggerAnnotationPerEntity.TryGetValue(entityType.Name, out IAnnotation sourceTriggerTable);
      IAnnotation targetTriggerTable = GetTableAnnotation(entityType);

      if (targetTriggerTable?.Value == sourceTriggerTable?.Value) {
        continue;
      }

      Boolean isCreate = targetTriggerTable != null;
      String tableName = (entityType as EntityType)?.GetTableName();
      String primaryKey = entityType.FindPrimaryKey().Properties[0].Name;
      if (isCreate) {
        SqlOperation sqlOperation = new SqlOperation();
        sqlOperation.Sql = $@"CREATE TRIGGER...";
        operations.Add(sqlOperation);
      }
      else {
        // drop trigger sqloperation
      }
    }
    return operations;
  }

  private static IAnnotation GetTableAnnotation(IEntityType entityType) {
    return entityType.GetAnnotations()?.FirstOrDefault(x => x.Name == "WantTrigger");
  }

  public Boolean HasTriggerAnnotationDifferences(IModel source, IModel target) {
    return GetTriggerTriggerDifferences(source, target).Any();
  }
}

Replace the model differ in your DbContext

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
  base.OnConfiguring(optionsBuilder);
  if (optionsBuilder == null) {
    return;
  }
  optionsBuilder.ReplaceService<IMigrationsModelDiffer, MyMigrationsModelDiffer>();
}

Mark the desired Models with an annotation.

builder.Entity<MyTable>().HasAnnotation("WantTrigger", "1.0");

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Bamdad
Solution 3 Peter Csala
Solution 4 Diego Frehner

Related Questions