基于条件更新多个字段的更简洁的方法

Question

目前我正在编写一个速度非常重要的应用程序。应用程序处理了很多记录，最后，我想更新那些被处理的记录。测试版有以下运行良好的逻辑:

string listOfIds = string.Join(", ", listOfIds.Select(q=> q.ID));
_db.ExecuteCommand(string.Format("update table set processed = 1 where id in ({1})", listofIds));

其中listOfIds包含已处理的所有id的列表。这非常有效，但现在我需要根据过程中发生的情况将"processed"设置为不同的值。我不能只设processed = 1，它是有条件的。listOfIds的定义是这样的

List<CustomClass> listOfIds = new List<CustomClass>();
class CustomClass
{
    public int Status { get; set; }
    public int ID { get; set; }
}

我的解决方案如下。我没有将所有记录添加到listOfIds中，而是必须将"status"的每个可能值添加到单独的列表中。这样的:

List<CustomClass> listOfSuccessfulIds = new List<CustomClass>();
List<CustomClass> listOfFailedIds = new List<CustomClass>();
List<CustomClass> listOfSomethingElseIds = new List<CustomClass>();
...
_db.ExecuteCommand(string.Format("update table set processed = 1 where id in ({1})", listOfSuccessfulIds ));
_db.ExecuteCommand(string.Format("update table set processed = 2 where id in ({1})", listOfFailedIds ));
_db.ExecuteCommand(string.Format("update table set processed = 3 where id in ({1})", listOfSomethingElseIds ));

这当然是功能性的，但是看起来很乱。特别是如果有大量的"已处理"的可能性，我觉得，一如既往，有一个更好的方法来处理它。

Answer 1

如果没有太多不同的值，可以使用case语句:

List<CustomClass> toUpdate = ...
var query = string.Format(@"
    UPDATE table 
    SET processed = CASE {0} ELSE 1/0 END
    WHERE id IN ({1})
    ",
    string.Join(
        " ",
        toUpdate.GroupBy(c => c.Status)
            .Select(g => string.Format("WHEN id IN ({0}) THEN {1}", g.Key, string.Join(",", g.Select(c => c.ID))
    ),
    string.Join(",", toUpdate.Select(c => c.ID))
);

将给出如下查询:

UPDATE table
SET processed = CASE WHEN id IN (1, 2) THEN 1 WHEN id IN (3, 4) THEN 2 ELSE 1/0 END
WHERE id IN (1, 2, 3, 4)

如果您有大量不同的id，您可能最好生成子查询并连接到它们:

var subQuery = string.Join(
    " UNION ALL ", 
    toUpdate.Select(c => string.Format("SELECT {0} AS id, {1} AS status", c.ID, c.Status)
);

然后执行如下查询:

UPDATE t
SET t.processed = q.status
FROM table t
JOIN ({subQuery}) q
    ON q.id = t.id

最后，如果这仍然产生太多的文本，你可以先将子查询所代表的"table"插入到临时表中(例如使用SqlBulkCopy)，然后执行上面的查询连接到临时表，而不是SELECT…合并所有子查询