What is the recommended practice to update or delete multiple entities in EntityFramework?

Question

In SQL one might sometimes write something like

DELETE FROM table WHERE column IS NULL

or

UPDATE table SET column1=value WHERE column2 IS NULL

or any other criterion that might apply to multiple rows.

As far as I can tell, the best EntityFramework can do is something like

foreach (var entity in db.Table.Where(row => row.Column == null))
    db.Table.Remove(entity);   // or entity.Column2 = value;
db.SaveChanges();

But of course that will retrieve all the entities, and then run a separate DELETE query for each. Surely that must be much slower if there are many entities that satisfy the criterion.

So, cut a long story short, is there any support in EntityFramework for updating or deleting multiple entities in a single query?

Here are some options - http://stackoverflow.com/questions/4407348/batching-db-commands-in-entity-framework-4-0 — Simon Whittemore, Mar 19 '12 at 20:03

Ladislav Mrnka · Accepted Answer · 2012-03-19T20:10:28.343

14

EF doesn't have support for batch updates or deletes but you can simply do:

db.Database.ExecuteSqlCommand("DELETE FROM ...", someParameter);

Edit:

People who really want to stick with LINQ queries sometimes use workaround where they first create select SQL query from LINQ query:

string query = db.Table.Where(row => row.Column == null).ToString();

and after that find the first occurrence of FROM and replace the beginning of the query with DELETE and execute result with ExecuteSqlCommand. The problem with this approach is that it works only in basic scenarios. It will not work with entity splitting or some inheritance mapping where you need to delete two or more records per entity.

edited Mar 19 '12 at 20:10

answered Mar 19 '12 at 20:01

Ladislav Mrnka

360,892
59
660
670

1

Bah... that’s rather disappointing. Is there some nice way to abstract the details like table and field names? Or convert a LINQ "Where" clause (the `IQueryable` that it returns) to runnable SQL? – Roman Starkov Mar 19 '12 at 20:02
Yeah, disappointing as romkyns says... I wonder if there’s any specific reason why they thought it wasn’t worth implementing, as I can hardly imagine any DB-driven web app that *doesn’t* require the occasional batch update/delete operation. — Would be cool if you know the answers to romkyns’s questions though! – Timwi Mar 19 '12 at 20:04
@Timwi: But it is still supported - executing raw SQL is still valid technique even when using EF. You can simply create method on your context executing the SQL internally and your upper layer will just use the method. – Ladislav Mrnka Mar 19 '12 at 20:08

score 5 · Answer 2 · edited May 23 '17 at 12:08

Take a look to Entity Framework Extensions (Multiple entity updates). This project allow set operations using lambda expressions. Samples from doc:

this.Container.Devices.Delete(o => o.Id == 1);

this.Container.Devices.Update(
     o => new Device() { 
        LastOrderRequest = DateTime.Now, 
        Description = o.Description + "teste"
     }, 
     o => o.Id == 1);

Digging EFE project source code you can see how automatize @Ladislav Mrnka second approach also adding setting operations:

    public override string GetDmlCommand()
    {
        //Recover Table Name

        StringBuilder updateCommand = new StringBuilder();
        updateCommand.Append("UPDATE ");
        updateCommand.Append(MetadataAccessor.GetTableNameByEdmType(
                                  typeof(T).Name));
        updateCommand.Append(" ");
        updateCommand.Append(setParser.ParseExpression());
        updateCommand.Append(whereParser.ParseExpression());

        return updateCommand.ToString();
    }

Edited 3 years latter

Take a look to this great answer: https://stackoverflow.com/a/12751429

+1 It looks good. It is a more robust way to create delete / update commands because it translates expression instead of parsing string query but by looking to source code it looks like complex mappings are not covered as well. — Ladislav Mrnka, Mar 19 '12 at 20:20
@LadislavMrnka, this is only a bit of code to ilustrate answer. Be free to see [all EFE code](http://efe.codeplex.com/SourceControl/list/changesets). — dani herrera, Mar 19 '12 at 20:23

score 2 · Answer 3 · answered Oct 04 '14 at 00:10

Entity Framework Extended Library helps to do this.

Delete

//delete all users where FirstName matches
context.Users.Delete(u => u.FirstName == "firstname");

Update

//update all tasks with status of 1 to status of 2
context.Tasks.Update(
    t => t.StatusId == 1, 
    t2 => new Task {StatusId = 2});

//example of using an IQueryable as the filter for the update
var users = context.Users.Where(u => u.FirstName == "firstname");
context.Users.Update(users, u => new User {FirstName = "newfirstname"});

https://github.com/loresoft/EntityFramework.Extended

What is the recommended practice to update or delete multiple entities in EntityFramework?

3 Answers3

Linked