有关linq批量插入及更新的问题

Question

有关linq批量插入及更新的问题
现有如下数据库表

教授	学生	得分	时间
张教授	小王	78	2024-11-01
李教授	小谢	75	2024-11-01
李教授	小元	82	2024-11-01
张教授	小谢	86	2024-11-01
李教授	小王	89	2024-11-01

现在要插入数据：
{
{张教授, 小钱, 90, 2024-11-02},
{张教授, 小赵, 90, 2024-11-02},
{张教授, 小孙, 90, 2024-11-02},
{张教授, 小王, 90, 2024-11-02}
}
由于<张教授-小王>这一组合已经在数据库表中存在，则更新这一条数据的得分和时间；其他未在数据库中存在的则直接插入。
现在的思路是：
循环要插入的数据，判断当前的数据<教授-学生>是否已经存在，若存在则更新；若不存在则加入待批量插入的list。循环结束，使用linq的InsertAllOnSubmit()方法将待批量插入的list提交到数据库表。
但是当数据量有百万条的时候，上述的算法思路处理起来极慢，大家有无更好的方法？

Answer 1

本答案参考 多次询问 ChatGPT-3.5 后选择的最佳回答，若是满意，还请采纳，谢谢

1. 使用SQL Server的MERGE语句（结合Linq to SQL）
   • 原理
     • SQL Server的MERGE语句可以在一个操作中实现根据条件进行插入或更新。在Linq to SQL中，可以通过执行原生的SQL语句来利用MERGE的功能。
   • 示例代码（假设使用Linq to SQL的DataContext类名为YourDataContext）

using (YourDataContext context = new YourDataContext())
{
    var newData = new[]
    {
        new { Professor = "张教授", Student = "小钱", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小赵", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小孙", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小王", Score = 90, Time = new DateTime(2024, 11, 02) }
    };

    string mergeSql = @"
MERGE INTO YourTable AS target
USING (
    VALUES
";
    foreach (var item in newData)
    {
        mergeSql += $"('{item.Professor}', '{item.Student}', {item.Score}, '{item.Time:yyyy - MM - dd}'),";
    }
    mergeSql = mergeSql.TrimEnd(',') + @"
) AS source (Professor, Student, Score, Time)
ON target.Professor = source.Professor AND target.Student = source.Student
WHEN MATCHED THEN
    UPDATE SET target.Score = source.Score, target.Time = source.Time
WHEN NOT MATCHED THEN
    INSERT (Professor, Student, Score, Time) VALUES (source.Professor, source.Student, source.Score, source.Time);";

    context.ExecuteCommand(mergeSql);
    context.SubmitChanges();
}

• 优点
  • 这种方式只需要一次数据库交互，相比于循环判断每条数据的方式，大大减少了数据库操作的开销。对于大量数据（百万条级别），性能提升会非常显著。

1. 利用临时表（如果数据库支持）
   • 原理
     • 先将需要插入或更新的数据插入到一个临时表中，然后通过一个连接操作和条件判断来在原始表中进行更新和插入操作。
   • 示例代码（假设SQL Server支持临时表）

using (YourDataContext context = new YourDataContext())
{
    var newData = new[]
    {
        new { Professor = "张教授", Student = "小钱", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小赵", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小孙", Score = 90, Time = new DateTime(2024, 11, 02) },
        new { Professor = "张教授", Student = "小王", Score = 90, Time = new DateTime(2024, 11, 02) }
    };

    // 创建临时表并插入数据
    string createTempTableSql = @"
CREATE TABLE #TempTable (
    Professor NVARCHAR(50),
    Student NVARCHAR(50),
    Score INT,
    Time DATE
);
INSERT INTO #TempTable (Professor, Student, Score, Time)
VALUES ";
    foreach (var item in newData)
    {
        createTempTableSql += $"('{item.Professor}', '{item.Student}', {item.Score}, '{item.Time:yyyy - MM - dd}'),";
    }
    createTempTableSql = createTempTableSql.TrimEnd(',');
    context.ExecuteCommand(createTempTableSql);

    // 进行更新和插入操作
    string mergeWithTempTableSql = @"
UPDATE t
SET t.Score = tt.Score, t.Time = tt.Time
FROM YourTable t
JOIN #TempTable tt ON t.Professor = tt.Professor AND t.Student = tt.Student;

INSERT INTO YourTable (Professor, Student, Score, Time)
SELECT tt.Professor, tt.Student, tt.Score, tt.Time
FROM #TempTable tt
LEFT JOIN YourTable t ON tt.Professor = t.Professor AND tt.Student = t.Student
WHERE t.Professor IS NULL;";
    context.ExecuteCommand(mergeWithTempTableSql);
    context.SubmitChanges();
}

• 优点
  • 临时表可以利用数据库的索引和优化机制，相比于在应用层进行循环操作更高效。不过，创建和管理临时表需要一些额外的数据库资源和权限。