Entity Framework Delete All on Submit

Question

In LINQ to SQL, I could do:

context.User_Roles.DeleteAllOnSubmit(context.User_Roles.Where(ur => ur.UserId == user.UserId));

Whats the equivalent to this for entity framework?

duplicate of http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities?lq=1 — surfen, Jun 12 '12 at 20:48

Femaref · Answer 1 · 2012-03-18T20:33:25.880

24

foreach(var entity in context.User_Roles.Where(ur => ur.UserId == user.UserId))
{
  context.User_Roles.DeleteObject(entity);
}
context.SaveChanges();

Of course, you can write an extension method, which would encapsulate this.

This would be something like this:

public static void DeleteObjects<TEntity> (this ObjectSet<TEntity> set, IEnumerable<TEntity> data) where TEntity : class
{
  foreach(var entity in data)
    set.DeleteObject(entity);
}

Called like:

context.User_Roles.DeleteObjects(context.User_Roles.Where(ur => ur.UserId == user.UserId))
context.SaveChanges();

edited Mar 18 '12 at 20:33

answered Jan 09 '11 at 19:32

Femaref

60,705
7
138
176

You may run into this issue using this method, this should help: http://stackoverflow.com/a/3742340/183174 – LostNomad311 Dec 14 '11 at 19:36
@nomad311 what exacly is the issue that you mention? – surfen Dec 30 '11 at 00:51
The title of that question `Can 't create extesion method for LINQ to Enitities link DeleteAllOnSubmit() in LINQ to SQL` is the error message you would get if you ran into the that issue. – LostNomad311 Dec 30 '11 at 14:03
`DeleteObjects` needs to be static and `TEntity` needs to be constrained to a class: `public static void DeleteObjects(this ObjectSet set, IEnumerable data) where TEntity : class` – Stephen Swensen Mar 18 '12 at 18:30

Stephen Swensen · Accepted Answer · 2014-02-09T17:49:11.833

5

@Femaref has the right idea, but for a true analog to L2E's DeleteAllOnSubmit, you'll want your extension method to make a copy of the entities being deleted before enumerating so that you don't get "collection modified while enumerating" exceptions.

public static void DeleteAllObjects<TEntity>(this ObjectSet<TEntity> set, IEnumerable<TEntity> data) where TEntity : class {
    foreach(var entity in data.ToList()) //data.ToList() makes a copy of data for safe enumeration
        set.DeleteObject(entity);
}

edited Feb 09 '14 at 17:49

answered Mar 18 '12 at 18:43

Stephen Swensen

22,107
9
81
136

1

This was my problem with the accepted answer. Is there a more efficient way than pulling the whole set into memory though? – Eric Apr 25 '12 at 22:12
1

@Eric - not out-of-the-box, you'd need to resort to a custom solution, perhaps using inline SQL someway, but I haven't tried anything like that – Stephen Swensen Apr 25 '12 at 22:36
1

Or just iterate through the data using: `for (int i = data.Count() - 1; i >= 0; i--) set.DeleteObject(data.ElementAt(i));` – Jimbo Nov 26 '12 at 13:57

score 2 · Answer 3 · answered Oct 08 '11 at 16:21

2

foreach(var entity in context.User_Roles.Where(ur => ur.UserId == user.UserId))
{
  context.User_Roles.DeleteObject(entity);
}
context.SaveChanges();

of course, this solution can work. But, it is the most inefficient solution. This solution will generate one delete SQL command for each record (entity). Imaging that you want to delete all data before year 2000 . there are more than 1,000,000 records in the database. If delete these objects in this way, more than 1,000,000 SQL commands will be sent to the server, it is a unnecessary big waste. What

answered Oct 08 '11 at 16:21

Jerry

435
4
12

1

Your answer looks incomplete. What would be an alternative, more efficient approach? – surfen Dec 30 '11 at 00:54
I didn't read your code carefullly. but, why do you say "delete ALL on submit"? what do you mean "ALL" here?
if you want to delete all data in a table over than 2000 rows or even more, you must use delete statement rather than the answers given above. for consideration of efficiency. – Jerry Jun 12 '12 at 13:12
3

It's unlikely that someone will have so many users with the same UserId (It should be a Primary Key and there would be only one user with this UserId). I agree however, that for a large number of entities there are faster solutions, like **ExecuteStoreCommand** http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities – surfen Jun 12 '12 at 20:46
yes, you are right. I was disguided by the title: delete ALL on submit. – Jerry Jun 13 '12 at 11:49

score 0 · Answer 4 · answered Oct 30 '12 at 18:33

0

There is no RemoveAll equivalent in Entity Framework, so you can load entities in memory and remove them one by one using DeleteObject method.

You can use Linq : context.MyEntitie.RemoveAll(context.MyEntitie);

answered Oct 30 '12 at 18:33

Pierre-Olivier Pignon

679
7
18

score 0 · Answer 5 · answered Dec 24 '12 at 18:07

0

use EntityFramework.Extensions
1) First install EntityFramework.Extensions using NuGet

2) Here is the code similar to Linq2Sql's DeleteAllOnSubmit():

using EntityFramework.Extensions;

....
public void DeleteAllUsers(User_Role user){
   context.User_Roles.Delete(ur => ur.UserId == user.UserId);
   context.SaveChanges();
}
...

answered Dec 24 '12 at 18:07

Rajesh

1,459
10
17

if you pass an argument like ur.UserID == user.UserId as bool, why dont you just pass a 'true' value ;-) So much cleaner, anyway, that will be always an antipattern. – mikus Feb 07 '13 at 14:17

Entity Framework Delete All on Submit

5 Answers5