设置WHERE条件以在ExecuteSqlCommand()中使用Ids.Contains()

问题描述

我正在使用Entity Framework，并且想要执行批量更新.加载每一行，更新这些行然后将它们保存回数据库中，效率太低.

I'm using Entity Framework and I want to perform a bulk update. It is way too inefficient to load each row, update those rows, and then save them back to the database.

所以我更喜欢使用 DbContext.Database.ExecuteSqlCommand().但是，如何使用此方法用ID列表中包含的ID更新所有这些行?

So I'd prefer to use DbContext.Database.ExecuteSqlCommand(). But how can I use this method to update all those rows with an ID contained in my list of IDs?

这是我到目前为止所拥有的.

Here's what I have so far.

IEnumerable<int> Ids;
DbContext.Database.ExecuteSqlCommand("UPDATE Messages SET Viewed = 1 WHERE Id IN (@list)", Ids);

我意识到我可以使用正确的查询来手动构建字符串，但是我更喜欢按照通常的建议传递参数.

I realize I could manually build a string with the correct query, but I'd prefer to pass my parameters as is generally recommended.

有什么办法可以做到这一点?

Is there any way to make that happen?

推荐答案

您仍然可以构建参数并将其包含在参数化查询中.

You can still build the parameters and include them in the parameterized query.

查询生成时看起来像这样

The query would look something like this when generated

UPDATE Messages SET Viewed = 1 WHERE Id IN (@p0, @p1, @p2, ..., @pn)

如此

IEnumerable<int> Ids;

然后

var parameters = Ids.Select((id, index) => new SqlParameter(string.Format("@p{0}", index), id));
var parameterNames = string.Join(", ", parameters.Select(p => p.ParameterName));
var query = string.Format("UPDATE Messages SET Viewed = 1 WHERE Id IN ({0})", parameterNames);

int affected = DbContext.Database.ExecuteSqlCommand(query, parameters.ToArray());

这篇关于设置WHERE条件以在ExecuteSqlCommand()中使用Ids.Contains()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！