What is the appropriate procedure for updating or deleting many EntityFramework entities?

.net c# entity-framework sql

Question

Sometimes, one may type anything like this in SQL.

DELETE FROM table WHERE column IS NULL

or

UPDATE table SET column1=value WHERE column2 IS NULL

or another characteristic that may be used to group together many rows.

The most that EntityFramework can achieve, as far as I can determine, is something like

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

However, that will unavoidably obtain every entity and execute a different DELETE query for each one. If there are several entities that meet the requirement, that must be substantially slower.

To sum up, does EntityFramework provide support for editing or removing numerous entities in a single query?

1
25
3/19/2012 7:56:56 PM

Accepted Answer

Although EF does not enable batch updates or deletions, you may still:

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

Edit:

People that really prefer LINQ queries sometimes use a workaround in which they first generate a select SQL query from LINQ query:

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

then track down the earliest instance ofFROM Then insert the following after the query's opening:DELETE and then use the resultExecuteSqlCommand . This method's drawback is that it only works in straightforward situations. If you need to remove two or more records per entity, entity splitting or some inheritance mapping won't work.

13
3/19/2012 8:10:28 PM

Popular Answer

Look at the numbers Extended Entity Framework (Multiple entity updates). In this project, set operations are supported through lambda expressions. Examples from the document

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

You can see how to automate @Ladislav Mrnka's second technique by digging up project source code for EFE and 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();
    }

3 years later, edited

Look at this excellent response: https://stackoverflow.com/a/12751429



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow