从LINQ查询批量更新? [英] Batch Update From a LINQ Query?

问题描述

现在，我有一个应用程序，可以一次处理一个数据库中的一条记录.

Right now I have an application that processes records in a DB one record at a time.

var records = from q in db.table 
              join j in db.OtherTable 
              where q.processed == 0 //Sample 
foreach(var record in records)
{
    //Do some stuff....

    db.ExecuteCommand("Update table set record.processed = 1 where id = record.ID");
}

在foreach的末尾，它更新记录为已处理.显然，因为LINQ在更新方面做得不好，所以我只执行了executeCommand.现在，要求该应用程序快速运行.现在，完成一些工作"花了不到一秒钟的时间遍历了数千行，它甚至使用SqlBulkCopy将一条记录插入到表中.

At the end of the foreach it updates the record as processed. Obviously because LINQ does not do a great job at updates I just do an executeCommand. Now it is a requirement that this application run quickly. Right now that "Do some stuff" takes less than a second running through thousands of rows, it even inserts a record into a table using SqlBulkCopy.

问题在于，当我放入代码来更新记录已处理时，它会大大降低记录的速度.它必须更新数据库数千次.在没有太多速度损失的情况下，更新所有我选择的记录(和仅我选择的记录)的更快方法是什么. (注意:如果整个过程都完成了(在foreach之后)，那需要一点时间就可以了.

The problem is that when I put in the code to update that the record is processed, it slows it down significantly. It has to update the database thousands of times. What would be a faster way to update all of the records that I selected (and only the records I selected) without too much of a loss of speed. (Note: it's okay if after the whole process is done (after the foreach) if that it takes a bit of time).

推荐答案

您可以基于列表中的ID生成IN语句:

You could generate an IN statement based on the IDs in your list:

string ids = string.Join(", ", records.Select(r => r.RecordID));
string sql = string.Format("Update table set record.processed = 1 WHERE RecordID IN ({0}) ", ids)
db.ExecuteCommand(sql);

这篇关于从LINQ查询批量更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！