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?

Fahad Rehman · Accepted Answer · 2017-07-06 19:16:55Z

14

Check the approach used in this article:

Batch Updates and Deletes with LINQ to SQL

edited Jul 6, 2017 at 19:16

Fahad Rehman

1,19913 silver badges25 bronze badges

answered Dec 8, 2008 at 0:50

Christian C. Salvadó

831k185 gold badges929 silver badges845 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pavel Yermalovich Over a year ago

Here is the working link: terryaney.wordpress.com/2008/04/14/…

tvanfosson · Accepted Answer · 2008-12-08 00:42:12Z

9

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.

answered Dec 8, 2008 at 0:42

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

1 Comment

Murph Over a year ago

I won't add an extra answer - my thought is that if you wanted an answer then it would have to be SQL for a set operation i.e. where you want to affect a lot of rows with a single statement but as you say its actually a tradeoff, what compromise do you want to make.

Druid · Accepted Answer · 2012-06-27 14:28:51Z

4

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

edited Jun 27, 2012 at 14:28

Druid

6,4634 gold badges44 silver badges57 bronze badges

answered Nov 11, 2009 at 21:49

Shannon Davidson

2162 silver badges3 bronze badges

Comments

Rup · Accepted Answer · 2011-01-27 14:40:51Z

1

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;
}

edited Jan 27, 2011 at 14:40

Rup

34.6k9 gold badges86 silver badges119 bronze badges

answered Jul 28, 2010 at 17:48

knowwebapp.com

954 silver badges16 bronze badges

Comments

Rory MacLeod · Accepted Answer · 2012-01-11 11:21:35Z

The Linq-to-SQL version is going to SELECT every row from the details table that matches the query, pull them into memory, and create objects for them. Then when it applies the updates, it will use a separate UPDATE statement for each object, and will (by default) include a WHERE clause that checks the value of every column to make sure it hasn't changed since you did the SELECT. If the row has changed, Linq will throw an exception.

The Linq version of the code is going to be MUCH slower (think 100x or 1000x slower), and expose you to additional exceptions.

The SQL version might be a bit harder to maintain as you refactor code in the future, but it will make a world of difference to your database.

I generally feel that Linq-to-SQL is great for selects and individual updates, and maybe for small batches the database would be fast enough that the extra queries wouldn't a problem, but I would have to think carefully before I used Linq for large batches, or on a web server.

Collectives™ on Stack Overflow

Updating multiple rows Linq vs SQL

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related