Delete All Rows in Entity Framework Core 6

• Download source code - 19.8 KB

Background

Sometimes, we may require to delete all the records from the table using Entity Framework Core. One of the commonly used approaches is to iterate each row and use the DBSet<T>.Remove() to delete each row as shown below:

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, are going to check alternative options, like running Truncate/Delete commands and unit testing options.

Clean Helper

The idea is to use a raw SQL query to Truncate a table or Delete everything from a table.

Annotation Helper

Annotation helper is to get table name and schema from entity framework mapping configuration of a model.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EfCoreHelper.Database.Core
{
    public class AnnotationHelper
    {
        private static string GetName(IEntityType entityType, 
                                      string defaultSchemaName = "dbo")
        {
            /*3.0.1 these were working*/
            //var schemaName = entityType.GetSchema();
            //var tableName = entityType.GetTableName();
            /*5 and 6 these are working*/
            var schema = entityType.FindAnnotation("Relational:Schema").Value;
            string tableName = entityType.GetAnnotation
                               ("Relational:TableName").Value.ToString();
            string schemaName = schema == null ? defaultSchemaName : schema.ToString();
            /*table full name*/
            string name = string.Format("[{0}].[{1}]", schemaName, tableName);
            return name;
        }

        public static string TableName<T>(DbContext dbContext) where T : class
        {
            var entityType = dbContext.Model.FindEntityType(typeof(T));
            return GetName(entityType);
        }

        public static string TableName<T>(DbSet<T> dbSet) where T : class
        {
            var entityType = dbSet.EntityType;
            return GetName(entityType);
        }
    }
}

Extension Methods

Along with Microsoft.EntityFrameworkCore, we need to install Microsoft.EntityFrameworkCore.Relational. This will enable us to use ExecuteSqlRaw() to run row query in Entity Framework Core.

Install-Package Microsoft.EntityFrameworkCore
Install Microsoft.EntityFrameworkCore.Relational

Here are the extension methods for both 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 Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System.Linq;

namespace EfCoreHelper.Database.Core
{
    public static class EfCleanHelper
    {
        public static string Truncate<T>(this DbSet<T> dbSet) where T : class
        {
            string cmd = $"TRUNCATE TABLE {AnnotationHelper.TableName(dbSet)}";
            var context = dbSet.GetService<ICurrentDbContext>().Context;
            context.Database.ExecuteSqlRaw(cmd);
            return cmd;
        }

        public static string Delete<T>(this DbSet<T> dbSet) where T : class
        {
            string cmd = $"DELETE FROM {AnnotationHelper.TableName(dbSet)}";
            var context = dbSet.GetService<ICurrentDbContext>().Context;
            context.Database.ExecuteSqlRaw(cmd);
            return cmd;
        }

        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 tableName, string schemaName = "dbo")
        {
            string name = string.Format("[{0}].[{1}]", schemaName, tableName);
            string cmd = $"TRUNCATE TABLE { name }";
            context.Database.ExecuteSqlRaw(cmd);
            return cmd;
        }

        public static string Delete(this DbContext context, 
                      string tableName, string schemaName = "dbo")
        {
            string name = string.Format("[{0}].[{1}]", schemaName, tableName);
            string cmd = $"DELETE FROM { name }";
            context.Database.ExecuteSqlRaw(cmd);
            return cmd;
        }
    }
}

Using the Code

Delete

Using Name

Db.Delete("Users");

Using DbSet<T>

Db.Users.Delete();

Use Transaction

using (var tran = Db.Database.BeginTransaction())
{
    try
    {
        Db.Users.Delete();
        //or
        //Db.Delete("Users");

        tran.Commit();
    }
    catch (Exception ex)
    {
        tran.Rollback();
    }
}

Truncate

Using Name

Db.Truncate("Users");

Using DbSet<T>

Db.Users.Truncate();

Clear

Using DbSet<T>

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

Using DbContext

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

Unit Testing

Let's add packages to the test project:

• Moq used for entity framework DbContext mocking
• NBuilder to populate sample test data

Install-Package Nunit
Install-Package Moq
Install-Package NBuilder

Clear methods are good for unit testing. But Truncate and Delete methods are running raw SQL, so for unit testing, we need to bypass them.

Add Things in Db Context

Here, the CpuAppDbContext db context class is implementing the interface ICpuAppDbContext. Which force to add void Truncate<T>() where T : class and void Delete<T>() where T : class methods, which will be used to truncate a table or delete data of a table. In the actual implemention, we are calling existing extension methods.

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(DbContextOptions<CpuAppDbContext> options) : base(options)
    {
    }
    public CpuAppDbContext() : base()
    {
    }

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

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

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new UserConfig());
    }
}

Using Methods In Actual Code

Db.Delete<User>();
Db.Truncate<User>();

Test Methods

Helper method to convert a List<T> to DbSet<T>:

public static class TestHelpers
{
    public static DbSet<T> ToDbSet<T>(this List<T> list) where T : class
    {
        IQueryable<T> queryableList = list.AsQueryable();
        var mockSet = new Mock<DbSet<T>>();
        mockSet.As<IQueryable<T>>().Setup(m => 
                             m.Provider).Returns(queryableList.Provider);
        mockSet.As<IQueryable<T>>().Setup(m => 
                             m.Expression).Returns(queryableList.Expression);
        mockSet.As<IQueryable<T>>().Setup(m => 
                             m.ElementType).Returns(queryableList.ElementType);
        mockSet.As<IQueryable<T>>().Setup(m => 
                             m.GetEnumerator()).Returns(queryableList.GetEnumerator());
        return mockSet.Object;
    }
}

Unit Testing Using Interface

Here, we are bypassing/mocking the Delete method:

[Test]
public void DbContext_Moq_Using_Interface()
{
    List<User> users = Builder<User>.CreateListOfSize(10).Build().ToList();
    bool isCalled = false;

    var dbMock = new Mock<ICpuAppDbContext>();
    dbMock.Setup(x => x.Users).Returns(users.ToDbSet());
    dbMock.Setup(x => x.Delete<User>()).Callback(() =>
    {
        users = new List<User>();
        isCalled = true;
    });

    /*will pass this db to repo*/
    ICpuAppDbContext db = dbMock.Object;
    db.Delete<User>();
    Assert.True(isCalled);
    //Assert.False(db.Users.Any());
}

Unit Testing using DbContext Class

For mocking, we must declare testable dbsets and methods as virtual. This virtual thing is also related to Lazy and Eager loading. So be sure before using or making changes to existing codes. I do prefer interface based mocking, and it is the best option.

[Test]
public void DbContext_Moq_Using_Class()
{
    List<User> users = Builder<User>.CreateListOfSize(10).Build().ToList();
    bool isCalled = false;

    var dbMock = new Mock<CpuAppDbContext>();
    dbMock.Setup(x => x.Users).Returns(users.ToDbSet());        /*in dbcontext,
          need to add virtual, public virtual DbSet<User> Users { get; set; }*/
    dbMock.Setup(x => x.Delete<User>()).Callback(() =>          /*in dbcontext,
          need to add virtual, public virtual void Delete<T>() where T : class*/
    {
        users = new List<User>();
        isCalled = true;
    });

    /*will pass this db to repo*/
    CpuAppDbContext db = dbMock.Object;
    db.Delete<User>();
    Assert.True(isCalled);
    //Assert.False(db.Users.Any());
}

Assembly Faking

To mock extension methods, Fakes Assembly can also be used.

Moq.EntityFrameworkCore

If we want to do unit testing using the context class itself, we can use Moq.EntityFrameworkCore also. But it requires EF Core 6.

Install-Package Moq.EntityFrameworkCore

Limitations

• Truncate or Delete SQL statements execute immediately, regardless of whether or not we call Db.SaveChanges()
• Clear is slower than Truncate/Delete and not good for large datasets.
• Clear is suitable for regular unit testing, Truncate and Delete are not.

References

• EF core test helper https://github.com/MichalJankowskii/Moq.EntityFrameworkCore
• Moq helper https://www.loganfranken.com/blog/517/mocking-dbset-queries-in-ef6
• Fakes https://www.youtube.com/watch?v=SkdgYG6W6X
• Lazy and Eager loading https://stackoverflow.com/questions/24079133/what-is-the-difference-between-dbset-and-virtual-dbset

About Code Sample

• Visual Studio 2022 Solution
• ASP.NET 6 
• EF Core 6,  also tested in core 5

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

{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=.\\SQLEXPRESS;
     Initial Catalog=Cup;Integrated Security=True"
  }
}

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

• 15^th August, 2022: Initial version