Update multiple records with Entity Framwork

Question

I am wondering what the best way to update multiple records with Entity Framework is. This is how I normally do it, and it does work:

 private static void SetNcprpCodesAsComplete(string[] ncprpCodes)
 {
 using (var ent = new Data.Entities())
 {
 var query = from ba in ent.BuildingAssessments
 where ncprpCodes.Contains(ba.NcprpCode)
 select ba.TabletAssessment;
 foreach (var ta in query) ta.Complete = true;
 ent.SaveChanges();
 }
 }

This query should also work (is one better than the other)

 var query = from ta in ent.TabletAssessments
 where ncprpCodes.Contains(ta.BuildingAssessment.NcprpCode)
 select ta;

Another way could be looping through the string[], attaching and updating. Is it possible to attach when doing a multi table query like this?

Answer 1

This is the usual way how to update properties in EF, and in your case, I'd say it seems the only proper way. You can surely attach and update - but then you'll loose all other properties in your entity. To sum up - if you'd need to update one property in entities, you'll need to do SELECT (your query), modify entities (foreach) and then do UPDATE (SaveChanges).

• JonathanPeel: Yes, this is the best I have been able to come up with. It is not quite as elegant as a SQL update, but maybe thinking in terms of SQL when using EF is my problem.
• Robert Goldwein: Yes, I can understand that :) It's not specific to EF, but to ORM in general - you don't work with rows and tables, you work with objects (entities) and their properties. Frankly, you can mix both ways, but only when you exactly know why and what you're doing, what impact such update would have on your context(s) and usually in very specific scenarios.

Answer 2

If you have X rows, you're going to issue X update statements to the database behind the scenes. You're also going to be loading up all those records in the first place. This could be a problem if you have say a million rows to update. So if performance becomes a problem, you could go the SQL route in this case. Especially since it seems the results are only being used for this update in your example

string sql = @"update MyTable set MyField={0} where MyCrit={1}";
List<Object> sqlParamsList = new List<object>();
sqlParamsList.Add(value1);
sqlParamsList.Add(value2);
ent.Database.ExecuteSqlCommand(sql, sqlParamsList.ToArray());

Note that this code is not vulnerable to sql injection attacks. I wanted to show the syntax for sql parameters using an object array.

I had a situation like this and using the method above got an order of magnitude performance improvement over the non-SQL way with only ~2000 records.