updating multiple records in database with linq to sql without loop, is it possible?

Question

ok, let say i want to update multiple rows in database at once, without looping, if we do it with loop, it might look like this :

var products = (from prod in context.Products
               where prod.ProductCategoryID == myProductID
               select prod).ToList();


// modify each object
foreach (var product in products)
{
    product.PhoneNumber = ???;
    product.Name = ???;
    product.Address = ???;
}


context.SubmitChanges();

is there better way to do this? without doing any loop? consider me as a beginner on linq, so if there is some better way, it would be great if you provide me some example, thanks in advance.

Parallel.For may be what your looking for - http://msdn.microsoft.com/en-us/library/system.threading.tasks.parallel.for.aspx and http://msdn.microsoft.com/en-us/library/dd460713.aspx — Sayse, May 21 '13 at 07:04
Habib provides the answer. The only improvement I see is removing the ToList but that is off topic. — Pleun, May 22 '13 at 19:19
is n't everyone free to give their opinion? to help other have a better understanding, i wonder. — NomNomNom, May 23 '13 at 05:44

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

LINQ is for quering records. Your current foreach loop is more readable and if you try other approach then it will use loop internally.

You can see this answer which modifies the collection using LINQ, but it is not the recommended way.

edited May 23 '17 at 11:51

Community

1
1

answered May 21 '13 at 06:49

Habib

219,104
29
407
436

In what i have heard about, i guess EF or Linq to SQL does not support bulk operations. Please suggest. – Saravanan May 21 '13 at 06:54
1

@saravanan, that is right. For example in LINQ to SQL we have `InsertAllOnSubmit` but internally it uses a loop. – Habib May 21 '13 at 06:56
Thank you very much for sharing this info. I have learnt this now. – Saravanan May 21 '13 at 07:05
hello @habib, thx for your response, well what i get from your explaination is most likely linq will always do loop? – NomNomNom May 21 '13 at 07:22
@HendraLim, yes LINQ internally uses loop for most of the queries. – Habib May 21 '13 at 07:24

user1587368 · Answer 2 · 2013-05-21T07:03:56.307

You can use that line:

var products = (from prod in context.Products
                where prod.ProductCategoryID == myProductID
                select new Product(prod.PhoneNumber.ToLower(), prod.Name.ToUpper(), prod.Address.Trim()).ToList();

If you wanna change your products directly in your context object : make a specific function in Products class only. It will simply well designed.

 (from prod in context.Products
  where prod.ProductCategoryID == myProductID
  select prod.MakeChange(myPhoneNumber, myName, myAddress);

As you can call that line of code, prod should be changed along the LINQ query.

updating multiple records in database with linq to sql without loop, is it possible?

2 Answers2

Linked