CODEWITHSUNDEEP
sqllinqlinq-to-sql
Tom Gullen
Tom Gullen

Reputation: 61729

Linq to SQL update multiple records in one query

Given the code:

var q = db.TranslationProjectKeys.Where(c => c.ProjectID == forProject.ID);
foreach (var key in q)
{
    key.Live = false;
}
db.SubmitChanges();

If I run the SQL server profiler it shows dozens of UPDATE.... SQL statements. I believe this would be considerably faster if this query was executed with one UPDATE statement:

UPDATE TranslationProjectKeys SET Live = 0 WHERE ProjectID = x

Is there a way to make the query execute in this way?

I'm wary of using db.ExecuteCommand("...") as I am aware the context can be cached which can cause some bugs.

Upvotes: 2

Views: 1973

Answers (2)

melihorhan
melihorhan

Reputation: 198

You can use EntityFramework.Extended . You can execute multiple update. For Example

var q = db.TranslationProjectKeys.Where(c => c.ProjectID == forProject.ID).Update(c => new TranslationProjectKey{ Live  = false })

Upvotes: 3

psoshmo
psoshmo

Reputation: 1550

linq to sql does not have a built in mass update function (unless something has changed). you could just execute the command manually like

dataContext.ExecuteCommand("UPDATE TranslationProjectKeys SET Live = 0 WHERE ProjectID = {0}", x);

or if you wanted to get fancy, you could try implementing your own mass update functionality like this article shows, but it is MUCH more in depth and complicated (Though it IS possible to do)

Upvotes: 1

Related Questions