Implementing soft delete in EF Core using triggers
Image from Pexels by Poppy Thomas Hill

Implementing soft delete in EF Core using triggers

Entity soft delete implementation in EF Core

Implementation approach for soft delete is more-less straight forward thing. Instead of deleting data record, you introduce a flag which will mark the record as deleted and you can simply filter out on it and not show the data with the flag set. This way you do not loose data, but you restrict the end user to see it.

This way of "deleting" data records, or better said marking them as deleted applies for certain types of applications, but sometime this requirement may come later as a new feature while the actual delete is in place. This can trigger bunch of refactoring and regression testing to ensure the records are not actually deleted from the database.

Luckily, if you are using EF Core to manipulate the data, you can simply add triggers for the specific entity type and intercept and alter the entity state.

In the following example, I will be using a simple sample ASP.NET Core project to store Items, Orders and OrderItems for a sample store. Initially, API will provide Item DELETE endpoint which will actually delete the record from the database, but I will update the solution later on to use NuGet package EntityFrameworkCore.Triggered to setup triggers for specific entity when it changes state to delete and instead mark the flag property and not actually delete the record.

Initial solution without soft-delete

I created initial sample ASP.NET Core solution which simply deletes the Item entity. These are three main entity models that we store in out database

public class Item
{
public int Id { get; set; }
public String Name { get; set; }
public String Description { get; set; }
public virtual ICollection<OrderItem> OrderItems { get; set; }
}
public class Order
{
public int Id { get; set; }
public DateTime CreatedOn { get; set; }

        public ICollection<OrderItem> OrderItems { get; set; }
    }
	
    public class OrderItem
    {
        public int OrderId { get; set; }
        public int ItemId { get; set; }
        public int Quantity { get; set; }

        public virtual Item Item { get; set; }
        public virtual Order Order { get; set; }
    }


    

I created an endpoint to delete Item directly from the API.

    [ApiController]
    [Route("[controller]")]
    public class ItemsController : ControllerBase
    {
        readonly StoreDbContext _dbContext;
        public ItemsController(StoreDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        [HttpGet]
        public async Task<IEnumerable<Item>> Get()
        {
          return  await _dbContext.Items.ToArrayAsync();
        }

        [HttpDelete]
        public async Task Delete(int id)
        {
            var item = await _dbContext.Items.FindAsync(id);
            if (item != null)
                _dbContext.Items.Remove(item);

            await _dbContext.SaveChangesAsync();
        }
    }
    

Trigger Swagger 

This also means cascade delete of OrderItem record and by deleting OrderItem record you lose tracking of the items contained in old orders. Ideally, if we do not have the specific item in stock or we are not selling it anymore, we just need to hide it from the list of items, while keeping them still visible in the existing orders.

To test the initial code behavior, I used NuGet package EntityFrameworkCore.SqlServer.Seeding and EntityFrameworkCore.SqlServer.Seeding.Tool global tool to add some sample data.

First I ran the tool create and include data seeding file

seeding add "Add_Initial_Items_Orders" -o Seedings

And then I added the following SQL script snippet to newly added .sql file to seed the data

DECLARE @mouseId INT
INSERT INTO Items([Name], [Description]) Values ('Logitech MX Master 2s', 'Logitech wireless mouse')
SET @mouseId = @@IDENTITY

DECLARE @keyboardId INT
INSERT INTO Items([Name], [Description]) Values ('Microsoft Sculpt Ergonomic keyboard', 'Microsoft ergonomic keyboard')
SET @keyboardId = @@IDENTITY

DECLARE @speakersId INT
INSERT INTO Items([Name], [Description]) Values ('CREATIVE Pebbles', 'Desktop speakers')
SET @speakersId = @@IDENTITY

DECLARE @orderId INT
INSERT INTO Orders(CreatedOn) values (GETUTCDATE())
SET @orderId = @@IDENTITY

INSERT INTO OrderItems(OrderId, ItemId, Quantity) Values (@orderId, @mouseId, 3)
INSERT INTO OrderItems(OrderId, ItemId, Quantity) Values (@orderId, @keyboardId, 1)
INSERT INTO OrderItems(OrderId, ItemId, Quantity) Values (@orderId, @speakersId, 2)
    
Note

Migration and seeding are already included in the application pipeline, so once you clone the code repository to your machine, after configuring the connection string in appsettings.json you should have the database structure along with sample data seeded upon application start

Instead of rewriting the delete method in the controller, we'll use triggers to mark the item as deleted.

Soft-delete trigger handling

To introduce soft-delete functionality without actual change in the delete method, we'll first add reference to EntityFrameworkCore.Triggered nuget package

dotnet add package EntityFrameworkCore.Triggered --version 2.3.0

The next thing, before we even write out triggers, is to mark entities that we want to make soft-deleteable. I will simple do this with an interface which will also force the implementation of deleted flag property.

I decided to have deleted flag stored as nullable DateTime, so that when we have the value present we know that item is deleted and we also know the time soft delete action occurred.

    public interface ISoftDelete
    {
        public DateTime? DeletedOn { get; set; }
    }
    

Now we need to implement this interface for each entity we want to apply soft-delete to. In our case this will be Item entity so therefore we need to reference and implement this interface.

    public class Item : ISoftDelete
    {
        public int Id { get; set; }
        public String Name { get; set; }
        public String Description { get; set; }
        public virtual ICollection<OrderItem> OrderItems { get; set; }
        public DateTime? DeletedOn { get; set; }
    }
    

There is one more thing to do before we introduce the trigger and that is creating and running the migration for the newly updated entity. Once the migration is executed, we'll have the new column in our Items table.

We have all in place to add out trigger, so let's do it

    public class SoftDeleteTrigger : IBeforeSaveTrigger<ISoftDelete>
    {
        readonly StoreDbContext _dbContext;
        public SoftDeleteTrigger(StoreDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task BeforeSave(ITriggerContext<ISoftDelete> context, CancellationToken cancellationToken)
        {
            if (context.ChangeType == ChangeType.Deleted)
            {
                var entry =_dbContext.Entry(context.Entity);
                context.Entity.DeletedOn = DateTime.UtcNow;
                entry.State = EntityState.Modified;
            }

            await Task.CompletedTask;
        }
    }
    

This trigger will now be hit every time there is a change on any entity which implements ISoftDelete interface. This basically means that if there is a requirement to introduce soft delete for any other entity, all we need to to is to implement this interface in the entity class and run the migration so that we have the new field created as a column in the database.

I you look at the code of the trigger class, you will see that it is quite simple. If change type is deleted, we just assign the current date time to DeletedOn property and we switch the entity state to Modified so that EF does not delete the entity when we perform saving of changes.

The last step for this is to register triggers to DI in Startup.cs class. You can register one by one trigger with extensions that come as part of EntityFrameworkCore.Triggered package or you can, which my preferred way to register all triggers from the assembly via EntityFrameworkCore.Triggered.Extensions nuget package.

            services.AddDbContext<StoreDbContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString(DbContextConfigConstants.DB_CONNECTION_CONFIG_NAME),
                    x =>
                    {
                        x.MigrationsHistoryTable("__EFMigrationsHistory");
                        x.MigrationsAssembly(this.GetType().Assembly.GetName().Name);
                    }
                );
                options.UseTriggers(triggerOptions => {
                    triggerOptions.AddAssemblyTriggers();
                });
            });
    

Complete source of the of sample project is awilable at github https://github.com/dejanstojanovic/efcore-triggers-softdelete

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article