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.

Answer 2

Old post but there is a RemoveRange method now:

    dbContext.MyEntities.RemoveRange(dbContext.MyEntities);
    dbContext.SaveChanges();

Answer 3

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>();

Answer 4

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();

Answer 5

As the accepted answer only mentions about the method below:

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

and rather gives alternatives to it, I've managed to write a method, which you can use to avoid loading all entities, then looping through them and use ExecuteSqlCommand instead.

Assuming using unit of work, where 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 ) );
}

First block of code retrievs the table name needed in ExecuteSqlCommand method.

Usage:

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

There's no need to call

context.SaveChanges()

Answer 6

If you are working with a unit of work and generic repository you may 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;
            }
        }

Usage:

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

Answer 7

Using repository pattern, where you give the repository the type of the model and it can work on any model type.

    public async Task<int> RemoveAllAsync()
    {
        Context.Set<T>().RemoveRange(await Context.Set<T>().ToListAsync());
        return await Context.SaveChangesAsync();
    }