This article explores some options to customize the Entity Framework Core migration table.
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:
ALTER TABLE [track].[__Migrations] ADD CreatedON DATETIME NULL;
ALTER TABLE [track].[__Migrations] _
ADD CONSTRAINT DF__Migrations_CreatedON DEFAULT GETUTCDATE() FOR [CreatedON];
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
History
- 4th August, 2022: Initial version