How to delete all elements in DbSet?

Question

What is the best way to remove all items in System.Data.Entity.DbSet using Entity Framework 4.3?

Answer 1

dbContext.Database.ExecuteSqlCommand("delete from MyTable"); 

(Do not joke.)

The problem is that EF does not support command commands and the only way to delete all objects in a collection without direct DML is:

 foreach (var entity in dbContext.MyEntities) dbContext.MyEntities.Remove(entity); dbContext.SaveChanges(); 

Or maybe a little cheaper not to download full objects:

 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 need to load all entities or all properties and delete objects one by one from the set. Moreover, when you call SaveChanges , EF will send n (= the number of entities in the set) DELETE statements to the database, which are also executed one after the other in the database (in one transaction).

So, direct SQL is clearly preferable for this purpose, since you only need one DELETE statement.

Answer 2

Here is another way to do this 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 a method and clear the set:

 myDbContext.DeleteAll<MyPocoClassName>(); 

Answer 3

If you want to delete all elements without SQL record and execute only Single Db Call

The extended Entity Framework library offers a batch removal method.

 context.Users.Delete(); 

Answer 4

Since the accepted answer only mentions the method below:

 context.Database.ExecuteSqlCommand("delete from MyTable"); 

and, rather, gives alternatives to it, I managed to write a method that you can use to not load all entities and then iterate through them and use ExecuteSqlCommand instead.

Assuming using a unit of work, where the context is DbContext:

 using System.Data.Entity.Core.Objects; using System.Text.RegularExpressions; public void DeleteAll() { ObjectContext objectContext = ( (IObjectContextAdapter)context ).ObjectContext; string sql = objectContext.CreateObjectSet<T>().ToTraceString(); Regex regex = new Regex( "FROM (?<table>.*) AS" ); Match match = regex.Match( sql ); string tableName = match.Groups[ "table" ].Value; context.Database.ExecuteSqlCommand( string.Format( "delete from {0}", tableName ) ); } 

The first block of code retrieves the table name required by the ExecuteSqlCommand method.

Using:

 using ( var context = new UnitOfWork() ) { context.MyRepository.DeleteAll(); } 

There is no need to call

 context.SaveChanges() 

Answer 5

If you work with a unit of work and a shared repository, you can find the following useful

 public virtual void DeleteWhere(Expression<Func<TEntity, bool>> filter = null, Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null, string includeProperties = "") { IQueryable<TEntity> query = dbSet; if (filter != null) { query = query.Where(filter); } foreach (var includeProperty in includeProperties.Split (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { query = query.Include(includeProperty); } foreach (var entity in query) { context.Entry(entity).State = EntityState.Deleted; } } 

Using:

 uow.myRepositoryName.DeleteWhere(u => u.RoomId == roomId); uow.Save(); 

Answer 6

You can achieve this using a direct request:

  ent.Database.ExecuteSqlCommand("delete from tablename");