Entity Framework is a framework for creating entities. Remove all rows from the table.

Problem

Using Entity Framework, how can I easily remove all rows from a table?

Currently, I’m using:

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

It does, however, take a long time to complete.

Is there any other option?

Asked by Zhenia

Solution #1

This is how you do it now in EF5 and EF6 for people who are Google it and ending up here like me:

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

Context is assumed to be a System. Data.Entity. DbContext

Answered by Ron Sijm

Solution #2

Warning: This method is only appropriate for tiny tables (under 1000 rows).

Here’s a method that deletes rows using entity framework (rather than SQL), so it’s not SQL Engine(R/DBM) specific.

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

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;}
}

Declare the following extension method for cleaner code:

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 method to clear up my test database after each testcase run (it’s certainly faster than recreating the database from scratch each time, but I didn’t check the format of the delete commands created).

Why can’t it be a little slower?

If you’re working with a large quantity of data, you’ll want to kill the SQL server process (it will eat all of the RAM) and the IIS process as well, because EF will cache all of the data in the same way as SQL server does. If your table has a lot of data, this isn’t the one to utilize.

Answered by 9 revs, 4 users 68%

Solution #3

The fastest method is to use SQL’s TRUNCATE TABLE command, which operates on the table rather than 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]");

Answered by Rudi Visser

Solution #4

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

Answered by user3328890

Solution #5

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]");
}

Answered by Manish Mishra

Post is based on https://stackoverflow.com/questions/15220411/entity-framework-delete-all-rows-in-table