The Alternative to Deleting Data in .NET 

CRUD stands for C - create R - read U - update D - Don't delete data

For completeness someone should mention SQL Server "Temporal tables" which automate soft delete out of the box. Temporal Tables also tick the audit changes requirement if the business require that as well.

I don't think this is the "correct" way to delete data. It's a way to soft delete data, IE hide inactive or obsolete data. :)

After decades of using both soft and hard delete, soft deleted adds more complexity and issues than it tries to solve. Other measures are better used to replace a soft delete, like an audit log as one example.

Love your videos, but just to make sure. This does not apply with GDPR rules right? If the model would soft delete user data.

So why don't have just DeteledAtUtc?

Also you may need to add filter on your unique indexes to not include deleted entities. So you won't get an exception when adding if there is a deleted entity with the same unique key.

I worked with a vendor application where their approach was to have a secondary audit schema which tracks changes to key tables in the main schema through db triggers. This seems preferable to me for many reasons. (doesn't bloat the main schema, separation of concerns, better performance, can have multiple audit records for each source row, different permissions for audit schema)

Isn't this highly dependent on your use case? this has some drawbacks like anything and this is only applicable for some cases

I don't like soft delete, I use a history table that contains all deleted and updated data so I can keep tracking the state of the entity easily, which is a generic table with jsonb column contains the data, column for data table name, column for userId who performed this action, date column. with this Iam avoiding duplicate keys problem in soft delete, i can restore entity to any state I want. beside that it satisfies my needs

Thanks for sharing. I would recommend to use a byte rather than a bool since it is possible that an entity is part of a hierarchical records. Sample Schema Department - Id (key) - ParentId (FK) - Name - IsSoftDeleted (byte) Sample data: Record 1: - Id: 1 - ParentId: NULL - Name: Engineering - IsSoftDeleted: 0 Record 2: - Id: 2 - ParentId: 1 - Name: Electrical Engineering - IsSoftDeleted: 0 Record 3: - Id: 3 - ParentId: 1 - Name: Mechanical Engineering - IsSoftDeleted: 0 Item 3 Deleted: Record 1: - Id: 1 - ParentId: NULL - Name: Engineering - IsSoftDeleted: 0 Record 2: - Id: 2 - ParentId: 1 - Name: Electrical Engineering - IsSoftDeleted: 0 Record 3: - Id: 3 - ParentId: 1 - Name: Mechanical Engineering - IsSoftDeleted: 1 Item 1 Deleted: Record 1: - Id: 1 - ParentId: NULL - Name: Engineering - IsSoftDeleted: 1 Record 2: - Id: 2 - ParentId: 1 - Name: Electrical Engineering - IsSoftDeleted: 1 Record 3: - Id: 3 - ParentId: 1 - Name: Mechanical Engineering - IsSoftDeleted: 2 In this way, when they reverted back Item 1 they need to decrement the "IsSoftDeleted" and be able to retain only those child items that were not deleted when the parent was deleted. Sorry for the long comment, better to provide sample rather than describing it :)

Keeping "deleted" data in your DB is going to get you into trouble in the EU because General Data Protection Regulations enshrine the principle that data must not be kept for "longer than necessary". Data that is "deleted" is clearly no longer necessary, so.....?

We use soft deletes and query filters at my job which is nice, but it becomes annoying when we are using composite keys which also have a soft delete for auditing purposes. When you go to try to add a new one after deleting the first you end up with a key conflict. Depending on the developer we normally have to either hard delete the entity and re-add or un-soft delete the original entity. I've also considered including the deleted time in the key which should also resolve the issue. Which makes more sense?

Great advice as always. Love dynamic query filters but there has been a long outstanding issue regarding the nested sub queries being created by ef core when you use them

EF does not support multiple query filters applied to same entity nor filters through inheritance. If you want to make it beautiful, you would add query filter to ISoftDeletable interface, not to Movie entity. In addition, you may have other interfaces also defining their filters and it should all be combined for each "true entity" to apply all the filters applicable. Unfortunately one must create their own "expression combiner" that scans through the inheritance tree of each Entity and then applies the combined expression of all found filters as a single filter to each specific entity that had any filter in their inheritance tree. Why, oh why? Been there, done that. Created my expression combiner and entity scanner to do all that...

I hate this approach: managing or hiding deleted data is, in my opinion, not something a database client should do or be worried about. I should be able to just run a delete statement and let the DBMS do its thing. Same for updates. A good database has mechanisms for this kind of stuff. And reverting back to a previous state should be the exception of exceptions.

Regarding the 'DeletedAtUtc' column, most of the time it's not really needed since usually each table will have an "UpdatedDate" column for an indication when was the last time the entry was updated. Since "deleting" an entry will usually be the last action being performed on it, this will be enough.

Nice approach, Nick, but it's only half the story. You could use a SaveChangesInterceptor to globally switch EntityState.Deleted to EntityState.Modified and handle DeletedAt and IsDeleted in one place when saving to the DB + helping avoid accidental data deletion when soft delete is implemented.

Awesome Nick. Hope to see more videos like this in future :) Thanks for this video.

You could also have IsDeleted column in table Movies and create a view vMovies which has a filter WHERE IsDeleted = 0. In every query, you would need to use the view instead of the table.

The issue with performance is not about indexes, it's about related entities which all have to be checked. E.g. if your movie must have a director, that director must have a country, county must have capital and so on - to select all movies you have to check that their directors are not deleted, their countries are not deleted, their capitals are not deleted and so on. You can disable that, but you must understand consequences (either implement cascading deletion yourself or restrict deleting entities that are depended upon).

Soft delete seems to be a good idea until you add a record and get a duplicate key violation. It also hides constraints violations when you can delete some records without deleting related records or delete a record even though other records refences it. A much better way is to use the temporal tables that keeps track of changes as well

I do "mark as deleted" for base data like products, people, payment methods, etc, because these things are connected to many transactional things like sales, cash registry, storage, finances, etc and if I allowed the system to delete these things then I'd have to delete the transactional registry too and that's an unacceptable data loss For transactional data, I just delete them, if the user said delete, then it gets deleted, I then write to an auditing table saying who deleted, what, when, did it have a monetary value to it, what observations the user entered into the system to delete that transaction, etc.

Hey Nick, have used this in a real application? It brings a lot of overkill pain, imagine a table with a string column which is index and unique set to true, soft delete is easy, but then another user try to enter the same name, but he can because is already there, the user says No I dont see the name there,is there but is marked as deleted, if you need that name than I need to undelete, what if the other properties of the item are different, ….. so when something is deleted must be deleted, if is important table implement hystory table, otherwise the code will become messy because somebody wants to keep the trash 🤣

Creating an index that includes more than 10% of a database is useless. Non-deleted items will ALWAYS be a majority of the database in most databases, so that index is actually harmful.

This is great and all, but one common issue I've run across many times in the past, is a "show deleted" feature as well. As you explained, we can simply ignore the query filters.. but that ignores all the query filters. I'm not sure if there's a new way of creating .NET query filters for EF Core, but it would be nice if we could selectively pick which query filters to ignore - or not ignore. Implicit vs explicit. Sometimes you have a bunch of query filters, such as user limitations or an tiered application. Then you need to have your query filter logic elsewhere and re-implement them those specific places where you don't need them after ignoring query filters. Let me know if there's a smarter way to do this, than having to do it manually.

You can also just use another table to store the data and rollback from it (either as columns or as JSON), it comes with the additional benefit that you can modify the main table with migrations without worryings about losing data (mainly in the JSON format, but with it comes the cost of implementing your custom rollback solution that maintains consistency between the main table and the history table).

1. You must still physically delete the data after quite a while. Ignoring GDPR would be a very bad idea. (Approach: Hangfire+Cascading deletes) 2. I'd use EF interceptors to make the delete state update the IsDeleted property and... 3. I'd make the IsDeleted property computed in the model builder and make it point to the DeletedAt date. (in both, the computed SQL and c#)

I like the approach where you make a base SoftDelete class and define the soft delete fields there and you just use this as base class for soft deleteable entities. Also I'm overriding savechanges and savechangesasync and detect the entity state using changetracker list of changed entities, if its set to deleted then i change it to update and set the soft delete fields, all to those entities which derive from the softdelete class. In this way its prettier and you can use delete calls like usual, the only thing you cant use normal way are the executedelete calls, gotta use executeupdate ones instead. Also using a CreatedAt, ModifiedAt and ModifiedBy fields and filling them accordingly by entity state, so added state fills created fields, modified fills modified fields etc.

It is not good idea to create index on boolean property, there is two possible values, the index selectiveness will be 50% or 0.5.(((

I missed where the ISoftDelete interface, created at the start of the video, was used. What was its purpose? Is it just informational to document the code? Also, I don't understand why both HasQueryFilter() and HasFilter() were needed at the end. They seem to have the same purpose. Do I need both of them?

I've been doing something very similar to this, except I override the save changes and look for implementors of ISoftDeletable and change state to modified and write delete date/time.

Nice Nick. Have found query filters useful for multi-tenancy scenarios as well.

Because everything is with Entity Framework, it is not the best optimization

third. who cares

I would love to see this implementation done well in Dapper, even though it's just manual control of the properties. I still think it would be a good idea to create the architecture for it, even if there are no global filters.

Another nice way to implement soft delete is using shadow properties, so you don't need to add IsDeleted and DeletedAt in your entity model.

You remind me when i deleted a table on production 😂 Good old days

thanks for the video! Genuine questions: 1) I'm not sure if you mentioned why you need to have two fields vs just the date (and doing the null check for the boolean). and 2) Once you have the global filter, could you simplify the 'delete' logic to not have the same check?

Future video idea: benchmarks on hard and soft deletes.

You should have who deleated it as well.

Hello Nick. I'm wondering on this topic, having an ISoftDeletable interface introduces the concept of deleted objects into your domain. This can be find depending on the usage, but if you don't want to "work" with deletable object, can't you only augment the definition of your db model with the modelBuilder to manually add the deletion's properties ? I'm curious to know if you've considered it, or if it is even relevant to "hide" the deletion data from your domain. Any thoughts ? cheers!

Soft delete isn't an anti-pattern?

This is very basic for soft deletion, the same applies to data change (created by, created at, updated by, updated at).

I don't like soft delete. It looks simple, but it can introduce problems in relational databases. For example, after a user's account was deleted due to inactivity, they were denied registering with the same email address because the column 'email' had a unique index.

All you need is a nullable datetime field. The null is you bool.

I use soft deletion instead of hard deletion for root entities. If you delete a root entity you can lose a lot of dependent data.

Really powerful feature and technique! But use with caution… as a user I would not be happy to have data leaked, that I deleted years ago… also might have legal consequences. Just keep this in mind.

so its about creating an archieve or creating a recycle bin not acually deleted i had this in mind but i ddint know how to bllue print it but thnks nick u throw some good light on it

What happens if you have a dependent entity on movie and then you query those entities, will it filter out the ones that were associated to the deleted movie?

Actually you dont need both columns in a table (IsDeleted and DeletedAtUtc), you only need DeletedAtUtc

I have a question regarding handling unique columns in this context. For example, I have an entity that must have a unique name within the system. When I soft delete an entity, I want to allow the creation of a new entity with the same name. However, if I later decide to restore the soft-deleted entity and another entity with the same name has already been created, this could lead to potential conflicts. What strategies or best practices can I employ to effectively manage these scenarios and ensure data integrity?

Do not, in an RMDBS, "mark a row as deleted" If you do this, you will have many tables, possibly each with a large number of rows, where your queries now need to include where conditions specifying if the record is "deleted" or not which will then need indexes and include columns to try to make those queries sane. For each table in the query A safer option is to have a sister table for "deleted" objects, possibly in another schema with the same table name, where only restoration might be potentially difficult.

I personally like using the Ardalis Specification pattern instead for reusable logic with EF Core. It gives you way more flexibility than a brute force, all-or-nothing approach. Good info though!

Hey @nickchapsas , at 5:15 you are adding to the where clause in the soft delete method "where [...] && !isdelete". The filter should also apply there, why are you add it twice (filter + code)?

That's why you usually have warehouse databases/replication for archiving and report creation. You don't want to do that on live DB and live DB performance might degrade over time (depending on indexes, queries, DB setup etc.) if there is a ton of data you are accessing. Again, depends on a case by case basis.

Interesting that you say most companies do it this way, I haven't experienced that and I've been doing it for 30 years. The exception is where referential integrity would require cascading the delete. In that case we use a similar strategy. Also, I should imagine for some products there are legal implications to a user requesting something to be deleted and it not actually being deleted. Still, an interesting topic well presented. Thank you.

Part of me says Yay, part of me says Nay, because it fully depends on use case, but not gonna lie, every time i see logical deletion it always feels like archiving and auditing were an afterthought and ld was simply a "quick fix" to postpone the inevitable... And yeah, i know, this world runs on band-aids and patchwork, but not sure if promoting it as a solution off the rip is a good idea.

We do something similar in our systems. I wouldn't use the boolean. In MS SQL I can still create a filterable index on the UTC date. Did not know about global filters, +1

This simple approach is a bit half-way between audit and business logic. What I mean. Either (1) soft deletes are purely for auditing and error correction, and only admins can resurrect sof-deleted objects. In this case you shouldn't have soft-delete flag and date in your domain model and use shadow properties for them and interceptors to convert delete to update. Or (2) resurrection is a part of business logic and then (see previous video) it would better to replace deleted flag with visoble in the domain model object state with corresponding state machine.

I usually implement soft deletes with just a nullable date, but never thought about the performance impact on that. Thank you for explaining both the need for a IsDeleted field and the index performance optimization by indexing it!

I did something similar just recently for deletion and update tracking. But I did it on EF level, not service level. So when saving changes, all entities requested to be deleted are actually modified with the flag being set and the timestamp being set automatically. There is really some fancy stuff you can do with EF.

I learned this concept a long time ago as "logical deletion" and I have been educated about it and EF a lot at work, however, this approach and the capability of EF Core was a bit new to me, so thanks for this. I was wondering, if something similar is feasible with revisions/history of an entity element with EF core. These days I mainly use graph databases and "nodes with history" is kind of a thing at the moment, so this was my first thought on this 😂.

06:44 I don't think it is more efficient, it performs about the same. Whether it is more elegant is very debatable. I prefer seeing when a query is filtering out items.

In case you work with DB which isn't supported by EF Core then my preferable aproach is to move the record to "archive" table, then you don't even have to think about filtering deleted data but in case you need to get deleted ones you will have to get data from both tables or create dedicated table view.

It looks nice but I have database design question about how to manage history records.. Why would I choose to keep deleted row in the original table ? I would create history table and on delete/update trigger insert the old record to history. When we keep the deleted row in same table, count of rows keeps expanding which might be a bad idea if table takes a lot of insert and deletes.

Can also use query filters for tenant ID from http context within a multi-tenant system. it would also be good to explain why soft delete is preferred over deleting data

A system I have worked on just used one field which was a nullable date time so if the date time was not null then it’s deleted otherwise it’s not

So even with GDPR companies don't delete your data 😮

Excellent advice, Nick! Thank you. Useful.

Fantastic, I had no idea about this, I will certainly be using this on my next project.

I LOVE global filters and soft deletes. I tend to use a few more properties for regulation purposes. But it works great.

I prefer auditing to soft-deletes. Just log the deletes with sufficient information to rebuild the data if needed.

Do we also need tô create the database index or just in entify tô improve performance?

I usually create another table containing the primary key of the deleted entity, this way I can create more properties that represents the deletion, is this a bad practice?

Why did you need to add the Where Statement on the Delete Async function? Wouldn't Global Filter handle that?

we also have deletedBy, even though not all tables needs soft delete we do it anyway and later run task to delete them, making some tables soft delete some are not is harder to manage in microservices when there is not enough people

In some years you need to write a Bible to do this "delete from MyTable where ColorId=5".

So this is the way to store the deleted history, thanks for this useful information ❤

Soft deletes are great until your data set is so large that SQL slows to crawl regardless

If there is a global filter, I think we can skip the filter where we are soft deleting

All this is well known, but how do you do cascade soft delete?

Just keep in mind you will be limited if have index on fields/unique constraints

reinventing privacy & data collection

I prefer to be explicit in these scenarios.

@5:53 I'm not familiar w/ the Lambda expression syntax being used. Is this a recent change in C#? Why aren't you using: .ExecuteUpdateAsync(x => x.SetProperty(p -> p.IsDeleted = true)); Or am I misreading, this and that's actually two parameters being passed to SetProperty and not just one (a Lamdba express with a comma in its RHS)?

Haha good way to keep data history )))

The best way to delete, is not to delete.

why don't you use the nullable DeletedAtUtc field as the deleted property? Is there a reason for performance at scale or no need to complicate the video ?

What about using multiple queryfilters like IsDeleted and TenentId And having options to disable a specific queryfilter

"As you added the global filter IsDeleted = 0, is it still necessary to include && !x.IsDeleted in the DeleteById method?"

I like to broaden the approach and use a column for Draft, Suspended, Pending, Finalized, Deleted. At least when you have multiple authors/system-users. The normal workflow is if your updating existing data and you need to stop mid way or need to get a second approval before finalizing it, you have two rows in the table. Different authors/system-users get their own row. Only one row ever exists as the source of truth the others can be hard deleted after finalized data is updated. The Deleted flag is just reusing the asset appropriately if the finalized data is ever considered depricated.

Great video. You mentioned you’d use both fields for reasons explained later but I guess I must’ve missed the explanation?

Do you have a video on htmx?

HARD disagree here. There rarely ever is a ' THE correct way', but softdeletes especially have so many disadvantages that it should never be the default without any consideration. The query filters also have their own set of rules, are easily missed or ignored in some cases, and you're screwed.

It's some way to delete data, but you can't say it's "The Correct Way". Not always it's correct.

Since when do query filters work with .FindAsync?

First like 😂

why do this instead of moving it to another table or database?

In a real life project this implementation is incomplete. You need to take in consideration the dependencies (cascade delete) and hierarchical structures