Setting WHERE condition to use Ids.Contains() in ExecuteSqlCommand()

Question

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 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?

I'd suggest looking into how Table-Valued Parameters work (though TVPs are used with stored procedures, not written commands). There should be a bunch of questions about this. e.g. here. Outside of that, I think you'd have to pass the list as a string and split it in some way (e.g. a string split function) or use a LIKE statement. There's also a bunch of questions about this, too, so it should be easy to find something that meets your requirements. — ZLK
– ZLK, Commented May 14, 2017 at 23:17
@ZLK: Thanks but I'm really not sure how Table-Valued Parameters can be used here. — Jonathan Wood
– Jonathan Wood, Commented May 14, 2017 at 23:25
The question I linked has a link to a short example of how you might achieve this: codeproject.com/Articles/22392/… (I don't believe there's a way to do it with a simple executesqlcommand statement, but I may be wrong). Essentially, you'd need to create a table type (with an INT column), create a stored procedure that takes a TVP of that type as a parameter, then execute the stored procedure by passing a data table with your values in it. — ZLK
– ZLK, Commented May 14, 2017 at 23:37
What's the error you get if you run your code now? You didn't specify (although I can presume it why it fails because there may not be a serializer for List or IEnumerable into one of the DbTypes in DbParameter) — Mark C.
– Mark C., Commented May 14, 2017 at 23:39
@MarkC.: To be honest, I didn't even run it. $500.00 says there's no way it would parse that as I want. Well okay, I just tried it. It didn't seem to raise an exception, but it didn't seem to do anything either. — Jonathan Wood
– Jonathan Wood, Commented May 14, 2017 at 23:42

Nkosi · Accepted Answer · 2017-05-15 00:23:34Z

6

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

edited May 15, 2017 at 0:23

answered May 14, 2017 at 23:39

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Mark C. Over a year ago

I think this is a good answer but not the answer OP was looking for.

Jonathan Wood Over a year ago

Interesting suggestion. Might be the best that you can do with ExecuteSqlCommand.

Nkosi Over a year ago

That is currently how I do it with EF. And I have searched for you requested for a really long time. I settled on this format when I saw it in some MS documentation.

Jonathan Wood Over a year ago

@Nkosi: Obviously, there comes a point where it's so much more effort that it's not really any more efficient. But it looks like this would still be more efficient.

Nkosi Over a year ago

Yes. I have this in production code. The second parameter is a param object[]

|

Dmitri Usanov · Accepted Answer · 2017-05-14 23:44:20Z

1

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/

answered May 14, 2017 at 23:44

Dmitri Usanov

3785 silver badges11 bronze badges

Collectives™ on Stack Overflow

Setting WHERE condition to use Ids.Contains() in ExecuteSqlCommand()

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related