Updating multiple rows Linq vs SQL

Question

Some time ago I wrote a piece of code to update multiple rows in a database table. The code was like this

var db = new MyDataContext();
db.Execute("UPDATE Details SET IsActive = 0 WHERE MasterId = 1");

Then the other day when I got the latest version of the file I saw that somebody changed the code to something like this

var details = from d in db.details where d.MasterId == 1 select d;
foreach (var detail in details)
  detail.IsActive = false;
db.SubmitChanges();

So my question is: What is the better way to update multiple rows? Using Linq or SQL?

Answer 1

I'd say it depends on whether efficiency or abstraction from the database is more important to you. The SQL way is going to create a dependency in your code that is harder to track, but is more efficient. The LINQ sample quoted removes the dependency on hand-coded SQL but involves at least 2 queries and some server side processing.

Answer 2

Check the approach used in this article:

• Batch Updates and Deletes with LINQ to SQL

Answer 3

PLINQO has implemented the batch updates and deletes and each entity generated by plinqo can use the batch update and delete operations.

context.Task.Update(t => t.Id == 1, t2 => new Task {StatusId = 2});

This will perform an Update Task Set StatusId = 2 Where Id = 1

Answer 4

for (int i = 0; i < pListOrderDetail.Count; i++) { for (int j = 0; j < stempdata.Count; j++) { pListOrderDetail[i].OrderID = pOrderID; pListOrderDetail[i].ProductID = stempdata[j].pProductID; pListOrderDetail[i].Quantity = stempdata[j].pQuantity; pListOrderDetail[i].UnitPrice = stempdata[j].pUnitPrice; pListOrderDetail[i].Discount = stempdata[j].pDiscount; db.SubmitChanges(); break; } continue; }