CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Shaul Behr
Shaul Behr

Reputation: 38023

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

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?

Upvotes: 8

Views: 20274

Answers (5)

Imran Chaudhary
Imran Chaudhary

Reputation: 79

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

Upvotes: 1

amit singh
amit singh

Reputation: 13

Try this

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

Upvotes: -1

pdiddy
pdiddy

Reputation: 6297

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]

Upvotes: 9

ivos
ivos

Reputation: 47

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

Upvotes: 2

Konamiman
Konamiman

Reputation: 50283

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

Upvotes: 14

Related Questions