Entity Framework Core 知识库

Appearance

自定义迁移操作 ​

在 EF Core 中,除了基本的 CRUD 迁移操作外,有时我们需要执行更复杂的数据库变更。本章将深入探讨如何创建自定义迁移操作,实现细粒度的数据库架构控制。

目录 ​

1. 为什么需要自定义迁移 ​

1.1 内置操作的局限性 ​

EF Core 提供的内置迁移操作包括:

  • CreateTable / DropTable
  • AddColumn / DropColumn
  • AlterColumn
  • CreateIndex / DropIndex
  • AddForeignKey / DropForeignKey

但以下场景需要自定义操作:

csharp
// ❌ 无法用内置操作完成的任务:
// 1. 重命名表或列
// 2. 修改列的数据类型(有数据时)
// 3. 拆分列/合并列
// 4. 创建数据库视图
// 5. 创建存储过程/触发器
// 6. 添加数据库约束(CHECK 约束)
// 7. 填充默认数据

1.2 自定义迁移的应用场景 ​

典型场景示例:

csharp
// 场景1: 用户表重构 - 姓名列拆分为名和姓
// 原结构: FullName (nvarchar(200))
// 新结构: FirstName (nvarchar(100)), LastName (nvarchar(100))

// 场景2: 添加枚举字段并填充历史数据
public enum OrderStatus
{
    Pending = 0,
    Processing = 1,
    Shipped = 2,
    Delivered = 3,
    Cancelled = 4
}

// 场景3: 创建数据库视图用于报表
CREATE VIEW v_OrderStatistics AS
SELECT 
    YEAR(OrderDate) as Year,
    MONTH(OrderDate) as Month,
    COUNT(*) as TotalOrders,
    SUM(TotalAmount) as TotalRevenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate);

// 场景4: 添加 CHECK 约束确保数据完整性
ALTER TABLE Products ADD CONSTRAINT CK_Products_Price 
CHECK (Price >= 0 AND Price <= 999999.99);

2. MigrationBuilder API 详解 ​

2.1 Sql() 方法 - 执行原始 SQL ​

最基础的自定义迁移方法,可以执行任意 SQL 语句。

基本语法:

csharp
public partial class AddCustomMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 执行任意 SQL
        migrationBuilder.Sql("UPDATE Products SET IsAvailable = 1 WHERE Stock > 0");
        
        // 支持多行 SQL
        migrationBuilder.Sql(@"
            CREATE VIEW v_ActiveCustomers AS
            SELECT Id, Name, Email
            FROM Customers
            WHERE IsActive = 1 AND DeletedAt IS NULL;
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW IF EXISTS v_ActiveCustomers");
    }
}

实际案例: 数据迁移:

csharp
public partial class MigrateCustomerPhoneData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 步骤1: 添加新列
        migrationBuilder.AddColumn<string>(
            name: "CountryCode",
            table: "Customers",
            type: "nvarchar(10)",
            nullable: true,
            defaultValue: "+86");
        
        migrationBuilder.AddColumn<string>(
            name: "AreaCode",
            table: "Customers",
            type: "nvarchar(10)",
            nullable: true);
        
        migrationBuilder.AddColumn<string>(
            name: "PhoneNumber",
            table: "Customers",
            type: "nvarchar(20)",
            nullable: true);
        
        // 步骤2: 从旧列解析数据到新列
        migrationBuilder.Sql(@"
            UPDATE Customers
            SET 
                PhoneNumber = SUBSTRING(Phone, CHARINDEX('-', Phone) + 1, LEN(Phone)),
                AreaCode = CASE 
                    WHEN CHARINDEX('-', Phone) > 0 
                    THEN SUBSTRING(Phone, 1, CHARINDEX('-', Phone) - 1)
                    ELSE '010'
                END
            WHERE Phone IS NOT NULL AND Phone <> '';
        ");
        
        // 步骤3: 删除旧列
        migrationBuilder.DropColumn(
            name: "Phone",
            table: "Customers");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 回滚: 恢复旧列
        migrationBuilder.AddColumn<string>(
            name: "Phone",
            table: "Customers",
            type: "nvarchar(50)",
            nullable: true);
        
        // 恢复数据
        migrationBuilder.Sql(@"
            UPDATE Customers
            SET Phone = ISNULL(AreaCode, '010') + '-' + ISNULL(PhoneNumber, '')
            WHERE PhoneNumber IS NOT NULL;
        ");
        
        // 删除新列
        migrationBuilder.DropColumn(name: "PhoneNumber", table: "Customers");
        migrationBuilder.DropColumn(name: "AreaCode", table: "Customers");
        migrationBuilder.DropColumn(name: "CountryCode", table: "Customers");
    }
}

2.2 参数化 SQL - 防止注入 ​

csharp
public partial class UpdateProductPrices : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // ⚠️ 不安全: 直接拼接字符串
        // migrationBuilder.Sql("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = " + categoryId);
        
        // ✅ 安全: 使用参数化查询
        migrationBuilder.Sql(
            sql: "UPDATE Products SET Price = Price * @multiplier WHERE CategoryId = @categoryId",
            suppressTransaction: false,
            parameters: new { multiplier = 1.1m, categoryId = 5 });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            sql: "UPDATE Products SET Price = Price / @multiplier WHERE CategoryId = @categoryId",
            parameters: new { multiplier = 1.1m, categoryId = 5 });
    }
}

2.3 事务控制 ​

csharp
public partial class ComplexMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 默认情况下,整个迁移在一个事务中执行
        
        // 如果需要某些操作在事务外执行:
        migrationBuilder.Sql(
            sql: "CREATE INDEX IX_LargeTable_Column1 ON LargeTable(Column1)",
            suppressTransaction: true);  // 某些数据库不支持事务内的索引创建
        
        // 其他操作在事务内
        migrationBuilder.Sql("UPDATE Products SET Status = 'Active'");
    }
}

3. 自定义 SQL 操作 ​

3.1 创建和管理数据库视图 ​

定义视图:

csharp
public partial class CreateOrderStatisticsView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE VIEW [dbo].[v_OrderStatistics] AS
            SELECT 
                YEAR(o.OrderDate) AS OrderYear,
                MONTH(o.OrderDate) AS OrderMonth,
                c.Country,
                COUNT(DISTINCT o.Id) AS TotalOrders,
                COUNT(DISTINCT o.CustomerId) AS UniqueCustomers,
                SUM(oi.Quantity) AS TotalQuantity,
                SUM(oi.Quantity * oi.UnitPrice) AS TotalRevenue,
                AVG(oi.Quantity * oi.UnitPrice) AS AvgOrderValue
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.Id
            INNER JOIN OrderItems oi ON o.Id = oi.OrderId
            GROUP BY 
                YEAR(o.OrderDate),
                MONTH(o.OrderDate),
                c.Country;
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW IF EXISTS [dbo].[v_OrderStatistics]");
    }
}

使用视图:

csharp
// 定义视图对应的实体
[Keyless]  // .NET 5+ 使用 Keyless,之前使用 HasNoKey()
public class OrderStatistics
{
    public int OrderYear { get; set; }
    public int OrderMonth { get; set; }
    public string Country { get; set; }
    public int TotalOrders { get; set; }
    public int UniqueCustomers { get; set; }
    public int TotalQuantity { get; set; }
    public decimal TotalRevenue { get; set; }
    public decimal AvgOrderValue { get; set; }
}

// 配置视图
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<OrderStatistics>(entity =>
    {
        entity.ToView("v_OrderStatistics");  // 映射到视图,不会创建表
        entity.HasNoUpdateLock();  // 可选:禁用更新锁
    });
}

// 查询视图
var stats = await context.OrderStatistics
    .Where(s => s.OrderYear == 2024)
    .OrderByDescending(s => s.TotalRevenue)
    .ToListAsync();

3.2 创建和管理存储过程 ​

定义存储过程:

csharp
public partial class CreateCalculateOrderTotalProcedure : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE PROCEDURE [dbo].[sp_CalculateOrderTotal]
                @OrderId INT,
                @IncludeTax BIT = 1
            AS
            BEGIN
                SET NOCOUNT ON;
                
                DECLARE @Subtotal DECIMAL(18,2);
                DECLARE @TaxRate DECIMAL(5,4) = 0.13;
                DECLARE @Total DECIMAL(18,2);
                
                -- 计算小计
                SELECT @Subtotal = SUM(oi.Quantity * oi.UnitPrice)
                FROM OrderItems oi
                WHERE oi.OrderId = @OrderId;
                
                -- 计算总计
                IF @IncludeTax = 1
                    SET @Total = @Subtotal * (1 + @TaxRate);
                ELSE
                    SET @Total = @Subtotal;
                
                -- 更新订单
                UPDATE Orders
                SET TotalAmount = @Total
                WHERE Id = @OrderId;
                
                SELECT @Total AS TotalAmount;
            END
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP PROCEDURE IF EXISTS [dbo].[sp_CalculateOrderTotal]");
    }
}

调用存储过程:

csharp
// 方法1: 使用 FromSqlRaw
var result = await context.Database
    .SqlQueryRaw<decimal>("EXEC sp_CalculateOrderTotal @p0, @p1", orderId, includeTax)
    .FirstOrDefaultAsync();

// 方法2: 使用 ExecuteSqlRaw (非查询)
var rowsAffected = await context.Database
    .ExecuteSqlRawAsync("EXEC sp_UpdateInventory @p0, @p1", productId, quantity);

// 方法3: 使用 ADO.NET
using var command = context.Database.GetDbConnection().CreateCommand();
command.CommandText = "sp_CalculateOrderTotal";
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@OrderId", orderId));
command.Parameters.Add(new SqlParameter("@IncludeTax", includeTax));

await context.Database.OpenConnectionAsync();
var result = await command.ExecuteScalarAsync();

3.3 创建和管理触发器 ​

定义触发器:

csharp
public partial class CreateAuditTrigger : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE TRIGGER [dbo].[tr_Products_Audit]
            ON [dbo].[Products]
            AFTER INSERT, UPDATE, DELETE
            AS
            BEGIN
                SET NOCOUNT ON;
                
                DECLARE @Action VARCHAR(10);
                DECLARE @EntityId INT;
                DECLARE @Changes NVARCHAR(MAX);
                
                -- 检测操作类型
                IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
                    SET @Action = 'UPDATE';
                ELSE IF EXISTS(SELECT * FROM inserted)
                    SET @Action = 'INSERT';
                ELSE IF EXISTS(SELECT * FROM deleted)
                    SET @Action = 'DELETE';
                
                -- 记录审计日志
                IF @Action IN ('INSERT', 'UPDATE')
                BEGIN
                    SELECT @EntityId = i.Id FROM inserted i;
                    
                    INSERT INTO AuditLogs (EntityName, EntityId, Action, Changes, CreatedAt)
                    VALUES ('Product', @EntityId, @Action, NULL, GETUTCDATE());
                END
                
                IF @Action = 'DELETE'
                BEGIN
                    SELECT @EntityId = d.Id FROM deleted d;
                    
                    INSERT INTO AuditLogs (EntityName, EntityId, Action, Changes, CreatedAt)
                    VALUES ('Product', @EntityId, @Action, NULL, GETUTCDATE());
                END
            END
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP TRIGGER IF EXISTS [dbo].[tr_Products_Audit]");
    }
}

3.4 添加 CHECK 约束 ​

csharp
public partial class AddProductConstraints : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 价格必须为正数
        migrationBuilder.Sql(@"
            ALTER TABLE Products 
            ADD CONSTRAINT CK_Products_Price_Positive 
            CHECK (Price >= 0);
        ");
        
        // 库存不能为负数
        migrationBuilder.Sql(@"
            ALTER TABLE Products 
            ADD CONSTRAINT CK_Products_Stock_NonNegative 
            CHECK (Stock >= 0);
        ");
        
        // 邮箱格式验证
        migrationBuilder.Sql(@"
            ALTER TABLE Customers 
            ADD CONSTRAINT CK_Customers_Email_Format 
            CHECK (Email LIKE '%_@__%.__%');
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("ALTER TABLE Products DROP CONSTRAINT IF EXISTS CK_Products_Price_Positive");
        migrationBuilder.Sql("ALTER TABLE Products DROP CONSTRAINT IF EXISTS CK_Products_Stock_NonNegative");
        migrationBuilder.Sql("ALTER TABLE Customers DROP CONSTRAINT IF EXISTS CK_Customers_Email_Format");
    }
}

4. 自定义迁移操作类 ​

对于复杂的迁移逻辑,可以创建自定义的 MigrationOperation 和 MigrationCommandGenerator。

4.1 创建自定义迁移操作 ​

csharp
// 自定义操作: 重命名列
public class RenameColumnOperation : MigrationOperation
{
    public string Table { get; set; }
    public string OldName { get; set; }
    public string NewName { get; set; }
    
    public override bool IsDestructiveChange => false;
}

// 自定义操作: 移动表到不同的 Schema
public class MoveTableOperation : MigrationOperation
{
    public string TableName { get; set; }
    public string OldSchema { get; set; }
    public string NewSchema { get; set; }
    
    public override bool IsDestructiveChange => false;
}

4.2 创建自定义 SQL 生成器 ​

csharp
public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public CustomSqlServerMigrationSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        IRelationalAnnotationProvider annotations,
        ISqlServerHelper sqlServerHelper)
        : base(dependencies, annotations, sqlServerHelper)
    {
    }
    
    protected override void Generate(
        RenameColumnOperation operation,
        IModel model,
        MigrationCommandListBuilder builder)
    {
        builder
            .Append("EXEC sp_rename '")
            .Append($"{operation.Table}.{operation.OldName}")
            .Append("', '")
            .Append(operation.NewName)
            .AppendLine("', 'COLUMN';");
    }
    
    protected override void Generate(
        MoveTableOperation operation,
        IModel model,
        MigrationCommandListBuilder builder)
    {
        builder
            .Append("ALTER SCHEMA ")
            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.NewSchema))
            .Append(" TRANSFER ")
            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.TableName))
            .AppendLine(";");
    }
}

4.3 注册自定义生成器 ​

csharp
// 在 DbContext 中注册
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .ReplaceService<IMigrationsSqlGenerator, CustomSqlServerMigrationSqlGenerator>();
}

4.4 使用自定义操作 ​

csharp
public partial class RenameProductColumns : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddOperation(new RenameColumnOperation
        {
            Table = "Products",
            OldName = "ProductName",
            NewName = "Name"
        });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddOperation(new RenameColumnOperation
        {
            Table = "Products",
            OldName = "Name",
            NewName = "ProductName"
        });
    }
}

5. 数据迁移与转换 ​

5.1 列拆分 ​

将一个列拆分为多个列,例如 FullName → FirstName + LastName。

csharp
public partial class SplitCustomerName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 步骤1: 添加新列
        migrationBuilder.AddColumn<string>(
            name: "FirstName",
            table: "Customers",
            type: "nvarchar(100)",
            nullable: true);
        
        migrationBuilder.AddColumn<string>(
            name: "LastName",
            table: "Customers",
            type: "nvarchar(100)",
            nullable: true);
        
        // 步骤2: 迁移数据
        migrationBuilder.Sql(@"
            UPDATE Customers
            SET 
                FirstName = CASE 
                    WHEN CHARINDEX(' ', FullName) > 0 
                    THEN LEFT(FullName, CHARINDEX(' ', FullName) - 1)
                    ELSE FullName
                END,
                LastName = CASE 
                    WHEN CHARINDEX(' ', FullName) > 0 
                    THEN RIGHT(FullName, LEN(FullName) - CHARINDEX(' ', FullName))
                    ELSE ''
                END
            WHERE FullName IS NOT NULL;
        ");
        
        // 步骤3: 删除旧列
        migrationBuilder.DropColumn(
            name: "FullName",
            table: "Customers");
        
        // 步骤4: 设置新列为必需
        migrationBuilder.AlterColumn<string>(
            name: "FirstName",
            table: "Customers",
            type: "nvarchar(100)",
            nullable: false,
            oldClrType: typeof(string),
            oldType: "nvarchar(100)",
            oldNullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 恢复 FullName 列
        migrationBuilder.AddColumn<string>(
            name: "FullName",
            table: "Customers",
            type: "nvarchar(200)",
            nullable: true);
        
        // 合并数据
        migrationBuilder.Sql(@"
            UPDATE Customers
            SET FullName = FirstName + ' ' + LastName
            WHERE FirstName IS NOT NULL OR LastName IS NOT NULL;
        ");
        
        // 删除新列
        migrationBuilder.DropColumn(name: "FirstName", table: "Customers");
        migrationBuilder.DropColumn(name: "LastName", table: "Customers");
    }
}

5.2 数据类型转换 ​

csharp
public partial class ConvertPriceToInt : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 假设原来用 decimal(18,2) 存储元,现在改为用 int 存储分
        
        // 步骤1: 添加临时列
        migrationBuilder.AddColumn<int>(
            name: "PriceInCents_Temp",
            table: "Products",
            type: "int",
            nullable: false,
            defaultValue: 0);
        
        // 步骤2: 转换数据 (元→分)
        migrationBuilder.Sql(@"
            UPDATE Products
            SET PriceInCents_Temp = CAST(Price * 100 AS INT)
            WHERE Price IS NOT NULL;
        ");
        
        // 步骤3: 删除旧列
        migrationBuilder.DropColumn(
            name: "Price",
            table: "Products");
        
        // 步骤4: 重命名临时列
        migrationBuilder.RenameColumn(
            name: "PriceInCents_Temp",
            table: "Products",
            newName: "Price");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 反向转换
        migrationBuilder.AddColumn<decimal>(
            name: "Price_Temp",
            table: "Products",
            type: "decimal(18,2)",
            nullable: false,
            defaultValue: 0m);
        
        migrationBuilder.Sql(@"
            UPDATE Products
            SET Price_Temp = CAST(Price AS DECIMAL(18,2)) / 100
            WHERE Price IS NOT NULL;
        ");
        
        migrationBuilder.DropColumn(name: "Price", table: "Products");
        
        migrationBuilder.RenameColumn(
            name: "Price_Temp",
            table: "Products",
            newName: "Price");
    }
}

5.3 枚举字段迁移 ​

csharp
public enum OrderStatus
{
    Pending = 0,      // 待处理
    Processing = 1,   // 处理中
    Shipped = 2,      // 已发货
    Delivered = 3,    // 已送达
    Cancelled = 4     // 已取消
}

public partial class AddOrderStatusEnum : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 添加 OrderStatus 列
        migrationBuilder.AddColumn<int>(
            name: "OrderStatus",
            table: "Orders",
            type: "int",
            nullable: false,
            defaultValue: 0);
        
        // 根据现有数据映射状态
        migrationBuilder.Sql(@"
            UPDATE Orders
            SET OrderStatus = 
                CASE
                    WHEN IsCancelled = 1 THEN 4
                    WHEN IsShipped = 1 THEN 2
                    WHEN IsDelivered = 1 THEN 3
                    ELSE 0
                END
        ");
        
        // 删除旧的布尔标志列
        migrationBuilder.DropColumn(name: "IsCancelled", table: "Orders");
        migrationBuilder.DropColumn(name: "IsShipped", table: "Orders");
        migrationBuilder.DropColumn(name: "IsDelivered", table: "Orders");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 恢复旧列
        migrationBuilder.AddColumn<bool>(
            name: "IsCancelled", table: "Orders", type: "bit", nullable: false, defaultValue: false);
        migrationBuilder.AddColumn<bool>(
            name: "IsShipped", table: "Orders", type: "bit", nullable: false, defaultValue: false);
        migrationBuilder.AddColumn<bool>(
            name: "IsDelivered", table: "Orders", type: "bit", nullable: false, defaultValue: false);
        
        // 反向映射
        migrationBuilder.Sql(@"
            UPDATE Orders
            SET 
                IsCancelled = CASE WHEN OrderStatus = 4 THEN 1 ELSE 0 END,
                IsShipped = CASE WHEN OrderStatus = 2 THEN 1 ELSE 0 END,
                IsDelivered = CASE WHEN OrderStatus = 3 THEN 1 ELSE 0 END
        ");
        
        // 删除新列
        migrationBuilder.DropColumn(name: "OrderStatus", table: "Orders");
    }
}

6. 条件迁移 ​

根据不同的条件执行不同的迁移逻辑。

6.1 基于数据库类型的条件迁移 ​

csharp
public partial class ConditionalMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 检测数据库类型
        if (migrationBuilder.IsSqlServer())
        {
            // SQL Server 特定语法
            migrationBuilder.Sql(@"
                CREATE NONCLUSTERED INDEX IX_Products_Name
                ON Products(Name);
            ");
        }
        else if (migrationBuilder.IsNpgsql())
        {
            // PostgreSQL 特定语法
            migrationBuilder.Sql(@"
                CREATE INDEX IX_Products_Name
                ON Products USING btree(Name);
            ");
        }
        else if (migrationBuilder.IsMySql())
        {
            // MySQL 特定语法
            migrationBuilder.Sql(@"
                CREATE INDEX IX_Products_Name
                ON Products(Name) USING BTREE;
            ");
        }
    }
}

// 扩展方法
public static class MigrationBuilderExtensions
{
    public static bool IsSqlServer(this MigrationBuilder migrationBuilder)
    {
        return migrationBuilder.ActiveProvider == "Microsoft.EntityFrameworkCore.SqlServer";
    }
    
    public static bool IsNpgsql(this MigrationBuilder migrationBuilder)
    {
        return migrationBuilder.ActiveProvider == "Npgsql.EntityFrameworkCore.PostgreSQL";
    }
    
    public static bool IsMySql(this MigrationBuilder migrationBuilder)
    {
        return migrationBuilder.ActiveProvider == "Pomelo.EntityFrameworkCore.MySql";
    }
}

6.2 基于数据的条件迁移 ​

csharp
public partial class MigrateOldData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 检查是否有旧数据需要迁移
        var hasLegacyData = migrationBuilder.HasData(
            "SELECT COUNT(*) FROM LegacyProducts WHERE MigratedToNewFormat = 0");
        
        if (hasLegacyData)
        {
            // 执行数据迁移
            migrationBuilder.Sql(@"
                INSERT INTO Products (Name, Description, Price, CreatedAt)
                SELECT 
                    ProductName,
                    ProductDescription,
                    UnitPrice,
                    CreatedDate
                FROM LegacyProducts
                WHERE MigratedToNewFormat = 0;
                
                UPDATE LegacyProducts
                SET MigratedToNewFormat = 1
                WHERE MigratedToNewFormat = 0;
            ");
        }
    }
}

7. 跨数据库兼容迁移 ​

编写可在多个数据库提供商上运行的迁移。

7.1 抽象层设计 ​

csharp
public abstract class CrossDatabaseMigration : Migration
{
    protected abstract void UpSqlServer(MigrationBuilder migrationBuilder);
    protected abstract void UpPostgreSql(MigrationBuilder migrationBuilder);
    protected abstract void UpMySql(MigrationBuilder migrationBuilder);
    
    protected abstract void DownSqlServer(MigrationBuilder migrationBuilder);
    protected abstract void DownPostgreSql(MigrationBuilder migrationBuilder);
    protected abstract void DownMySql(MigrationBuilder migrationBuilder);
    
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        switch (migrationBuilder.ActiveProvider)
        {
            case "Microsoft.EntityFrameworkCore.SqlServer":
                UpSqlServer(migrationBuilder);
                break;
            case "Npgsql.EntityFrameworkCore.PostgreSQL":
                UpPostgreSql(migrationBuilder);
                break;
            case "Pomelo.EntityFrameworkCore.MySql":
                UpMySql(migrationBuilder);
                break;
            default:
                throw new NotSupportedException($"Unsupported provider: {migrationBuilder.ActiveProvider}");
        }
    }
    
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        switch (migrationBuilder.ActiveProvider)
        {
            case "Microsoft.EntityFrameworkCore.SqlServer":
                DownSqlServer(migrationBuilder);
                break;
            case "Npgsql.EntityFrameworkCore.PostgreSQL":
                DownPostgreSql(migrationBuilder);
                break;
            case "Pomelo.EntityFrameworkCore.MySql":
                DownMySql(migrationBuilder);
                break;
            default:
                throw new NotSupportedException($"Unsupported provider: {migrationBuilder.ActiveProvider}");
        }
    }
}

7.2 实际应用示例 ​

csharp
public partial class CreateProductsTableCrossDb : CrossDatabaseMigration
{
    protected override void UpSqlServer(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(200)", nullable: false),
                Price = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false)
                    .Annotation("SqlServer:DefaultValueSql", "GETUTCDATE()")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
            });
        
        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name");
    }
    
    protected override void UpPostgreSql(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Name = table.Column<string>(type: "character varying(200)", nullable: false),
                Price = table.Column<decimal>(type: "numeric(18,2)", nullable: false),
                CreatedAt = table.Column<DateTime>(type: "timestamp with time zone", nullable: false)
                    .Annotation("Npgsql:DefaultValueSql", "NOW()")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
            });
        
        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name");
    }
    
    protected override void UpMySql(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                Name = table.Column<string>(type: "varchar(200)", nullable: false),
                Price = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                CreatedAt = table.Column<DateTime>(type: "datetime(6)", nullable: false)
                    .Annotation("MySql:DefaultValueSql", "UTC_TIMESTAMP()")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
            })
            .Annotation("MySql:CharSet", "utf8mb4");
        
        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name");
    }
    
    // Down 方法类似...
}

8. 最佳实践与常见陷阱 ​

8.1 最佳实践清单 ​

✅ 应该做的:

  1. 始终提供 Down 方法

    csharp
    protected override void Down(MigrationBuilder migrationBuilder)
{
    // 确保可以安全回滚
}

  2. 使用参数化查询防止注入

    csharp
    migrationBuilder.Sql(
    "UPDATE Products SET Price = @price WHERE Id = @id",
    parameters: new { price = 99.99m, id = 1 });

  3. 分批处理大数据量

    csharp
    migrationBuilder.Sql(@"
    WHILE (1=1)
    BEGIN
        UPDATE TOP (1000) Products
        SET IsProcessed = 1
        WHERE IsProcessed = 0;
        
        IF @@ROWCOUNT = 0 BREAK;
    END
");

  4. 测试迁移脚本

    bash
    # 生成 SQL 脚本进行审查
dotnet ef migrations script PreviousMigration NextMigration -o migration.sql

  5. 在生产环境前先在测试环境验证

    bash
    # 应用到测试数据库
dotnet ef database update --connection "TestConnectionString"

8.2 常见陷阱 ​

❌ 不应该做的:

  1. 不要在迁移中执行长时间运行的操作而不考虑锁定

    csharp
    // ❌ 坏做法: 可能锁定表很长时间
migrationBuilder.Sql("UPDATE LargeTable SET Processed = 1");

// ✅ 好做法: 分批处理
migrationBuilder.Sql(@"
    DECLARE @BatchSize INT = 1000;
    WHILE (1=1)
    BEGIN
        UPDATE TOP (@BatchSize) LargeTable
        SET Processed = 1
        WHERE Processed = 0;
        
        IF @@ROWCOUNT < @BatchSize BREAK;
    END
");

  2. 不要忘记备份重要数据

    csharp
    // 在破坏性操作前先备份
migrationBuilder.Sql(@"
    SELECT * INTO Products_Backup_20240101
    FROM Products
");

  3. 不要在同一迁移中混合 DDL 和 DML

    csharp
    // ❌ 不推荐
migrationBuilder.CreateTable(...);  // DDL
migrationBuilder.Sql("INSERT INTO ...");  // DML

// ✅ 推荐: 分成两个迁移
// Migration1: CreateTable
// Migration2: SeedData

  4. 不要硬编码数据库特定的标识符

    csharp
    // ❌ 坏做法
migrationBuilder.Sql("CREATE INDEX [IX_Test] ON [dbo].[Products]");

// ✅ 好做法
migrationBuilder.Sql("CREATE INDEX [IX_Test] ON [Products]");

8.3 性能优化技巧 ​

1. 并行索引创建 (SQL Server 2017+)

csharp
migrationBuilder.Sql(@"
    CREATE NONCLUSTERED INDEX IX_Products_Name
    ON Products(Name)
    WITH (ONLINE = ON, MAXDOP = 4);
", suppressTransaction: true);

2. 禁用自动统计信息更新

csharp
migrationBuilder.Sql(@"
    ALTER DATABASE CURRENT 
    SET AUTO_UPDATE_STATISTICS OFF;
    
    -- 执行迁移...
    
    ALTER DATABASE CURRENT 
    SET AUTO_UPDATE_STATISTICS ON;
");

3. 最小化日志记录

csharp
migrationBuilder.Sql(@"
    ALTER INDEX ALL ON LargeTable REBUILD 
    WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);
", suppressTransaction: true);

8.4 调试技巧 ​

查看生成的 SQL:

bash
# 生成完整 SQL 脚本
dotnet ef migrations script --idempotent -o migration.sql

# 查看单个迁移的 SQL
dotnet ef migrations script PreviousMigration NextMigration -o migration.sql

启用详细日志:

csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information);
}

总结 ​

自定义迁移操作是 EF Core 高级功能的核心部分,允许你:

  1. 执行任意 SQL - 突破 ORM 限制,实现复杂数据库变更
  2. 管理数据库对象 - 视图、存储过程、触发器、约束
  3. 数据迁移与转换 - 列拆分、类型转换、枚举映射
  4. 跨数据库兼容 - 编写可移植的迁移脚本
  5. 精细控制 - 自定义迁移操作类和 SQL 生成器

关键原则:

  • ✅ 始终提供可逆的 Down 方法
  • ✅ 使用参数化查询
  • ✅ 分批处理大数据量
  • ✅ 在生产环境前充分测试
  • ✅ 保留完整的审计日志

掌握这些技术后,你可以应对任何复杂的数据库架构变更需求!

基于 MIT 许可发布

Copyright © 2026 EF Core 知识库