Delete All Rows in a Table in Entity Framework

• Download source code - 18.2 KB

Background

There are times when we may need to delete all the records from the table using Entity Framework. One of the commonly used ways is to iterate each row and use the DBSet.Remove().

foreach (var item in Db.Users)
{
    Db.Users.Remove(item);
}
Db.SaveChanges();

This process is slower than Truncate/Delete and not suitable for large datasets. Here, we are going to check alternative options, like running Truncate/Delete commands and unit testing options. The idea is to use a raw SQL query to Truncate a table or Delete everything from a table.

Extension Helper

Table Name

Extension methods to get the table name and schema of a mapped entity. We are going to use this name and schema to create a Truncate/Delete script.

using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text.RegularExpressions;

namespace EfHelper.Database.Core
{
    public static class ContextExtensions
    {
        public static string GetTableName<T>(this DbContext context) where T : class
        {
            ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
            return objectContext.GetTableName<T>();
        }

        public static void GetTableName<T>
        (this DbContext context, out string schema, out string tableName) where T : class
        {
            ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
            string fullTableName = objectContext.GetTableName<T>();
            List<string> names = fullTableName.Split('.').Select
                                 (x => x.Trim(new char[] {'[' ,']' ,' '})).ToList();
            schema = names[0];
            tableName = names[1];
        }

        private static string GetTableName<T>(this ObjectContext context) where T : class
        {
            string sql = context.CreateObjectSet<T>().ToTraceString();
            Regex regex = new Regex(@"FROM\s+(?<table>.+)\s+AS");
            Match match = regex.Match(sql);
            string table = match.Groups["table"].Value;
            return table;
        }
    }
}

Here, we are getting full table name as output:

string name = Db.GetTableName<User>();
Assert.AreEqual("[dbo].[Users]", name);

Here, we are getting both schema and table name as output:

string schema, tableName;
Db.GetTableName<User>(out schema, out tableName);
Assert.AreEqual("dbo", schema);
Assert.AreEqual("Users", tableName);

Delete/Truncate/Clear

There are the extensions methods for DbSet<T> and DbContext:

• Truncate: Truncates a table using a truncate query
• Delete: Delete all rows of a table using a delete query
• Clear: Delete all rows of a table using RemoveRange method

using System.Data.Entity;
using System.Linq;

namespace EfHelper.Database.Core
{
    public static class EfCleanHelper
    {
        public static void Clear<T>(this DbContext context) where T : class
        {
            DbSet<T> dbSet = context.Set<T>();
            if (dbSet.Any())
            {
                dbSet.RemoveRange(dbSet.ToList());
            }
        }

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

        public static string Truncate(this DbContext context, string tableNameWithSchema)
        {
            string cmd = $"TRUNCATE TABLE { tableNameWithSchema }";
            context.Database.ExecuteSqlCommand(cmd);
            return cmd;
        }

        public static string Delete(this DbContext context, string tableNameWithSchema)
        {
            string cmd = $"DELETE FROM { tableNameWithSchema }";
            context.Database.ExecuteSqlCommand(cmd);
            return cmd;
        }
    }
}

Using the Code

Db Context

using EfHelper.Database.Application.Config;
using EfHelper.Database.Application.Model;
using EfHelper.Database.Core;
using System;
using System.Data.Entity;

namespace EfHelper.Database.Application.Context
{
    public interface ICpuAppDbContext : IDisposable
    {
        DbSet<User> Users { get; set; }
        void Truncate<T>() where T : class;
        void Delete<T>() where T : class;
        DbSet<T> Set<T>() where T : class;
        int SaveChanges();
    }

    public class CpuAppDbContext : DbContext, ICpuAppDbContext
    {

        public CpuAppDbContext() : base()
        {
        }

        public CpuAppDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
        }

        public virtual DbSet<User> Users { get; set; }


        public void Truncate<T>() where T : class
        {
            this.Truncate(this.GetTableName<T>());
        }

        public void Delete<T>() where T : class
        {
            this.Delete(this.GetTableName<T>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new UserConfig());
        }
    }
}

Delete

Using Name

Db.Delete("Users");

Using Type

Db.Delete<User>();

Use Db Transaction

using (var tran = Db.Database.BeginTransaction())
{
    try
    {
        Db.Delete<User>();
        tran.Commit();
    }
    catch (Exception ex)
    {
        exception = ex;
        tran.Rollback();
    }
}

Truncate

Using Name

Db.Truncate("Users");

Using Type

Db.Truncate<User>();

Clear

Using DbSet<T>

Db.Users.Clear();
Db.SaveChanges();

Using DbContext

Db.Clear<User>();
Db.SaveChanges();

Limitations

• Delete() and Truncate() SQL statements execute immediately, regardless of whether or not we call Db.SaveChanges().
• We will not be able to unit test Delete() and Truncate().
• Clear() is slow with a large dataset but can be unit tested.
• Code tested with SQL Server, Oracle.

References

• Get table name: https://stackoverflow.com/questions/6598857/dbset-table-name

About Code Sample

• Visual Studio 2019 Solution
• .NET Framework 4.7.2
• EF 6, also tested with 5

Database.Test is an intrigued unit test project. Change the connection string in App.config. Create Users table in db, check db.sql of project Database.Application. Check/Run tests of EfCleanHelperTests.cs and EfCleanHelperUnitTests.cs.

<connectionStrings>
    <add name="DefaultConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Cup;
                              Integrated Security=True"
            providerName="System.Data.SqlClient" />
</connectionStrings>

DROP TABLE IF EXISTS [dbo].[Users]
GO
CREATE TABLE [dbo].[Users](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [CreatedBy] [nvarchar](max) NOT NULL,
    [ModifiedOn] [datetime2](7) NULL,
    [ModifiedBy] [nvarchar](max) NULL
)

History

• 28^th December, 2021: Initial version