构建从MySQL迁移至SQL Server的C#数据迁移工具技术问询

核心思路 #

针对表结构差异、多表迁移需求，以及批量插入后需获取自增ID关联导航属性的场景，推荐结合EF Core特性+通用映射逻辑的方案，既兼顾多表复用性，又能自动处理ID回填与导航属性关联。

方案1：EF Core 批量映射+原生SaveChanges（适配导航属性） #

实现步骤 #

• 定义实体映射：用AutoMapper创建源（MySQL）与目标（SQL Server）实体的映射规则，包括表名、列名映射，以及导航属性的转换（比如将MySqlCustomer.Addresses转成SqlServerCustomer.Addresses）。
• 分页读取源数据：从MySQL分批拉取数据（比如每批1000条），避免内存溢出。
• 批量转换并关联导航属性：通过映射器将源实体转成目标实体，确保导航实体与主实体建立对象引用（无需手动设置外键，EF会自动处理）。
• 批量插入+自动回填ID：将目标实体批量添加到DbContext，调用SaveChangesAsync()后，EF Core会自动将SQL Server生成的自增ID填充到实体中，导航实体的外键也会同步关联。
• 封装通用迁移方法：打造泛型方法，适配不同表的迁移逻辑，减少重复代码。

示例代码 #

// 泛型批量迁移方法
public async Task BatchMigrate<TSource, TTarget>(
    Func<int, int, Task<List<TSource>>> getSourceBatch,
    IMapper mapper,
    int batchSize) where TTarget : class
{
    int pageIndex = 0;
    while (true)
    {
        // 从MySQL分页获取源数据
        var sourceBatch = await getSourceBatch(pageIndex, batchSize);
        if (!sourceBatch.Any()) break;

        // 转换为SQL Server目标实体
        var targetEntities = mapper.Map<List<TTarget>>(sourceBatch);

        // 批量添加到DbContext
        _sqlServerDb.Set<TTarget>().AddRange(targetEntities);
        // SaveChanges后自动回填自增ID，导航属性外键同步关联
        await _sqlServerDb.SaveChangesAsync();

        pageIndex++;
    }
}

// 使用示例（迁移Customer表）
await BatchMigrate<MySqlCustomer, SqlServerCustomer>(
    async (page, size) => await _mySqlDb.Customers
        .Include(c => c.Addresses)
        .Include(c => c.Orders)
        .Skip(page * size)
        .Take(size)
        .ToListAsync(),
    _autoMapper,
    1000);

方案2：EF Core + 自定义批量SQL+OUTPUT子句（高性能场景） #

如果EF原生批量插入性能不足，可通过自定义SQL结合OUTPUT子句批量插入并获取ID，再手动关联导航属性。

实现步骤 #

• 创建SQL Server表值类型：为每个目标表定义对应的表值类型，用于批量传参。
• 生成批量插入SQL：编写带OUTPUT inserted.Id的插入语句，返回插入后的自增ID。
• 执行SQL并获取ID列表：通过DbContext执行原生SQL，读取返回的ID。
• 关联导航属性外键：将ID映射到目标实体，设置导航实体的外键字段。
• 批量插入导航实体：用EF或原生SQL插入导航实体。

示例代码（Customer表批量插入） #

public async Task BatchInsertWithNavigation(List<SqlServerCustomer> customers)
{
    // 1. 准备表值参数（需先在SQL Server创建[dbo].[CustomerTableType]）
    var tvp = new DataTable();
    tvp.Columns.Add("Name", typeof(string));
    tvp.Columns.Add("Email", typeof(string));
    foreach (var c in customers) tvp.Rows.Add(c.Name, c.Email);

    // 2. 执行批量插入并获取ID
    var insertedIds = new List<int>();
    using (var cmd = _sqlServerDb.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = @"
            INSERT INTO [dbo].[Customers] (Name, Email)
            OUTPUT inserted.Id
            SELECT Name, Email FROM @CustomerBatch";
        cmd.Parameters.Add(new SqlParameter("@CustomerBatch", SqlDbType.Structured)
        {
            Value = tvp,
            TypeName = "[dbo].[CustomerTableType]"
        });

        await _sqlServerDb.Database.OpenConnectionAsync();
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync()) insertedIds.Add(reader.GetInt32(0));
        }
    }

    // 3. 关联导航属性外键
    for (int i = 0; i < customers.Count; i++)
    {
        customers[i].Id = insertedIds[i];
        foreach (var order in customers[i].Orders) order.CustomerId = insertedIds[i];
    }

    // 4. 批量插入导航实体
    _sqlServerDb.Orders.AddRange(customers.SelectMany(c => c.Orders));
    await _sqlServerDb.SaveChangesAsync();
}

方案选择建议 #

• 方案1：优先选择，无需编写原生SQL，复用EF的映射与变更追踪，多表迁移适配成本低，性能满足绝大多数场景。
• 方案2：适合超大规模数据迁移（单表千万级以上），性能更高，但需要为每个表创建表值类型，代码复用性稍弱。

注意事项 #

• 事务包裹：每批次迁移需用TransactionScope或EF的事务包裹，避免部分插入失败导致数据不一致。
• 索引优化：迁移前禁用目标表的非必要索引，迁移完成后重建，提升插入速度。
• 数据校验：添加转换后的数据校验逻辑，确保符合目标表的约束（如非空、长度限制）。

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