EF.NET Core单事务多插入流：30万+数据批量Upsert方案咨询

我之前处理过类似的大规模Upsert+事务一致性的场景，给你几个可行的方案，都能避开分布式事务的问题，同时满足性能和主键关联的需求：

方案一：单一DbContext+批量MERGE+手动事务 #

不用创建多个DbContext，而是复用同一个DbContext，在本地事务内执行批量Upsert操作，同时通过SQL的OUTPUT子句获取插入的主键，解决子对象关联问题。

具体步骤： #

1. 开启DbContext的本地事务：

using var transaction = await _dbContext.Database.BeginTransactionAsync();
try
{
    // 按层级顺序处理表：先父表，再子表，最后孙表
    foreach (var parentBatch in parentData.Batch(1000)) // 按1000条分批次
    {
        // 执行父表的MERGE语句，同时输出插入的主键和原始业务标识
        var keyMapping = await _dbContext.Database.SqlQueryRaw<(Guid NewId, string ExternalId)>(
            @"MERGE INTO ParentTable AS target
              USING @batch AS source
              ON target.ExternalId = source.ExternalId
              WHEN MATCHED THEN UPDATE SET target.Name = source.Name, ...
              WHEN NOT MATCHED THEN INSERT (Name, ...) VALUES (source.Name, ...)
              OUTPUT inserted.Id, source.ExternalId;",
            new SqlParameter("@batch", SqlDbType.Structured) 
            { 
                TypeName = "dbo.ParentTableType", 
                Value = parentBatch.ToDataTable() 
            }
        ).ToListAsync();

        // 把主键映射存入字典，用于子表关联
        var parentKeyDict = keyMapping.ToDictionary(k => k.ExternalId, k => k.NewId);

        // 处理子表数据，替换为新的父主键
        var childBatch = childData
            .Where(c => parentKeyDict.ContainsKey(c.ExternalParentId))
            .Select(c => new ChildTable 
            { 
                ParentId = parentKeyDict[c.ExternalParentId], 
                Name = c.Name,
                ExternalId = c.ExternalId
            })
            .Batch(1000);

        // 执行子表的MERGE操作
        foreach (var cb in childBatch)
        {
            await _dbContext.Database.ExecuteSqlRawAsync(
                @"MERGE INTO ChildTable AS target
                  USING @batch AS source
                  ON target.ExternalId = source.ExternalId
                  WHEN MATCHED THEN UPDATE SET target.Name = source.Name, ...
                  WHEN NOT MATCHED THEN INSERT (ParentId, Name, ...) VALUES (source.ParentId, source.Name, ...);",
                new SqlParameter("@batch", SqlDbType.Structured) 
                { 
                    TypeName = "dbo.ChildTableType", 
                    Value = cb.ToDataTable() 
                }
            );
        }
    }

    await transaction.CommitAsync();
}
catch (Exception ex)
{
    await transaction.RollbackAsync();
    throw;
}

优势： #

• 所有操作在单一连接的本地事务内，完全不会触发分布式事务
• 批量MERGE比单条Upsert性能提升数倍，分批次处理也能控制内存占用
• 通过OUTPUT子句精准获取插入的主键，完美解决子对象关联问题

注意点： #

• 提前创建对应的表值类型（ParentTableType、ChildTableType），和目标表结构匹配
• 关闭DbContext的变更跟踪（_dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;），避免批量操作后内存溢出
• 给目标表的ExternalId（业务唯一键）加索引，提升MERGE的匹配效率

方案二：SqlBulkCopy+临时表+MERGE+事务 #

如果数据量极大（300k+），SqlBulkCopy的导入速度是最快的，结合临时表和MERGE操作，既能保证性能，又能实现事务一致性。

具体步骤： #

1. 在SQL Server中创建临时表（或内存优化表），结构和目标表一致，包括业务唯一键
2. 用SqlBulkCopy把所有父表、子表数据批量导入到对应临时表（同一连接下）
3. 在本地事务内，按层级顺序执行MERGE，将临时表的数据同步到正式表，同时用OUTPUT获取主键映射，用于子表关联
4. 所有操作完成后提交事务，出错则回滚

示例代码片段： #

using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();

try
{
    // 批量导入父表数据到临时表
    using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
    bulkCopy.DestinationTableName = "#TempParent";
    await bulkCopy.WriteToServerAsync(parentData.ToDataTable());

    // MERGE父表，获取主键映射
    var keyMapping = new List<(Guid NewId, string ExternalId)>();
    using var cmd = new SqlCommand(@"
        MERGE INTO ParentTable AS target
        USING #TempParent AS source
        ON target.ExternalId = source.ExternalId
        WHEN MATCHED THEN UPDATE SET target.Name = source.Name, ...
        WHEN NOT MATCHED THEN INSERT (Name, ...) VALUES (source.Name, ...)
        OUTPUT inserted.Id, source.ExternalId;", connection, transaction);
    
    using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        keyMapping.Add((reader.GetGuid(0), reader.GetString(1)));
    }

    // 处理子表：更新临时表中的父主键，再MERGE到正式表
    var parentKeyDict = keyMapping.ToDictionary(k => k.ExternalId, k => k.NewId);
    // 这里可以用SqlCommand更新#TempChild的ParentId字段，或者重新导入处理后的子表数据

    // 执行子表MERGE...

    await transaction.CommitAsync();
}
catch
{
    transaction.Rollback();
    throw;
}

优势： #

• SqlBulkCopy的导入速度远高于普通批量插入，适合超大规模数据
• 所有操作在单一连接事务内，无分布式事务风险
• 临时表的使用能减少正式表的锁竞争，提升MERGE性能

注意点： #

• 内存优化表需要提前创建，且对数据类型有一定限制；普通临时表则无需提前创建，但要注意会话隔离
• 子表的临时表需要先关联父表的主键映射，再执行MERGE
• 给临时表的业务唯一键加索引，加速MERGE的匹配过程

方案三：Ado.Net单连接多命令+本地事务 #

如果不想依赖EF，直接用Ado.Net的底层操作，能获得最高的性能和控制权，同样避免分布式事务。

核心思路： #

• 打开一个SqlConnection，开启本地事务
• 创建多个SqlCommand，都关联这个连接和事务
• 按层级顺序执行批量MERGE命令，通过OUTPUT获取主键映射，处理子表数据
• 所有命令执行完成后提交事务，出错回滚

优势： #

• 完全脱离EF的开销，性能最优
• 精细控制每个SQL命令，适合复杂的业务逻辑
• 单一连接事务，无分布式事务问题

注意点： #

• SqlConnection不是线程安全的，异步操作要确保用await顺序执行，不要并发调用
• 手动管理命令的参数和结果读取，需要更严谨的代码编写

这些方案的核心都是复用单一连接，使用SQL Server本地事务，从根源上避免多连接触发分布式事务的问题，同时通过批量操作（MERGE、SqlBulkCopy）保证性能，通过OUTPUT子句解决插入主键的关联需求。

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