How should I remove all elements in a DbSet?

Question

What's the best way to remove all elements in a System.Data.Entity.DbSet, with Entity Framework 4.3?

Answer 1

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.