Update records using LINQ

Question

I need to set a value in a table for a subset of rows. In SQL, I would do this:

UPDATE dbo.Person SET is_default = 0 WHERE person_id = 5

Is there a way to do this in LINQ?

I currently use the:

var result = (from p in Context.People....)

notation.

Is there an update method I can use? Or do I have to get all the records, then update them one-by-one in a Foreach?

Is this the most efficient way, if this is even possible?

(from p in Context.person_account_portfolio where p.person_id == personId select p)
   .ToList()
   .ForEach(
       x =>
       x.is_default =
       false);

Answer 1

I assume person_id is the primary key of Person table, so here's how you update a single record:

Person result = (from p in Context.Persons
              where p.person_id == 5
              select p).SingleOrDefault();

result.is_default = false;

Context.SaveChanges();

and here's how you update multiple records:

List<Person> results = (from p in Context.Persons
                        where .... // add where condition here
                        select p).ToList();

foreach (Person p in results)
{
    p.is_default = false;
}

Context.SaveChanges();

Answer 2

This worked best.

(from p in Context.person_account_portfolio 
 where p.person_id == personId select p).ToList()
                                        .ForEach(x => x.is_default = false);

Context.SaveChanges();

Answer 3

Just as an addition to the accepted answer, you might find your code looking more consistent when using the LINQ method syntax:

Context.person_account_portfolio
.Where(p => person_id == personId)
.ToList()
.ForEach(x => x.is_default = false);

.ToList() is neccessary because .ForEach() is defined only on List<T>, not on IEnumerable<T>. Just be aware .ToList() is going to execute the query and load ALL matching rows from database before executing the loop.

Answer 4

You have two options as far as I know:

1. Perform your query, iterate over it to modify the entities, then call SaveChanges().
2. Execute a SQL command like you mentioned at the top of your question. To see how to do this, take a look at this page.

If you use option 2, you're losing some of the abstraction that the Entity Framework gives you, but if you need to perform a very large update, this might be the best choice for performance reasons.

Answer 5

Yes. You can use foreach to update the records in linq.There is no performance degrade.

you can verify that the standard Where operator is implemented using the yield construct introduced in C# 2.0.

The use of yield has an interesting benefit which is that the query is not actually evaluated until it is iterated over, either with a foreach statement or by manually using the underlying GetEnumerator and MoveNext methods

For instance,

var query = db.Customers.Where (c => c.Name.StartsWith ("A"));
query = query.Where (c => c.Purchases.Count() >= 2);
var result = query.Select (c => c.Name);

foreach (string name in result)   // Only now is the query executed!
   Console.WriteLine (name);

Exceptional operators are: First, ElementAt, Sum, Average, All, Any, ToArray and ToList force immediate query evaluation.

So no need to scare to use foreach for update the linq result.

In your case code sample given below will be useful to update many properties,

 var persons = (from p in Context.person_account_portfolio where p.person_name == personName select p);

//TO update using foreach

foreach(var person in persons)
{
//update property values
}  

I hope it helps...

Answer 6

Yes, you have to get all records, update them and then call SaveChanges.

Answer 7

Strangely, for me it's SubmitChanges as opposed to SaveChanges:

    foreach (var item in w)
    {
        if (Convert.ToInt32(e.CommandArgument) == item.ID)
        {
            item.Sort = 1;
        }
        else
        {
            item.Sort = null;
        }
        db.SubmitChanges();            
    }