EF Core 2.1 Code-First模式下为__MigrationsHistory表添加自定义列

Alright, let's tackle adding that AppliedOn column to track when migrations are actually applied to your database in EF Core 2.1 Code-First. Since __MigrationsHistory is a system-managed table that EF Core relies on to track migrations, we can't just create a standard EF migration for it. Instead, we'll combine manual SQL changes with custom EF Core configuration to make this work properly.

Step 1: Add the AppliedOn Column to the Existing Table #

First, we need to manually add the column to your existing __MigrationsHistory table. Run this SQL script against your database (adjust the schema if you're not using dbo):

ALTER TABLE [dbo].[__MigrationsHistory]
ADD [AppliedOn] DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME();

• Using DATETIME2 is recommended for precise time tracking, and SYSUTCDATETIME() ensures we store UTC time (avoids timezone issues).
• The DEFAULT value will populate the column for all existing migration records automatically.

Step 2: Create a Custom History Row Class #

Next, we'll define a custom class that extends EF Core's default HistoryRow to include the AppliedOn property. This tells EF Core about the new column:

using Microsoft.EntityFrameworkCore.Migrations;
using System;

public class CustomHistoryRow : HistoryRow
{
    public DateTime AppliedOn { get; set; }
}

Step 3: Implement a Custom History Repository #

We need a custom history repository to configure the AppliedOn column and ensure EF Core handles it correctly during future migrations. For SQL Server, inherit from SqlServerHistoryRepository (adjust this if you're using a different database like MySQL or PostgreSQL):

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System;

public class CustomSqlServerHistoryRepository : SqlServerHistoryRepository
{
    public CustomSqlServerHistoryRepository(HistoryRepositoryDependencies dependencies)
        : base(dependencies)
    {
    }

    protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> builder)
    {
        // Start with the default configuration for __MigrationsHistory
        base.ConfigureTable(builder);
        
        // Add configuration for our new AppliedOn column
        builder.Property<DateTime>("AppliedOn")
               .IsRequired()
               .HasDefaultValueSql("SYSUTCDATETIME()");
    }
}

Step 4: Register the Custom Repository in Your DbContext #

Finally, update your DbContext to use our custom history repository instead of the default one. Do this in the OnConfiguring method:

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

public class YourDbContext : DbContext
{
    // Your DbContext properties and DbSets here...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
           .UseSqlServer("Your_Connection_String_Here", 
               opts => opts.MigrationsHistoryTable("__MigrationsHistory", "dbo"))
           .ReplaceService<IHistoryRepository, CustomSqlServerHistoryRepository>();
    }
}

What This Does #

• From now on, any new migrations you create and apply will automatically have the AppliedOn column populated with the UTC time the migration ran.
• Existing migration records already have values from the initial SQL script.
• EF Core will recognize the AppliedOn column and include it when querying the __MigrationsHistory table.

Just a quick note: If you're using a database other than SQL Server, you'll need to inherit from the appropriate history repository class (e.g., MySqlHistoryRepository for MySQL) and adjust the SQL syntax for the default value accordingly.

内容的提问来源于stack exchange，提问作者Aliaksei Zhukau