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

User · Answer

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.

User · Answer

This works Properly in EF 5   YourEntityModel myEntities   new YourEntityModel     var objCtx     IObjectContextAdapter myEntities  ObjectContext  objCtx ExecuteStoreCommand  TRUNCATE TABLE  TableName

User · Answer

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

User · Answer

dataDb Table RemoveRange dataDb Table   dataDb SaveChanges

User · Answer

Warning  The following is only suitable for small tables  think  lt  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 lt Vote gt  Votes get set       public DbSet lt Poll gt  Polls get set       public DbSet lt Voter gt  Voters get set       public DbSet lt Candidacy gt  Candidates get set       For tidier code you can declare the following extension method   public static class EntityExtensions       public static void Clear lt T gt  this DbSet lt T gt  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    EF will get ALL the rows  VotingContext Votes   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

User · Answer

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

User · Answer

In my code I didn t really have nice access to the Database object  so you can do it on the DbSet where you also is allowed to use any kind of sql  It will sort of end out like this   var p   await  db Persons FromSql  truncate table Persons select top 0   from Persons   ToListAsync

User · Answer

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

User · Answer

Delete all records  Do not reset the primary index like  truncate         lt summary gt      SET - DELETE all record by table - no truncate - return deleted records      lt  summary gt  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

User · Answer

var all   from c in dataDb Table select c  dataDb Table RemoveRange all   dataDb SaveChanges

User · Answer

var list   db Discounts ToList   Select x   gt  x as Discount   foreach  var item in list        db Discounts Remove item     db SaveChanges

User · Answer

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

User · Answer

You can do that without Foreach  dataDB Table RemoveRange dataDB Table   dataDB SaveChanges      This will remove all rows

User · Answer

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

User · Answer

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 lt T gt  this DbSet lt T gt  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 lt T gt    IRepository lt T gt  where T   class  new         private readonly IEfDbContext  context       public void BulkInsert IEnumerable lt T gt  entities                 context BulkInsert entities              public void Truncate                  context Database ExecuteSqlCommand   TRUNCATE TABLE  typeof T  Name                   usage   DataAccess TheRepository Truncate     var toAddBulk   new List lt EnvironmentXImportingSystem gt          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

User · Answer

This avoids using any sql  using  var context   new MyDbContext          var itemsToDelete   context Set lt MyTable gt         context MyTables RemoveRange itemsToDelete       context SaveChanges

User · Answer

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

User · Answer

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

User · Answer

This works for me    EF v3 1 5 context ModelName RemoveRange context ModelName ToList     context SaveChanges

User · Answer

In EFCore  version i am using is 3 1  you can use the following to remove all rows -   context Database ExecuteSqlRaw  TRUNCATE TABLE  TableName

User · Answer

var data    from n in db users select n   db users RemoveRange data   db SaveChanges

User · Answer

If MVC  you can do   public async Task lt IActionResult gt  DeleteAll         var list   await  context YourClass ToListAsync         context YourClass RemoveRange list       await  context SaveChangesAsync        return RedirectToAction nameof Index

[c#] Entity Framework. Delete all rows in table

Examples related to c#

Examples related to sql

Examples related to linq

Examples related to entity-framework