Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 538
Timwi Avatar asked Mar 19 '12 19:03

Timwi


People also ask

Which is best approach in Entity Framework?

As in this diagram, if we already have domain classes, the Code First approach is best suited for our application. The same as if we have a database, Database First is a good option. If we don't have model classes and a database and require a visual entity designer tool then Model First is best suited.

How do I delete all records in Entity Framework?

Table select o; foreach (var row in rows) { dataDb. Table. Remove(row); } dataDb. SaveChanges();


1 Answers

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.

like image 196
Ladislav Mrnka Avatar answered Oct 09 '22 10:10

Ladislav Mrnka