current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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).

share|improve this answer
    
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. –  JonathanPeel Jul 2 '14 at 10:12
1  
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. –  Robert Goldwein Jul 2 '14 at 14:27
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.