Executing SQL Code within EntityFrameworkCore Migration in Correct Order (SQLite)

Introduction

A new MigrationSqlGenerator that will generate SQL from MigrationOperations that can have attribute "ExecuteAfter" or "ExecuteBefore". Hence, letting the possibility to change the value of a column right after altering the column in the same migration.

Background

The problem came when I wanted to change a database column type from NOT NULL to NULL. After the modification, I wanted all my "null" values to be changed to real nulls. And all this, in the same migration file because I didn't want one to execute only one migration (because the issue can be fixed easily by having two migrations). Hence, writing this new MigrationsSqlGenerator that looks at attributes on MigrationOperation classes to choose how to group them and to execute them by group.

I am using SQLite. So, maybe the issue isn't happening using another database and thus, another MigrationsSqlGenerator. If someone could test this, and see the issue happening with other database, I will change the article stating it also fixes for other databases.

Initial question I asked on StackOverFlow.

Using the Code

Full project com.cyberinternauts.csharp.Database on GitHub (though, not including the usage).

As for now, the project only has only two MigrationOperations that allows to change a date type column from NULL to NOT NULL and vice-versa. The fake null value when the column is NOT NULL is '0001-01-01 00:00:00'.

I added two new MigrationOperation called ArbitrarySqlBefore and ArbitrarySqlAfter so there is no need to create other MigrationOperation classes unless desired.

1. First add the project com.cyberinternauts.csharp.Database to your project by:
   • Adding a submodule to your repo
   • Cloning the repo without submodule
   • Copying the code into your project
2. Add this into your DbContext:
   C#

   protected override void OnConfiguring(DbContextOptionsBuilder options)
   {
       options.ReplaceService<IMigrationsSqlGenerator, 
       com.cyberinternauts.csharp.Database.MigrationsSqlGenerator
                                           <SqliteMigrationsSqlGenerator>>();
   }

3. Create a migration.
4. Add this using to the newly created migration:
   C#

   using com.cyberinternauts.csharp.Database;

5. Example of usage in the Up method.
   C#

   migrationBuilder.AlterColumn<DateTime>(
       name: "BirthDay",
       table: "MetaPersons",
       type: "TEXT",
       nullable: true,
       oldClrType: typeof(DateTime),
       oldType: "TEXT");
   migrationBuilder.ChangeDateToNullable("MetaPersons", "BirthDay");

Understanding the Core

The most important point of this article is to know how this has been achieved. The creation of a class using the decorator pattern: com.cyberinternauts.csharp.Database.MigrationsSqlGenerator .

This class implement IMigrationsSqlGenerator so it can be used instead of your current migrations SQL generator. It uses a generic parameter that allows you to pass the real generator that you are currently using and it allows you to add new MigrationOperation classes that supports an attribute: ExecuteBefore or ExecuteAfter.

First, the generalization of the class to use a decorator pattern:

public class MigrationsSqlGenerator<GeneratorType> : 
    IMigrationsSqlGenerator where GeneratorType : IMigrationsSqlGenerator

The class implements IMigrationsSqlGenerator and has a generic type that needs to implement IMigrationsSqlGenerator. There is a difference with the image below: there is no Decorator abstract class, I went directly to a usable concrete class.

With this in place, the class can be used as a migration SQL generator and it can create and use a specific already existing generator. Hence, because of the need to create the underlying generator, the class has a constructor that has the same parameters as the Microsoft one:

Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator .

public MigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, 
                              IRelationalAnnotationProvider migrationsAnnotations)
{
    if (Activator.CreateInstance(typeof(GeneratorType), new object[] 
       { dependencies, migrationsAnnotations }) is GeneratorType generator)
    {
        BaseGenerator = generator;
        Dependencies = dependencies;
    }
    else
    {
        throw new MissingMethodException(typeof(GeneratorType) + 
        " is missing a constructor (" + typeof(MigrationsSqlGeneratorDependencies) + ", 
        " + typeof(IRelationalAnnotationProvider) + ")");
    }
}

The usage of a generic parameter (GeneratorType) instead of a parameter in the constructor is to continue to support dependency injection using the same constructor signature as the Microsoft ones.

The implementation of the method of IMigrationsSqlGenerator interface is where the real "magic" appears: reordering the operations and executing them in groups.

At first, it selects the migration operations that are marked with an attribute ExecuteBefore, stores them and removes them from the operations list that was passed.

// Take operations to execute before and remove them from "middle" operations
var operationsToExecuteBefore = middleOperations
    .Where(o => o.GetType().CustomAttributes.Any
     (a => a.AttributeType.Equals(typeof(MigrationAttributes.ExecuteBeforeAttribute))))
    .ToList();
operationsToExecuteBefore.ForEach(o => middleOperations.Remove(o));

It does the same for the migration operations marked with ExecuteAfter .

// Take operations to execute after and remove them from "middle" operations
var operationsToExecuteAfter = middleOperations
    .Where(o => o.GetType().CustomAttributes.Any
    (a => a.AttributeType.Equals(typeof(MigrationAttributes.ExecuteAfterAttribute))))
    .ToList();
operationsToExecuteAfter.ForEach(o => middleOperations.Remove(o));

It finally executes all the groups independently and combines the results.

// Generate operations by group (before, middle, after)
var before = Generate(operationsToExecuteBefore, model);
var middle = BaseGenerator.Generate(middleOperations, model, options);
var after = Generate(operationsToExecuteAfter, model);

// Combine generations
var combined = new List<MigrationCommand>();
combined.AddRange(before);
combined.AddRange(middle);
combined.AddRange(after);

Which gives this final method code:

public IReadOnlyList<MigrationCommand> Generate(IReadOnlyList<MigrationOperation> operations, 
       IModel? model = null, MigrationsSqlGenerationOptions options = 
                                          MigrationsSqlGenerationOptions.Default)
{
    var middleOperations = operations.ToList();

    // Take operations to execute before and remove them from "middle" operations
    var operationsToExecuteBefore = middleOperations
        .Where(o => o.GetType().CustomAttributes.Any
         (a => a.AttributeType.Equals(typeof(MigrationAttributes.ExecuteBeforeAttribute))))
        .ToList();
    operationsToExecuteBefore.ForEach(o => middleOperations.Remove(o));

    // Take operations to execute after and remove them from "middle" operations
    var operationsToExecuteAfter = middleOperations
        .Where(o => o.GetType().CustomAttributes.Any
         (a => a.AttributeType.Equals(typeof(MigrationAttributes.ExecuteAfterAttribute))))
        .ToList();
    operationsToExecuteAfter.ForEach(o => middleOperations.Remove(o));

    // Generate operations by group (before, middle, after)
    var before = Generate(operationsToExecuteBefore, model);
    var middle = BaseGenerator.Generate(middleOperations, model, options);
    var after = Generate(operationsToExecuteAfter, model);

    // Combine generations
    var combined = new List<MigrationCommand>();
    combined.AddRange(before);
    combined.AddRange(middle);
    combined.AddRange(after);

    // Return combined generations
    return combined;
}

I skipped the private method Generate that loops through migration operations and calls a Generate method on each of those to get their proper SQL code.

protected IReadOnlyList<MigrationCommand> Generate
          (List<MigrationOperation> operations, IModel? model)
{
    MigrationCommandListBuilder migrationCommandListBuilder = new(Dependencies);
    try
    {
        foreach (BaseMigrationOperation operation in operations)
        {
            operation.Generate(Dependencies, model, migrationCommandListBuilder);
        }
    }
    catch 
    {
        //Nothing to do                    
    }

    return migrationCommandListBuilder.GetCommandList();
}

I skipped it because, in fact, it only mimics the behavior of the following method in the class Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator .

public virtual IReadOnlyList<MigrationCommand> Generate
  (IReadOnlyList<MigrationOperation> operations, IModel? model = null, 
  MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default)

I decided to have the real SQL query generation in the migration operation classes because I prefer not to have to modify the generator class whenever I add a new operation.

Points of Interest

• Having only one migration instead of two
• The MigrationsSqlGenerator is a generic class that can be used with any other: as for me SqliteMigrationsSqlGenerator
• Can you imagine any other? I'll list them here.

History

• 9^th March, 2022: 1.0: Created the article
• 9^th March, 2022: 1.0a: Added two new methods to be able to execute any SQL code
• 10^th March, 2022: 1.0b: Explained core class