c# - How do I update multiple Entity models in one SQL statement? -

i had following:

list<message> unreadmessages = this.context.messages             .where( x =>                 x.ancestormessage.messageid == ancestormessageid &&                 x.read == false &&                 x.sentto.id == userid ).tolist();  foreach(var unreadmessage in unreadmessages) {     unreadmessage.read = true; }  this.context.savechanges(); 

but there must way of doing without having 2 sql queries, 1 selecting items, , 1 updating list.

how do this?

current idiomatic support in ef

as far know, there no direct support "bulk updates" yet in entity framework (there has been ongoing discussion bulk operation support while though, , included @ point).

(why) want this?

it clear operation that, in native sql, can achieved in single statement, , provides significant advantages on approach followed in question. using single sql statement, small amount of i/o required between client , db server, , statement can executed and optimized db server. no need transfer , iterate through potentially large result set client side, update 1 or 2 fields , send other way.

how

so although not directly supported ef, still possible this, using 1 of 2 approaches.

option a. handcode sql update statement

this simple approach, not require other tools/packages , can performed async well:

var sql = "update table x set fielda = @fielda fieldb = @fieldb"; var parameters = new sqlparameter[] { ..., ... }; int result = db.database.executesqlcommand(sql, parameters); 

or

int result = await db.database.executesqlcommandasync(sql, parameters); 

the obvious downside is, breaking nice linqy paradigm , having handcode sql (possibly more 1 target sql dialect).

option b. use 1 of ef extension/utility packages

since while, number of open source nuget packages available offer specific extensions ef. number of them provide nice "linqy" way issue single update sql statement server. 2 examples are:

1. entity framework extended library allows performing bulk update using statement like:

   context.messages.update(      x => x.read == false && x.sentto.id == userid,      x => new message { read = true });

   it available on github

2. entityframework.utilities allows performing bulk update using statement like:

   efbatchoperation    .for(context, context.messages)    .where(x => x.read == false && x.sentto.id == userid)    .update(x => x.read, x => x.read = true);

   it available on github

and there other packages , libraries out there provide similar support.