How should I remove all elements in a DbSet?

.net, c#, entity-framework-4

Solution

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.

Problem

Original source

Related problems

• How can I delete 1,000 rows with EF6?