Click here to Skip to main content
16,015,351 members
Articles / Web Development / ASP.NET
Tip/Trick

Delete All Rows in a Table in Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.33/5 (2 votes)
28 Dec 2021CPOL2 min read 38.7K   28   3   3
Delete all the records from the table using EF
This guide explores efficient methods for deleting all records from a table using Entity Framework, discussing alternatives such as Truncate/Delete commands and providing extension helper methods and code samples for implementation and testing.

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

C#
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.

C#
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:

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

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

C#
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
C#
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

C#
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
C#
Db.Delete("Users");
Using Type
C#
Db.Delete<User>();
Use Db Transaction
C#
using (var tran = Db.Database.BeginTransaction())
{
    try
    {
        Db.Delete<User>();
        tran.Commit();
    }
    catch (Exception ex)
    {
        exception = ex;
        tran.Rollback();
    }
}

Truncate

Using Name
C#
Db.Truncate("Users");
Using Type
C#
Db.Truncate<User>();

Clear

Using DbSet<T>
C#
Db.Users.Clear();
Db.SaveChanges();
Using DbContext
C#
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

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.

XML
<connectionStrings>
    <add name="DefaultConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Cup;
                              Integrated Security=True"
            providerName="System.Data.SqlClient" />
</connectionStrings>
SQL
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

  • 28th December, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionImmediate Execution Pin
Jeff Bowman3-Jan-22 14:56
professionalJeff Bowman3-Jan-22 14:56 
AnswerRe: Immediate Execution Pin
DiponRoy3-Jan-22 19:41
DiponRoy3-Jan-22 19:41 
GeneralRe: Immediate Execution Pin
Jeff Bowman4-Jan-22 11:20
professionalJeff Bowman4-Jan-22 11:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.