How to run a mass update/delete query in Linq?

Question

I have 2 Linq2Sql classes: Parent and Child. I want to do things like removing all children for a parent, or updating all child records. In SQL I would have written:

delete Child where ParentID = @p

or

update Child set Val = Val+1 where ParentID = @p

I can do this in Linq the brute force way inside the Parent class:

Children.ToList().ForEach(c => c.DeleteOnSubmit()); // DeleteOnSubmit is my own method

and

Children.ToList().ForEach(c => c.Val++);

But given Linq's inherent performance penalty on ForEach loops, this seems like a very inefficient way to do things. Is there some way of achieving the desired end that will fire off exactly one query?

score 14 · Answer 1 · answered Dec 23 '09 at 13:00

14

For these cases you can use the DataContext.ExecuteCommand method to directly execute SQL on your database. For example:

dataContext.ExecuteCommand("delete Child where ParentID = {0}", parentId);

answered Dec 23 '09 at 13:00

Konamiman

49,681
17
108
138

1

Ugh, that's ugly. Doesn't using literal SQL defeat half the purpose of using Linq? – Shaul Behr Dec 23 '09 at 13:05
3

@Shaul, For the most part, yes, but LINQ-To-SQL doesn't support mass update/delete, so that's your option. Another option would be a stored procedure. – Ryan Alford Dec 23 '09 at 13:08
Sometimes we *need* to use ugly code. You can unuglify it a little by confining it inside a method, `DeleteChilds(parentId)`or the like. – Konamiman Dec 23 '09 at 13:19
Sorry about withdrawing the answer credit - but take a look at Terry Aney's solution to this problem, and I think you'll agree this is a freakin' brilliant (and elegant) approach! – Shaul Behr Dec 24 '09 at 14:59
1

This approach is standard SQL and handles the task efficiently and quickly with an EXTREMELY efficient, fast, simple query. Having to use an "ExpressionTree" or loops or anything like that seems completely absurd to me. Linq is a mess. – Manachi Jan 29 '18 at 05:17

score 9 · Accepted Answer · edited Mar 05 '14 at 16:31

9

Look at this link : It's using ExpressionTree : http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx [Broken Link]

http://terryaney.wordpress.com/2008/04/14/batch-updates-and-deletes-with-linq-to-sql/ [Probably the correct one]

edited Mar 05 '14 at 16:31

Community

1
1

answered Dec 23 '09 at 14:55

pdiddy

6,217
10
50
111

+1 Wow, this is a great link! I'm on the verge of changing my mind about answer credit... – Shaul Behr Dec 24 '09 at 12:38
2

@Shaul This might be a great answer now, but link only answers have this problem that sooner or later they become worthless, because the external resource can no longer be reached. It's always better to write at least a part of the solution here so it will be available as long as SO lives. – jahu Jun 25 '14 at 10:26
@MarcinHabuszewski - acknowledged, thank you. 5 years later, I never use Linq2Sql anymore, and Entity Framework Extended has native support for this. – Shaul Behr Jun 25 '14 at 10:45

score 2 · Answer 3 · answered Jan 05 '10 at 16:33

2

Take a look to http://magiq.codeplex.com mass operation with linq.

answered Jan 05 '10 at 16:33

ivos

47
1

score 1 · Answer 4 · answered Mar 16 '17 at 19:03

Update and Delete

A current limitation of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. To overcome this problem we have to extend the current entity framework using EntityFramework.Extended. EntityFramework.Extended have useful features like Batch Update and Delete, Audit log, Query Result cache, Future Queries. Batch update and delete eliminates the need to retrieve and load an entity before modifying it. Here are a few lines of code to demonstrate how to delete, update.

Install via nuget

PM> Install-Package EntityFramework.Extended

Update

Scenario: Update customers which have country USA. If we do this without any extensions, we have to fetch all customers which have country USA, modify the list and update it using loops. Using Entity Framework.Exdended we don’t need to fetch the list of customers, simply add where condition, set update data and execute query.

static void Main(string[] args)   
{   
    using(var db = new DataContext())   
    {   
        db.Customers.Where(c => c.Country == "USA").Update(c => new Customer()   
        {   
            Country = "IN"   
        });   

        foreach(var customer in db.Customers.ToList())    
        {   
            Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);   
        }   
    }   

    Console.ReadLine();   
}

https://code.msdn.microsoft.com/entity-framework-batch-994cd739

score -1 · Answer 5 · edited Apr 13 '14 at 08:40

-1

Try this

_db.tblStockAllocationEurails.Where(t => t.StockNo >= From && t.StockNo <= To).ToList().ForEach(t => t.StatusID = NewGuid);
_db.SaveChanges();

edited Apr 13 '14 at 08:40

Mureinik

297,002
52
306
350

answered Apr 11 '14 at 12:06

amit singh

13
1

How to run a mass update/delete query in Linq?

5 Answers5

Linked