Customize Entity Framework Core Migration History Table

• Download source code - 13.9 KB

Background

Entity Framework Core keeps track of applied migrations by adding logs in a table named __EFMigrationsHistory, and schema dbo. The migration table contains two columns, MigrationId and ProductVersion. Here, we are going to explore some options to customize this table.

• Change migration table name and schema
• Change migration table's column names
• Add a default values column in the migration table
• Add a required column in the migration table

Change Table Name and Schema

We are going to use a custom table name __Migrations and schema name track for the migration table. The schema name is optional, if not specified, default will be dbo.

public class AppDb : DbContext
{
    public DbSet<Process> Process { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile(Path.Combine(AppContext.BaseDirectory, "appsettings.json"), 
             optional: false, reloadOnChange: true)
            .Build();
        optionsBuilder
            .UseSqlServer(config.GetConnectionString("DatabaseConnection"), 
                d => { d.MigrationsHistoryTable("__Migrations", "track"); });
    }
}

Change Column Names

To change column names, we are going to replace an existing/default service of type IHistoryRepository with a custom config class HistoryRepository:

public class AppDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile(Path.Combine(AppContext.BaseDirectory, "appsettings.json"), 
                         optional: false, reloadOnChange: true)
            .Build();
        optionsBuilder
            .UseSqlServer(config.GetConnectionString("DatabaseConnection")})
            .ReplaceService<IHistoryRepository, HistoryRepository>();
    }
}

Here, we are changing table column names from:

• MigrationId to Id
• ProductVersion to Version.

internal class HistoryRepository : SqlServerHistoryRepository
{
    public HistoryRepository(HistoryRepositoryDependencies dependencies) : 
                             base(dependencies)
    {
    }
    protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> history)
    {
        base.ConfigureTable(history);
        history.Property(h => h.MigrationId).HasColumnName("Id");
        history.Property(h => h.ProductVersion).HasColumnName("Version");
    }
}

Inside ConfigureTable(EntityTypeBuilder<HistoryRow> history) method, this section actually looks smaller like entity mapping using Fluent API. We should be able to change other things like table name, schema name, data type, etc. I haven't tried yet, but logically, that should work.

Add Column With Default Value

Add an empty migration Init to the project. It is important to make sure this is the first migration and added before adding/mapping any table or any other object to the DbContext class. Initially, the Up and Down methods will be empty.

Add-Migration Init

Up

Adding the new AppliedAtUtc column to the migration table [track].[__Migrations] and creating a constraint DF__Migrations_AppliedAtUtc for that column to set the current UTC date time as the default value.

Down

Dropping the constraint we added in the up section and the column itself.

public partial class Init : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("ALTER TABLE [track].[__Migrations] 
                              ADD AppliedAtUtc DATETIME NULL;");
        migrationBuilder.Sql("ALTER TABLE [track].[__Migrations] 
                              ADD CONSTRAINT DF__Migrations_AppliedAtUtc 
                              DEFAULT GETUTCDATE() FOR [AppliedAtUtc];");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropCheckConstraint("DF__Migrations_AppliedAtUtc", 
                                             "__Migrations", "track");
        migrationBuilder.DropColumn("AppliedAtUtc", "__Migrations", "track");
    }
}

Equivalent SQL:

/*changes*/
ALTER TABLE [track].[__Migrations] ADD CreatedON DATETIME NULL;
ALTER TABLE [track].[__Migrations] _
      ADD CONSTRAINT DF__Migrations_CreatedON DEFAULT GETUTCDATE() FOR [CreatedON];
/*rollback*/
ALTER TABLE [track].[__Migrations] DROP CONSTRAINT DF__Migrations_CreatedON;
ALTER TABLE [track].[__Migrations] DROP COLUMN CreatedON;

Add Required Column

Here, we are going to add a required column ProjectName to the migration table.

public class AppDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var config = new ConfigurationBuilder()
            .AddJsonFile(Path.Combine(AppContext.BaseDirectory, 
            "appsettings.json"), optional: false, reloadOnChange: true)
            .Build();
        optionsBuilder
            .UseSqlServer(config.GetConnectionString("DatabaseConnection")})
            .ReplaceService<IHistoryRepository, HistoryRepository>();
    }
}

Inside ConfigureTable(EntityTypeBuilder<HistoryRow> history), we are adding a new column as required. And inside GetInsertScript(HistoryRow row) method, we are creating a custom insert statement for the migration table.

public class ContextConstants
{
    public static string ProjectName = "Console";
}

internal class HistoryRepository : SqlServerHistoryRepository
{
    public const string CustomColumnName = "ProjectName";
    public HistoryRepository(HistoryRepositoryDependencies dependencies) : 
                             base(dependencies)
    {
    }
    protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> history)
    {
        base.ConfigureTable(history);
        history.Property<string>(CustomColumnName).HasMaxLength(300).IsRequired();
    }
    public override string GetInsertScript(HistoryRow row)
    {
        var stringTypeMapping = 
            Dependencies.TypeMappingSource.GetMapping(typeof(string));
        return new StringBuilder()
            .Append("INSERT INTO ")
            .Append(SqlGenerationHelper.DelimitIdentifier(TableName, TableSchema))
            .Append("(")
            .Append(SqlGenerationHelper.DelimitIdentifier(MigrationIdColumnName))
            .Append(", ")
            .Append(SqlGenerationHelper.DelimitIdentifier(ProductVersionColumnName))
            .Append(", ")
            .Append(SqlGenerationHelper.DelimitIdentifier(CustomColumnName))
            .Append(") ")
            .Append("VALUES (")
            .Append(stringTypeMapping.GenerateSqlLiteral(row.MigrationId))
            .Append(", ")
            .Append(stringTypeMapping.GenerateSqlLiteral(row.ProductVersion))
            .Append(", ")
            .Append(stringTypeMapping.GenerateSqlLiteral(ContextConstants.ProjectName))
            .Append(")")
            .AppendLine(SqlGenerationHelper.StatementTerminator)
            .ToString();
    }
}

About Code Sample

• Visual Studio 2022 Solution
• EF Core 6 (also tested with EF Core 5)
• Check codes of Db.Custom project, AppDb.cs. For testing, we need to set this project as a startup project.

In appsettings.json, we will find the target DB connections:

{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=.\\SQLEXPRESS;Initial Catalog=Cup;
                           Integrated Security=True"
  }
}

Commands:

Add-Migration Init
Update-Database
Script-Migration

Drop-Database 
Remove-Migration Init

Db:

References

• https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table
• https://stackoverflow.com/questions/55342435/how-to-customize-migration-history-table-with-entity-framework-core

History

• 4^th August, 2022: Initial version