C# EF Core中基于列表高效批量插入更新的实现（含SqlBulkCopy）

问题背景 #

现有两个数据列表：

• List A（源数据，包含需同步的最新信息）：

ID Name Age Address 
---------------------
1  abc  5   address11
2  de   67  address12
3  ac   55  address13
4  ffc  2   address14
5  cg   7   address15

• List B（目标数据，需被更新/补充）：

ID Name Age Address    DateUpdated DatedAdded
----------------------------------------------
1  abc  5   address11  1/1/2023    1/1/2023
2  cg   7   address84  2/2/2023    1/1/2023 

需基于List A对List B执行**更新（ID匹配时同步最新字段并更新DateUpdated）和插入（ID不存在时新增记录，DateUpdated和DatedAdded设为当前日期）**操作，最终得到List C：

ID Name Age Address    DateUpdated DatedAdded
---------------------------------------------
1  abc   5   address11  6/1/2023    1/1/2023
2  cg    7   address15  6/1/2023    1/1/2023 
3  de   67   address12  6/1/2023    6/1/2023 
4  ac   55   address13  6/1/2023    6/1/2023
5  ffc   2   address14  6/1/2023    6/1/2023 

当前采用foreach遍历List A逐条执行SQL插入/更新的方式，500条数据耗时约1分钟，5万条数据效率极低，需优化方案。

优化方案 #

1. 避免O(n*n)迭代：用字典实现高效匹配 #

如果需要先在内存中处理数据（比如字段验证），不要用嵌套循环遍历List A和List B，而是把List B转换成以ID为键的字典，这样单条记录的查找时间复杂度从O(n)降到O(1)，整体操作复杂度为O(n+m)（n是List A长度，m是List B长度）。

示例代码（C#）：

// 假设实体类定义
public class User
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Address { get; set; }
    public DateTime DateUpdated { get; set; }
    public DateTime DatedAdded { get; set; }
}

// 将List B转为字典，ID作为键
var listBDict = listB.ToDictionary(u => u.ID);

// 遍历List A，快速匹配List B中的记录
foreach (var aItem in listA)
{
    if (listBDict.TryGetValue(aItem.ID, out var bItem))
    {
        // 存在匹配ID，更新字段（仅内存操作）
        bItem.Address = aItem.Address;
        bItem.DateUpdated = DateTime.Now;
    }
    else
    {
        // 不存在匹配ID，创建新记录
        listBDict[aItem.ID] = new User
        {
            ID = aItem.ID,
            Name = aItem.Name,
            Age = aItem.Age,
            Address = aItem.Address,
            DateUpdated = DateTime.Now,
            DatedAdded = DateTime.Now
        };
    }
}

// 此时listBDict.values即为内存中的List C
var listC = listBDict.Values.ToList();

2. 用SqlBulkCopy+MERGE实现高效数据库操作 #

逐条执行SQL是效率低的核心原因，最优方案是用SqlBulkCopy批量导入数据到临时表，再通过SQL的MERGE语句一次性完成更新和插入，大幅减少数据库交互次数。

具体步骤：

1. 创建与目标表结构匹配的临时表；
2. 用SqlBulkCopy将List A批量写入临时表；
3. 执行MERGE语句，对比临时表和目标表的ID，完成更新/插入；
4. 清理临时表。

示例代码（C# + SQL）：

第一步：创建临时表

CREATE TABLE #TempUsers (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT,
    Address NVARCHAR(200)
)

第二步：SqlBulkCopy批量导入List A

using (var conn = new SqlConnection("你的数据库连接字符串"))
{
    conn.Open();

    // 创建临时表
    using (var createTableCmd = new SqlCommand("CREATE TABLE #TempUsers (ID INT PRIMARY KEY, Name NVARCHAR(50), Age INT, Address NVARCHAR(200))", conn))
    {
        createTableCmd.ExecuteNonQuery();
    }

    // 配置SqlBulkCopy
    using (var bulkCopy = new SqlBulkCopy(conn))
    {
        bulkCopy.DestinationTableName = "#TempUsers";
        // 字段映射（字段名一致可省略）
        bulkCopy.ColumnMappings.Add("ID", "ID");
        bulkCopy.ColumnMappings.Add("Name", "Name");
        bulkCopy.ColumnMappings.Add("Age", "Age");
        bulkCopy.ColumnMappings.Add("Address", "Address");

        // 将List A转为DataTable
        var dataTable = new DataTable();
        dataTable.Columns.Add("ID", typeof(int));
        dataTable.Columns.Add("Name", typeof(string));
        dataTable.Columns.Add("Age", typeof(int));
        dataTable.Columns.Add("Address", typeof(string));

        foreach (var item in listA)
        {
            dataTable.Rows.Add(item.ID, item.Name, item.Age, item.Address);
        }

        // 批量写入临时表
        bulkCopy.WriteToServer(dataTable);
    }

    // 第三步：执行MERGE完成更新和插入
    var mergeSql = @"
        MERGE INTO TargetUsers AS Target
        USING #TempUsers AS Source
        ON Target.ID = Source.ID
        WHEN MATCHED THEN
            UPDATE SET 
                Target.Address = Source.Address,
                Target.DateUpdated = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (ID, Name, Age, Address, DateUpdated, DatedAdded)
            VALUES (Source.ID, Source.Name, Source.Age, Source.Address, GETDATE(), GETDATE());
    ";

    using (var mergeCmd = new SqlCommand(mergeSql, conn))
    {
        mergeCmd.ExecuteNonQuery();
    }

    // 第四步：清理临时表
    using (var dropTableCmd = new SqlCommand("DROP TABLE #TempUsers", conn))
    {
        dropTableCmd.ExecuteNonQuery();
    }
}

说明：

• SqlBulkCopy是.NET原生的批量导入工具，性能远高于逐条插入；
• MERGE是SQL Server原生语句，一次操作即可完成更新和插入，避免多次数据库交互；
• 若数据量极大（百万级），可将List A分批次导入临时表后执行MERGE，避免内存溢出。

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