How should I remove all elements in a DbSet?
dbContext.Database.ExecuteSqlCommand("delete from MyTable");
(No kidding.)
The problem is that EF doesn't support any batch commands and the only way to delete all entities in a set using no direct DML would be:
foreach (var entity in dbContext.MyEntities)
dbContext.MyEntities.Remove(entity);
dbContext.SaveChanges();
Or maybe a litte bit cheaper to avoid loading full entities:
foreach (var id in dbContext.MyEntities.Select(e => e.Id))
{
var entity = new MyEntity { Id = id };
dbContext.MyEntities.Attach(entity);
dbContext.MyEntities.Remove(entity);
}
dbContext.SaveChanges();
But in both cases you have to load all entities or all key properties and remove the entities one by one from the set. Moreover when you call SaveChanges
EF will send n (=number of entities in the set) DELETE statements to the database which also get executed one by one in the DB (in a single transaction).
So, direct SQL is clearly preferable for this purpose as you only need a single DELETE statement.
Old post but there is a RemoveRange method now:
dbContext.MyEntities.RemoveRange(dbContext.MyEntities);
dbContext.SaveChanges();
If you want to remove all elements without writing any SQL and only execute a Single Db Call
Entity Framework Extended Library offers a batch delete method.
context.Users.Delete();
Here's another way you can do it in code.
public static class Extensions
{
public static void DeleteAll<T>(this DbContext context)
where T : class
{
foreach (var p in context.Set<T>())
{
context.Entry(p).State = EntityState.Deleted;
}
}
}
To actually call the method and clear the set:
myDbContext.DeleteAll<MyPocoClassName>();