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()
.
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
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
- 28th December, 2021: Initial version
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.