Entity Framework. Delete all rows in table

Question

How can I quickly remove all rows in the table using Entity Framework?

I am currently using:

var rows = from o in dataDb.Table
           select o;
foreach (var row in rows)
{
    dataDb.Table.Remove(row);
}
dataDb.SaveChanges();

However, it takes a long time to execute.

Are there any alternatives?

Answer 1

Update for EF Core 7+, we now have a Bulk Deletion method available as part of EF Core which means the specific method is implemented by the underlying provider (ie. not specific to SQL Server).

The problem with the original code as well as some other answers is that you're loading all of the entities in the table in to memory to be able to delete them. EF Core 7 shipped with a workaround for this.

You can now do:

await context.Table.ExecuteDeleteAsync();

For SQL implementations this will map to a DELETE FROM query being generated. There is no support for this in the NoSQL (Cosmos DB) provider as far as I can see.

Note that you still have to consider relationship deletions (you can simply use the same call on any related tables), as well as the loss of TRUNCATE TABLE and no longer resetting any Identity properties. You can however reset this yourself (SQL Server):

DBCC CHECKIDENT ('[Table]', RESEED, 0);

---

Original:

Using SQL's TRUNCATE TABLE command will be the fastest as it operates on the table and not on individual rows.

dataDb.ExecuteStoreCommand("TRUNCATE TABLE [Table]");

Assuming dataDb is a DbContext (not an ObjectContext), you can wrap it and use the method like this:

var objCtx = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)dataDb).ObjectContext;
objCtx.ExecuteStoreCommand("TRUNCATE TABLE [Table]");

Answer 2

It is a very clean solution.

_context.RemoveRange(_context.ModelName);

_context.SaveChanges();

Answer 3

EF Core 7.0 solves this problem once and for all by adding bulk update and delete semantics:

await dataDB.Table.ExecuteDeleteAsync();

Note that this syntax immediately executes the underlying (SQL) command to delete the data from the table. It does not fiddle around with tracking the entity, marking it for deletion, and waiting for UpdateDatabase to execute the transaction against the database.

Also note that multiple ExecuteDelete and ExecuteUpdate commands will not be contained in a single transaction by default. However, the DbContext transaction APIs can be used in the normal way to wrap these commands in a transaction.

Answer 4

My solution, mixing my ideas, some answers (Ron one from this thread, but also this and this for reflection) and trying to cover some different conditions.

It is based on EF6, but it should work fine, just fixing some extensions like GetTableName<TEntity>.

My solution:

• uses extensions, so you only need DbSet to launch
• has a row count threshold, to decide between RemoveRange or SQL execution, to avoid perfomance issues
• the SQL version is based on DELETE instead of TRUNCATE, to avoid foreign key issues (it has to fit your requirements, of course)
• has a parameter to save changes inline

private const int RangeLimit = 100;

private static void ClearTable<TEntity>(this DbSet<TEntity> dataSet, bool saveChanges = true) where TEntity : class
{
    DbContext context = null;

    if (dataSet.Count() > RangeLimit)
    {
        context = dataSet.GetContext();
        context.Database.ExecuteSqlCommand($"DELETE FROM [{context.GetTableName<TEntity>()}]");
    }
    else
    {
        dataSet.RemoveRange(dataSet);
    }

    if (!saveChanges)
    {
        return;
    }

    if (context == null)
    {
        context = dataSet.GetContext();
    }
    context.SaveChanges();
}

private static DbContext GetContext<TEntity>(this DbSet<TEntity> dbSet)
    where TEntity : class
{
    var internalSet = dbSet
        .GetType()
        .GetField("_internalSet", BindingFlags.NonPublic | BindingFlags.Instance)
        ?.GetValue(dbSet);
    var internalContext = internalSet?.GetType().BaseType
        ?.GetField("_internalContext", BindingFlags.NonPublic | BindingFlags.Instance)
        ?.GetValue(internalSet);
    return (DbContext)internalContext?.GetType()
        .GetProperty("Owner", BindingFlags.Instance | BindingFlags.Public)
        ?.GetValue(internalContext, null);
}

public static string GetTableName<TEntity>(this DbContext context) where TEntity : class
{
    return (context as IObjectContextAdapter).ObjectContext.CreateObjectSet<TEntity>().EntitySet.Name;
}

All you have to do, with a database table named Entries, is:

databaseContext.Entries.ClearTable();

if you want to save changes, or if you don't want:

databaseContext.Entries.ClearTable(false);

It is based on reflection, to simplify code. It has some performance tradeoff, of course, but reflection happens once for each table, hence should be completely acceptable in these conditions.

Answer 5

For those that are googling this and ended up here like me, this is how you currently do it in EF5 and EF6:

context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]");

Assuming context is a System.Data.Entity.DbContext

Edit:

Currently in net6.0 (dotnet 6 core) you can do the following:

context.Database.ExecuteSqlRaw("TRUNCATE TABLE [TableName]");

Or use the Async overload:

await context.Database.ExecuteSqlRawAsync("TRUNCATE TABLE [TableName]");

These are extension methods coming from Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions

If you're having issues with foreign keys (in MySql), you might have to do the following (Doing the SET FOREIGN_KEY_CHECKS = 0; part in a separate call does not seem to work for me)

context.Database.ExecuteSqlRaw("SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE [TableName];");

So if you want to truncate your entire database (Possibly for unittesting reasons) - you can do the following:

var tableNames = context.Model.GetEntityTypes()
    .Select(t => t.GetTableName())
    .Distinct()
    .ToList();

foreach (var tableName in tableNames)
{
    context.Database.ExecuteSqlRaw($"SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `{tableName}`;");
}

Answer 6

The following works on SQLite database (using Entity Framework).

It seems that the fastest way to clear all the db tables is using context.Database.ExecuteSqlCommand("some SQL"), as some comments above highlighted as well. Here I am going to show how to reset the 'index' count of tables too.

context.Database.ExecuteSqlCommand("delete from TableA");
context.Database.ExecuteSqlCommand("delete from sqlite_sequence where name='TableA'");//resets the autoindex

context.Database.ExecuteSqlCommand("delete from TableB");
context.Database.ExecuteSqlCommand("delete from sqlite_sequence where name='TableB'");//resets the autoindex 

context.Database.ExecuteSqlCommand("delete from TableC");
context.Database.ExecuteSqlCommand("delete from sqlite_sequence where name='TableC'");//resets the autoindex 

One important point is that if you use foreign keys in your tables, you must first delete the child table before the parent table, so the sequence (hierarchy) of tables during deletion is important, otherwise a SQLite exception may occur.

Note: var context = new YourContext()

Answer 7

If

using(var db = new MyDbContext())
{
    await db.Database.ExecuteSqlCommandAsync(@"TRUNCATE TABLE MyTable"););
}

causes

Cannot truncate table 'MyTable' because it is being referenced by a FOREIGN KEY constraint.

I use this:

using(var db = new MyDbContext())
{
    await db.Database.ExecuteSqlCommandAsync(@"DELETE FROM MyTable WHERE ID != -1");
}

Answer 8

Works for EF Core 3

public static class EntityExtensions
{
    public static async Task ClearAsync<T>(this DbSet<T> dbSet) where T : class
    {
        var command = dbSet.CreateDbCommand();
        command.CommandText = $"TRUNCATE TABLE {dbSet.EntityType.GetSchema()}.{dbSet.EntityType.GetTableName()}";
        await command.ExecuteNonQueryAsync();
    }
}

but please note that dbSet.CreateDbCommand is an extension

Answer 9

Here is a variation on the popular solution by Ron that avoids the use of hardcoded string table names by taking advantage of another popular solution on stack overflow for determining the underlying table name for an entity framework class.

With these extension methods the solution looks like this:

_dbContext.TruncateTable<TheTableName>();

(use this.TruncateTable<... if you're editing code within an EF DBContext class or partial class file)

And here's the extension class:

public static class EntityFrameworkExtensions
{
    private static string ParseTableNameFromSQL(string sql)
    {
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }
    
    public static string GetTableName<T>(this IObjectContextAdapter context) where T : class =>
        ParseTableNameFromSQL(context.ObjectContext.CreateObjectSet<T>().ToTraceString());
    
    public static void TruncateTable<T>(this DbContext dbContext) where T : class =>
        dbContext.Database.ExecuteSqlCommand($"TRUNCATE TABLE {dbContext.GetTableName<T>()}");

    public static void DeleteAllTableRows<T>(this DbContext dbContext) where T : class =>
        dbContext.Database.ExecuteSqlCommand($"DELETE FROM {dbContext.GetTableName<T>()}");
}

The last extension method DeleteAllTableRows is a useful alternative if your table cannot be truncated (e.g. due to foreign key references) - this is still much faster than the Entity Framework RemoveAll alternative.

Answer 10

context.TableName.RemoveRange(context.TableName);
context.SaveChanges();

Answer 11

There are several issues with pretty much all the answers here:

1] Hard-coded sql. Will brackets work on all database engines?
2] Entity framework Remove and RemoveRange calls. This loads all entities into memory affected by the operation. Yikes.
3] Truncate table. Breaks with foreign key references and may not work accross all database engines.

Use https://entityframework-plus.net/, they handle the cross database platform stuff, translate the delete into the correct sql statement and don't load entities into memory, and the library is free and open source.

Disclaimer: I am not affiliated with the nuget package. They do offer a paid version that does even more stuff.

Answer 12

This works for me... EF v3.1.5

context.ModelName.RemoveRange(context.ModelName.ToList());
context.SaveChanges();

Answer 13

Make sure when you are trying to delete parent all children will cascade on delete. Or children have nullable foreign key.

Answer 14

In EFCore (version i am using is 3.1) you can use the following to remove all rows -

context.Database.ExecuteSqlRaw("TRUNCATE TABLE [TableName]");

Answer 15

If MVC, you can do:

public async Task<IActionResult> DeleteAll()
{
    var list = await _context.YourClass.ToListAsync();
    _context.YourClass.RemoveRange(list);
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
}

Answer 16

Delete all records. Do not reset the primary index like "truncate".

/// <summary>
/// SET - DELETE all record by table - no truncate - return deleted records
/// </summary>
public static int setListDelAllMYTABLE()
{
    // INIT
    int retObj = 0;
    using (MYDBEntities ctx = new MYDBEntities())
    {
        // GET - all record
        var tempAllRecord = ctx.MYTABLE.ToList();
        // RESET
        ctx.MYTABLE.RemoveRange(tempAllRecord);
        // SET - final save
        retObj += ctx.SaveChanges();
    }
    // RET
    return retObj;
}

Answer 17

You can do that without Foreach

dataDB.Table.RemoveRange(dataDB.Table);
dataDB.SaveChanges();

This will remove all rows

Answer 18

Warning: The following is only suitable for small tables (think < 1000 rows)

Here is a solution that uses entity framework (not SQL) to delete the rows, so it is not SQL Engine(R/DBM) specific.

This assumes that you're doing this for testing or some similar situation. Either

• The amount of data is small or
• The performance doesn't matter

---

Simply call:

VotingContext.Votes.RemoveRange(VotingContext.Votes);

---

Assuming this context:

public class VotingContext : DbContext
{
    public DbSet<Vote> Votes{get;set;}
    public DbSet<Poll> Polls{get;set;}
    public DbSet<Voter> Voters{get;set;}
    public DbSet<Candidacy> Candidates{get;set;}
}

For tidier code you can declare the following extension method:

public static class EntityExtensions
{
    public static void Clear<T>(this DbSet<T> dbSet) where T : class
    {
        dbSet.RemoveRange(dbSet);
    }
}

Then the above becomes:

VotingContext.Votes.Clear();
VotingContext.Voters.Clear();
VotingContext.Candidacy.Clear();
VotingContext.Polls.Clear();
await VotingTestContext.SaveChangesAsync();

I recently used this approach to clean up my test database for each testcase run (it´s obviously faster than recreating the DB from scratch each time, though I didn´t check the form of the delete commands that were generated).

---

Why can it be slow?

1. EF will get ALL the rows (VotingContext.Votes)
2. and then will use their IDs (not sure exactly how, doesn't matter), to delete them.

So if you're working with serious amount of data you'll kill the SQL server process (it will consume all the memory) and same thing for the IIS process since EF will cache all the data same way as SQL server. Don't use this one if your table contains serious amount of data.

Answer 19

I came across this question when I had to deal with a particular case: fully updating of content in a "leaf" table (no FKs pointing to it). This involved removing all rows and putting new rows information and it should be done transactionally (I do not want to end up with an empty table, if inserts fails for whatever reason).

I have tried the public static void Clear<T>(this DbSet<T> dbSet) approach, but new rows are not inserted. Another disadvante is that the whole process is slow, as rows are deleted one by one.

So, I have switched to TRUNCATE approach, since it is much faster and it is also ROLLBACKable. It also resets the identity.

Example using repository pattern:

public class Repository<T> : IRepository<T> where T : class, new()
{
    private readonly IEfDbContext _context;

    public void BulkInsert(IEnumerable<T> entities)
    {
        _context.BulkInsert(entities);
    }

    public void Truncate()
    {
        _context.Database.ExecuteSqlCommand($"TRUNCATE TABLE {typeof(T).Name}");
    }
 }

 // usage 
 DataAccess.TheRepository.Truncate();
 var toAddBulk = new List<EnvironmentXImportingSystem>();

 // fill toAddBulk from source system
 // ...

 DataAccess.TheRepository.BulkInsert(toAddBulk);
 DataAccess.SaveChanges();

Of course, as already mentioned, this solution cannot be used by tables referenced by foreign keys (TRUNCATE fails).

Answer 20

var data = (from n in db.users select n);
db.users.RemoveRange(data);
db.SaveChanges();

Answer 21

This avoids using any sql

using (var context = new MyDbContext())
{
    var itemsToDelete = context.Set<MyTable>();
    context.MyTables.RemoveRange(itemsToDelete);
    context.SaveChanges();
}

Answer 22

using (var context = new DataDb())
{
     var ctx = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)context).ObjectContext;
     ctx.ExecuteStoreCommand("DELETE FROM [TableName] WHERE Name= {0}", Name);
}

or

using (var context = new DataDb())
{
     context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]");
}

Answer 23

This works Properly in EF 5:

YourEntityModel myEntities = new YourEntityModel();

var objCtx = ((IObjectContextAdapter)myEntities).ObjectContext;
objCtx.ExecuteStoreCommand("TRUNCATE TABLE [TableName]");

Answer 24

If you wish to clear your entire database.

Because of the foreign-key constraints it matters which sequence the tables are truncated. This is a way to bruteforce this sequence.

    public static void ClearDatabase<T>() where T : DbContext, new()
    {
        using (var context = new T())
        {
            var tableNames = context.Database.SqlQuery<string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE '%Migration%'").ToList();
            foreach (var tableName in tableNames)
            {
                foreach (var t in tableNames)
                {
                    try
                    {

                        if (context.Database.ExecuteSqlCommand(string.Format("TRUNCATE TABLE [{0}]", tableName)) == 1)
                            break;

                    }
                    catch (Exception ex)
                    {

                    }
                }
            }

            context.SaveChanges();
        }
    }

usage:

ClearDatabase<ApplicationDbContext>();

remember to reinstantiate your DbContext after this.

Answer 25

var all = from c in dataDb.Table select c;
dataDb.Table.RemoveRange(all);
dataDb.SaveChanges();