Setting WHERE Condition To Use Ids.Contains() In ExecuteSqlCommand()

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. So I'd prefe

Solution 1:

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)

So given

IEnumerable<int> Ids;

Then

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());

Solution 2:

Instead of generating query string with exact values, you can generate query string with as many parameters as you have. So you'll get smth like:

DbContext.Database.ExecuteSqlCommand("UPDATE Messages SET Viewed = 1 WHERE Id IN (@p0,@p1,@p2,...,@pN)", Ids);

by smth like this:

var paramsDef = string.Concat(Ids.Select(x=>$"{(Ids.IndexOf(x) > 0 ? "," : "")}p{Ids.IndexOf(x)}"));            
DbContext.Database.ExecuteSqlCommand($"UPDATE Messages SET Viewed = 1 WHERE Id IN {paramsDef}", Ids);

Some links I found people doing similar with SqlCommand: http://www.svenbit.com/2014/08/using-sqlparameter-with-sqls-in-clause-in-csharp/ http://nodogmablog.bryanhogan.net/2016/01/parameterize-sql-where-in-clause-c/