The Alternative to Deleting Data in .NET

Check out my courses on Dometrain: dometrain.com/
Subscribe to my weekly newsletter: nickchapsas.com
Get the source code: mailchi.mp/dom...
Become a Patreon and get special perks: / nickchapsas
Hello, everybody. I'm Nick, and in this video, I will show you a very elegant way to delete data in .NET using a technique called, soft delete.
Workshops: bit.ly/nickwor...
Don't forget to comment, like and subscribe :)
Social Media:
Follow me on GitHub: github.com/Elf...
Follow me on Twitter: / nickchapsas
Connect on LinkedIn: / nick-chapsas
Keep coding merch: keepcoding.shop
#csharp #dotnet

[@TheTigerus]

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

[@Sergio_Loureiro]

So it could be CRAP: C - create R - read A - alter P - persist, don't delete

[@vivan000]

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

[@flibbertigibbet6324]

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.

[@andreasgkizis2135]

I did not know this, Thanks !

[@ghidello]

Oh, I used to think that with temporal table a delete simply removes the row from the regular table and you have to update it first in order to have the last version in the temporal table.

[@reikooters]

Good shout on temporal tables - didn't know about this and will have to try it out. I've been using hand rolled history tables where each time we insert/update/delete we insert a row with a date range to the history table so that we can have accurate historical reporting, and sounds like this will work the same way but automatically.

[@tomheijtink8688]

I wish there was a way to only have temporal tables for delete operations. Temporal tables shine in that using cascade deletes it also ensures all related data to be deleted. And it in some cases keep the table more performant and smaller

[@cornerooster7893]

Temporal tables are great. ❤ You can delete unused records from the history table if you want!

[@astarfullofskies8735]

Your approach feels nice, Nick, elegant way for sure. However, if you’re going to implement soft delete, you might as well go all the way and create a table partition for the deleted data, so that you only query from the non-deleted data partition. This approach has the benefit of entirely alienating deleted data from the result set, instead of having the query skipping it at every execution.

[@Tsunami14]

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)

[@qj0n]

This way is better if you really need support to undo the delete, audit logs are harder in that case. Though for good undo support, one should consider event sourcing

[@Eneong]

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.

[@tonydrake462]

sounds like you have another problem here - After 8 years of running this architecture I've never created an entity with a deleted key!!

[@DanWalshTV]

@@tonydrake462 A unique constraint doesn't necessarily mean a key. You could have a unique constraint on many different types of fields depending on the use-case and in those scenarios, Eneong is correct, you'd need to factor those in.

[@hck1bloodday]

is that an sql server only thing? I'm tinking on starting a project with postgres

[@KnightSwordAG]

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!

[@ebrahimalkadim7551]

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

[@ruekkart]

Like a type of event sourcing?

[@KnightSwordAG]

@@ebrahimalkadim7551 not all db systems support queries of jsonb, and even then, the query performance isn’t always great.

[@ebrahimalkadim7551]

@@ruekkart yup based on it😁

[@miroslavmakhruk4102]

I wrote my comment about history tables and then seen yours.. 🙂Totally agree.. 🙂

[@ebrahimalkadim7551]

@@miroslavmakhruk4102 hahaha not only me using this 😂😂

[@byejason]

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.

[@impero101]

That's one thing I like about document databases - when I have a situation that requires soft-delete, I can basically just move the document to a "delete"-collection. Much simpler.

[@tehaon]

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?

[@DeofolLock]

This is my exact issue with soft deletes. I don't think there is an easy solution other than what you specified. In the end, we abandoned soft deletes for using temporal tables instead.

[@amnesia3490]

We use history table aside the original table and supply the history table on delete/update triggers of original table. I am not expert in db design but I like this implementation

[@recycledsoldier]

For composite key tables, we had to do something similar by making the unique key something like [Key 1 + Key 2 + Timestamp] or something like that. Then when we add new items, we just check to see if Key 1 + Key 2 + !IsDeleted exists. If you need auditing, you should really use temporal tables or some sort of history/versioning table.

[@E4est]

The solution I came up with some years ago was to add an Id as primary key and instead make the composite key an index with unique constraint. I then added a filter to the index so it only checks for entries that aren't deleted. This way they could be removed and re-added without allowing duplicates. It still hurt to break the clean database schema over this.

[@fifty-plus]

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.

[@Robert-G]

yes, it’s only allowing to undo a delete but not a destructive change. a change log allows you to get a version at a specific time (to diff or undo) and will be a valid basis for an audit log at the same time. hopefully it’s something he talks about in a future video. he’s currently going through a bunch of basics.

[@davidmartensson273]

At least some 7 years back, updates was faster than deletes when we did tests so we used soft delete during peak hours and then hard delete in batches during slow hours to release the storage, not sure if later versions of SQL server have better solutions for that.

[@DJReRun]

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

[@miroslavkabat]

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.

[@fusedqyou]

Pointless when the correct behavior is to use the `DeleteById` method which uses the correct approach. Modifying this behavior just limits your code in case you do want to implement actual deletion, but if you really want this then I'd argue you throw an exception in DEBUG and otherwise ignore it.

[@miroslavkabat]

​@@fusedqyou If you want to implement Hard & Soft deletes, use separate DbContexts. It can be beneficial use ReadOnlyDbContext for Query and SoftDeletableDbContext for Commands and HardDeletableDbContext for hard delete Commands or moving deleted data to an archive DB after some time. Combining SaveChangesInterceptor + Global filters can make implementation of SoftDeletable instant and without any changes in codebase.

[@diadetediotedio6918]

@@fusedqyou The "correct behavior" is a bit prepotent way of saying this.

[@miroslavkabat]

​@@fusedqyou If you want to implement Hard & Soft delete at same time, use separate DbContexts. It can be beneficial to have separate ReadOnlyDbContext for Query, SoftDeletableDbContext for Commands and HardDeletableDbContext for hard delete Commands or moving deleted data to archive DB after some time. Use SaveChangesInterceptor + Global filters can make implementation of SoftDeletable instant and without any changes across codebase.

[@fusedqyou]

@@diadetediotedio6918 My point is that you are accepting the wrong approach when a correct one exists in your code base. Take it how you want, but the point is to use the correct methods.

[@PaulOliver-d2u]

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

[@casperhansen826]

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

[@VadimBolshakov]

I'm dealing with such DB right now its complete nightmare. It has almost 0 foreign keys and data integrity. All fields are nullable. It means you have to dbl/triple check values coming from your own DB!

[@VoroninPavel]

> It also hides constraints violations That's the issue. Developers are used to just put FK constraints without thinking. But in reality this implies transaction coupling and often hides a valid business case. Soft delete is just another dirty hack usually.

[@1EFeKT1]

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.

[@Mr8411s]

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 😂.

[@diadetediotedio6918]

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

[@diadetediotedio6918]

You can also use a temporal table for very sensitive data and to have db guarantees over it, some databases implement this concept.

[@haxi52]

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.

[@HikingUtah]

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?

[@thepassenger5484]

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

[@tamirlyn]

This right here. I'd appreciate presenting these things together with analysis of tradeoffs.

[@miroslavmakhruk4102]

Yep, absolutely. It's a very niche approach for some really rare use-cases... In most cases IRL it will not suffice..

[@EldonElledge]

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

[@s2ym3k]

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.

[@sanzandsanz]

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

[@o.t.1934]

Added to bookmarks. Thanks for doing your great content

[@mortenmoulder]

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.

[@cryam6428]

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.

[@gppsoftware]

I'd suggest that you are functionally overloading a single field ie it isn't 'S' in SOLID - it is being used for multiple purposes. This probably doesn't confirm with data modelling rules either because the field is not representing a single piece of information. On the one hand, you are using it to indicate what appears to be a 'workflow state' and on the other hand, one of those states appears to be 'deleted'. It will work, but I'd probably make the 'deleted' flag into a separate field. That way, if you want to 'undelete' a record, it returns the record with the same 'workflow state' that it had when they 'deleted' it. Using a single field, you don't know what workflow state it was, so then have to have rules about what to restore it as and then it isn't a true 'undo'. Similarly, if ever you want to do any kind of analysis of what states records were in when they were deleted, the single field approach doesn't support this.

[@youaremine99]

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

[@mohammadjaber3898]

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

[@michelnunes4421]

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.

[@KnightSwordAG]

@@michelnunes4421 how would that work exactly? A separate table with foreign key associations to the entity? The separate table has the isdeleted and deletedate data and then shadow property associations in ef core?

[@dorins3787]

​@@KnightSwordAGjust google on shaddow properties.. but shaddow properties do not work with NoTracking as far as I know

[@mortenmoulder]

@@KnightSwordAG I don't think so. Joins can be expensive on large data sets. Unless you query them separately, I don't think that's what he means. But I'm interested in knowing as well

[@michelnunes4421]

@@KnightSwordAG If you add something like the following, EF will create 2 columns in the entity table (1 for IsDeleted and 1 for DeletedAt) but you don't need to have those properties in the entity class, they will be created by ef in a shadow state. public static void AddSoftDeleteConfiguration(this EntityTypeBuilder builder) where TEntity : class { builder .Property("IsDeleted"); builder .HasIndex("IsDeleted") .HasFilter("\"IsDeleted\" = false"); builder .Property("DeletedAt"); builder .HasQueryFilter(x => !EF.Property(x, "IsDeleted")); } When you need to access those properties you can use EF.Prperty(entity, "PropertyName"). A soft delete will be like: _context.Sale .Where(x => x.Id == id) .ExecuteUpdateAsync(x => x.SetProperty(p => EF.Property(p, "IsDeleted"), true) .SetProperty(p => EF.Property(p, "DeletedAt"), DateTime.UtcNow));

[@steve-o2198]

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

[@ShNeoteric]

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

[@nickchapsas]

I forgot to mention it but yeah if you have to delete someone’s data you will also need to have a mechanism to do that

[@larsthomasdenstad9082]

It is absolutely not in compliance with GDPR. Still a good approach for many things. Just not in the EU if you are relating to people.

[@KnightSwordAG]

The pattern usually also employs hard deletes as a separate process run by either jobs or flags in the delete process. Usually to comply with GDPR rules. This is why the date field is so critical; it allows for the hard delete to be scheduled in the future, and still retain the soft deletes to support undos, oopsies, and other user interface creature comforts.

[@PelFox]

I prefer to anonymize instead of delete for gdpr. Changing all personal data to randomized values. That way you still have some history for reporting tools.

[@DoeIsSlapen]

@@PelFoxthat’s arguably very tricky. I would advise against it if its not mandatory to keep specific data. If there is a way to trace back patterns to a specific person, you can still be in trouble.

[@darkjudic]

So why don't have just DeteledAtUtc?

[@pagorbunov]

Heavier index and slower queries

[@ruslan9108]

We use DateCreated, DateUpdated fields and isDeleted flag. When you delete you just update DateUpdated. My point is that you can decide by yourself what would you need from your service fields. Technically create 3 date fields might be right, but you may never use it.

[@timur2887]

@@ruslan9108 I woned how DateUpdated may contain any value, more common to save table data in history like temporal tables in MS SQL

[@Sergio_Loureiro]

see the whole video.

[@figloalds]

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.

[@haraheiquedossantos4283]

Totally agree. Transaction data tends to be high in volume. If you just mark as deleted/inactivated you will eventually have performance problems overtime. Put in a audit table or migrate the cold/warm with the objective of partitioning the data are the best approachs IMO

[@dinov5347]

Soft deletes will create other performance issues. For example, if you have existing indices for other columns now you have to add the isDeleted to that as well. Basically you are implementing partitioning as soon as you introduce soft deletes. Plus you can't do use unique indices anymore due to the isDeleted flag (you can create multiple records with deleted true and the unique column.

[@sonics5439]

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.

[@canabale]

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

[@troncek]

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.

[@fishzebra]

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

[@MrStupiuno]

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!

[@Kommentierer]

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.

[@СергейИванченков-ДляСвоих]

Excellent advice, Nick! Thank you. Useful.

[@timlong7289]

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.....?

[@VadimBolshakov]

We "deleted" YOUR data. Right... How do you know its my data...

[@nerfzinet]

With the DeletedDateUtc field you could have a scheduled job that hard deletes personal data after a few weeks where necessary. This would allow users to recover their data within a reasonable time frame if the deletion was accidental or something, and conform to the GDPR which from what I can tell requires that the request is acted upon within 30 days.

[@hck1bloodday]

The GDPR is about personal identifiable data, you can update the personal data qand keep the rest, and will not be violating GDPR

[@TanelTeede]

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.

[@dominiqueruest4780]

This is how a journey called "Event sourcing" begins.

[@taylor.w.merritt]

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

[@nickchapsas]

I think I forgotted to mention it but basically it's because in EF core the filter isn't computed on every executuion so if I set it to be = DateTime.UtcNow it would only remember the first DateTime.UtcNow value

[@noelfrancisco5778]

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 :)

[@canabale]

Why not just use cascading deletes?

[@noelfrancisco5778]

Hi @@canabale, do you mean using constraints? If that's the case, will that be a hard delete rather than soft delete?

[@GigAHerZ64]

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

[@mortenmoulder]

This. Important to know. I wish EF Core had a way of adding filters, that could be defined elsewhere, so we can ignore specific filters.

[@MaiconLLoti]

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.

[@codingbott]

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)?

[@jeffersonamoridarocha1061]

Future video idea: benchmarks on hard and soft deletes.

[@Maxim.Shiryaev]

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.

[@ANav-in7vh]

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

[@dmitriibessonov1964]

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?

[@amnesia3490]

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.

[@lpussacq]

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!

[@GBPhilip]

I was wondering about the use of Shadow properties for IsDeleted, to avoid the pollution of the domain model. I've hit the problem at the moment of not be able to include them in a query filter though. More reading required

[@MonsterLlama]

@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)?

[@digvijayad]

He is chaining the two .SetProperty methods. One is for setting the IsDeleted and the other for DeletedAtUtc. So basically, x.SetProperty(...).SetProperty(...); this is the same as having two separate calls to x.SetProperty for each of the props. His formatting was weird for this, I usually align the periods when chaining. movie .SetProperty(...) .SetProperty(...);

[@MonsterLlama]

@@digvijayad That's not what I was referring to. It looks like the SetProperty method takes two arguments, but since I'm not familiar with it I thought he was passing just one agrument with a weird Lambda expression format that I've never seen before. I was interpreting the below call as taking just one argument: SetProperty(x => x.IsDeleted, true) so the RHS would be x.IsDeleted, true which confused me as I have never seen a comma like that on the RHS of a Lambda expression.

[@TheSadman2001]

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?

[@paulorgmalheiros]

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

[@GeorgiShukov-e2c]

Why you are using !x.IsDeleted in the query for deleting if you have the global filter for the table that prevent loading of already deleted entities?

[@jaja8376]

I really like the video but i do think it would be good to add more definition as to why the time is relevant.

[@fritzfahrmann4730]

is the filter also working for nested objects?

[@RohitMoni0]

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?

[@cristiz-vf4ww]

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?

[@dorins3787]

No. Because you do not have "cascade" on soft delete. Context.MovieDetails.Where(x=> x.MovieId==1) will get rows event if context.Moviea.FindAsync(1) will not

[@pilotboba]

Why would you query a dependant entity? You shouldn't even have dbset's for dependant entities.

[@mortenmoulder]

Yes. Query filters are applied "globally", so they are filtered out with relationships as well

[@dorins3787]

@@mortenmoulder not quite.. you have the filter on an entity. Not on the relations. If you have the dbsets for the dependent entities (can be the case, say that you soft delete an user), you will see the data.

[@mortenmoulder]

@@dorins3787 If I set up my relationships, say parent and children, and I have the deleted query filter on the child entity, I will not see the children that are deleted, when I query the parent and make EF do the auto joining for me. I think we mean the same thing and are just misunderstanding each other

[@kabal911]

Since when do query filters work with .FindAsync?

[@cdnkarasu]

Years ago in our health related apps we use soft delete for deletions and edits/versions but now I prefer to use SQL Temporal Tables.

[@onistag]

I was just thinking this. I use Temporal Tables in my latest project at work, with no soft delete. We can always go into the history and get that last record if we need it to restore it.

[@gppsoftware]

That is another option, but does EF support it now ? It didn't used to.

[@cdnkarasu]

@@gppsoftware Yes

[@marcobaccaro]

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

[@KalleBlixtHagholm]

Could perhaps the query filters be used to filter out data per user or role, so that only some clients can access data while others simply don’t see it?

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

[@teamdroid9834]

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

[@oldmanjeb384]

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

[@fifty6737]

reinventing privacy & data collection

[@Tony-dp1rl]

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.

[@erkan2284]

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

[@OldWiseLlama]

About that filtered index. At least in the past, and at least with MS SQL Server, filtered indexes haven't worked that well with parameterized queries. And with EF pretty much everything is parametrized so the query plan probably doesn't even use the filtered index.

[@SuperLabeled]

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

[@alexanderpoplooukhin7448]

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.

[@gppsoftware]

You only loose dependent data if your database doesn't have referential integrity (foreign keys) configured. If you do have them, all SQL Server implementations will prevent such a delete and you won't loose anything. Assuming you aren't using cascading deletes. This is a problem I see in every 'no sql' database because they don't support foreign key constraints so when a root record is deleted, there is nothing to protect it and prevent orphaned records. How anyone can build a reliable database with such inferior technology is beyond me, but apparently, current 'fashion' says it is acceptable!

[@MichaelKolczynski]

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.

[@MichaelKolczynski]

Or as another said - use "temporal table" features

[@andmatic]

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

[@MaxiTB]

It's a nice concept, but be aware of privacy laws. Most countries don't allow data to exist beyond a certain business case is resolved except when it's legally required (for example for tax purposes, and even that is time constrained - often around one or two decades). So it is super important to have proper data retention in mind, otherwise you can run in a lot countries into serious law violations that could end up costing the company billions. So yes, soft delete is a useful feature, but no, leaving data in your database (or in any storage) can result in serious legal issues for whoever is the data owner.

[@hohohomeboy]

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.

[@tonydrake462]

seperate issue - the 'right to forget' is handled by scrubbing everything - so a seperate issue - We have a specific function the removes any 'personal' details as per the law.

[@sp11-mp9ti]

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.

[@patrick71994]

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.

[@KnightSwordAG]

That's part of the reason for the QueryFilter abstraction in EF Core. So that ordinary queries are not impeded by the very real requirement for soft deletes to support things users want, like historical references and the ability to undo deletions. Databases in and of themselves are not able to accomplish this without considerable setup, and it could be argued that they are not the correct tool for the job, and that's a fair argument to have. But I challenge you then to tell me what the right tool should be, and how likely most of us are going to get that tool.

[@antonmartyniuk]

You could have reasons to keep data in the database. Like when an employee quits the job, all his comments and created issues will remain in the database

[@patrick71994]

@@KnightSwordAG one system that you can use are temporal tables in MSSQL. It also has support in EF for it to get the history of a record, you see what values have changed over time and you can see how the tables was in a point of time. And it all works invisible of the rest. You just insert, update and delete records and the system does the rest. So, that is one way to get it at a database level, where every database client just works without thinking about it. Even just writing a simple sql-script works. MySQL and other DMBSes also have features like this.

[@panbotuk]

Soft delete isn't an anti-pattern?

[@T___Brown]

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

[@ErazerPT]

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.

[@amrosamy8232]

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

[@pilotboba]

afaik IgnoreQueryFilters ignores ALL query filters. So, you would still need to add a where on your tenant_id if you are getting deleted but still want only for a tenant_id.

[@michaelakin766]

Could you have removed the !IsDeleted check on the delete method?

[@patfre]

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

[@mauritsvdp406]

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 ?

[@nickchapsas]

I forgot to mention it in the video but it’s because a global filter is computed once which means if I only used the date field it would only work for the first query

[@NickFiorello]

@@nickchapsas I would not have expected the filter (e => e.DeletedAtUtc != null) to change over time. Can you elaborate?

[@lucianbumb6690]

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 🤣

[@argentum..]

Index filter is enough (as mentioned in the video), but yes, one step more

[@menuaayvazyan]

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

[@RuneBivrin]

That's true, but that's not what a filtered index does. A filtered index only maps rows where the filter condition is met, so by excluding rows that has IsDeleted = 0, the index is on only the rows that have not been deleted. And by making that index UNIQUE, the Find() method works, since the index is on the ID column.

[@phw1009]

It could be better if you introduce 'SavesChangesInterceptor' of EF.

[@timur2887]

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

[@DevMeloy]

Most places I've worked used some iteration of soft delete. Not sure the reason for 2 fields, I'd just derive deleted from a date field.

[@gppsoftware]

Two fields: date deleted and who deleted ?

[@DevMeloy]

@@gppsoftware mmm... Maybe? ModifiedBy? But change auditing would give better ROI...IMO

[@gppsoftware]

@@DevMeloy I use 4 fields on tables that require it: createdby, createddate, modifedby and modifieddate. Delete is either hard or soft depending on requirements. ModifiedDate is also used to control 'lost updates' with concurrent users. Change auditing is another option, but again, it depends on requirements: if full auditing is required, then it's fine, but if only the last change is needed, it can be overkill.

[@DevMeloy]

@@gppsoftware yeah, I've worked in both fully audited and just ModifiedBy schemes. Sounded like Nick wanted both IsDeleted and a date field. Building any system, I'd prefer soft delete unless the data truly doesn't matter.

[@mattymattffs]

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

[@miroslavkovac8888]

Do you have a video on htmx?

[@pilotboba]

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

[@Bear197504]

What is the "correct" way ?

[@pilotboba]

@@Bear197504 I mean deleting data. Lol.

[@qj0n]

@@Bear197504 from security point of view, deleting should be an irreversible operation. If you are required to delete data, you not only should do the hard delete, but also guarantee that it's not stored in some hard drive

[@RiversJ]

Actually delete it, this works for soft-delete nicely but sometimes delete means you absolutely must ensure the data doesn't exist anywhere anymore, not in Db, not in Elastic and not in Cache. Say customer data deletion for GDPR purposes.

[@prman9984]

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.

[@dorins3787]

True. Also, an index on boolean is useless