3

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?

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • 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](http://stackoverflow.com/questions/1030848/how-to-pass-user-defined-table-type-as-stored-procedured-parameter-in-c-sharp). 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 May 14 '17 at 23:17
  • @ZLK: Thanks but I'm really not sure how Table-Valued Parameters can be used here. – Jonathan Wood May 14 '17 at 23:25
  • The question I linked has a link to a short example of how you might achieve this: https://www.codeproject.com/Articles/22392/SQL-Server-Table-Valued-Parameters (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 May 14 '17 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 `DbType`s in `DbParameter`) – Mark C. May 14 '17 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 May 14 '17 at 23:42
  • It had to of done something? Can you enabling logging and debug it? – Mark C. May 14 '17 at 23:44
  • @MarkC.: I don't seem to have the needed permissions to do this on the server. I could obviously configure another database to test, but really I don't see how that cold work and it doesn't. I really don't think that is supported. – Jonathan Wood May 14 '17 at 23:52
  • Ah, gotcha. I was referring to the `Log` property through Entity Framework. It's odd that EF wouldn't support this but Dapper does. – Mark C. May 14 '17 at 23:53

2 Answers2

5

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());
Nkosi
  • 235,767
  • 35
  • 427
  • 472
  • 1
    I think this is a good answer but not the answer OP was looking for. – Mark C. May 14 '17 at 23:42
  • Interesting suggestion. Might be the best that you can do with `ExecuteSqlCommand`. – Jonathan Wood May 14 '17 at 23:49
  • 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. – Nkosi May 14 '17 at 23:51
  • @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. – Jonathan Wood May 14 '17 at 23:53
  • @Nkosi: So far, I'm getting an exception `System.ArgumentException: 'No mapping exists from object type System.Int32[] to a known managed provider native type.'`. Did you try the code? – Jonathan Wood May 15 '17 at 00:02
  • Yes. I have this in production code. The second parameter is a `param object[]` – Nkosi May 15 '17 at 00:04
  • @Nkosi: Looks like you had the `(i, id)` parameters swapped, but I'm still getting the same error. Wonder what I have different. – Jonathan Wood May 15 '17 at 00:08
  • @JonathanWood hold on let me go pull up that project and take a look. – Nkosi May 15 '17 at 00:10
  • @Nkosi: Any luck? I can't see what I'm doing that is different. It doesn't seem to accept an array for the `params` argument. – Jonathan Wood May 15 '17 at 00:37
  • @JonathanWood I checked and it works for me. Also found another answer here where they do basically the same thing http://stackoverflow.com/a/25497384/5233410. I already had an up-vote on that answer so I guess I would have come across this in my research – Nkosi May 15 '17 at 00:41
  • @JonathanWood No I was referring to the article I link to in my last comment. – Nkosi May 15 '17 at 00:44
  • @Nkosi: Oh, you updated your answer to create an array of `SqlParameter`. That's the part I was missing (and what the other question you linked to does.) – Jonathan Wood May 15 '17 at 00:46
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/

Dmitri Usanov
  • 348
  • 4
  • 11