The Alternative to Deleting Data in .NET

Summary

In this video, Nick demonstrates how to implement soft deletes in a .NET API using Entity Framework Core. Soft deletes involve marking records as deleted with flags rather than permanently removing them from the database. This approach allows for easy recovery of data and improves system performance. Nick explains the benefits, implementation steps, and optimizations, including adding a global query filter and filtered index for better query performance. The tutorial also covers how to handle specific cases when soft-deleted data needs to be accessed, providing a practical and elegant solution for handling deletions in any database system.

Takeaways

• 😀 Soft deletes allow data to be marked as deleted without actually removing it from the database, enabling easy rollback and auditing.
• 😀 Implementing soft deletes in .NET is efficient and can be done using Entity Framework Core, optimizing performance while maintaining data integrity.
• 😀 The `ISoftDeletable` interface should contain two properties: `IsDeleted` (a boolean flag) and `DeletedAtUtc` (a nullable DateTime to track when the entity was deleted).
• 😀 Using EF Core's global query filters, you can automatically exclude soft-deleted entities from queries, simplifying the code and improving efficiency.
• 😀 Instead of deleting entities, soft deletes update the entity's properties (marking it as deleted and setting the deletion timestamp).
• 😀 The soft delete pattern works universally across SQL and NoSQL databases, making it a versatile solution.
• 😀 Adding a filtered index on the `IsDeleted` column helps ensure that queries filtering out deleted entities remain performant.
• 😀 Soft deletes provide the ability to easily restore or recover data without needing to regenerate it or track its origin.
• 😀 Using `IgnoreQueryFilters()` allows specific queries to retrieve soft-deleted entities, providing flexibility for special cases.
• 😀 By implementing soft deletes, you avoid permanent data loss and can maintain an audit trail for deleted entities.
• 😀 Performance is enhanced by using EF Core's efficient methods, like `ExecuteUpdateAsync` for batch updates, reducing the need to read and update individual records.

Q & A

What is the concept of 'soft deletes' as explained in the video?

Soft deletes involve marking a record as deleted without actually removing it from the database. This allows for recovery of the data later and helps with performance, as the data remains in the system but is considered 'non-existent' for users.

What are the benefits of using soft deletes in database management?

The primary benefit of soft deletes is the ability to recover deleted data without regenerating it. This can help businesses rollback changes or restore lost information. Additionally, it can optimize performance by avoiding the overhead of full deletes.

Why does the speaker recommend adding two fields instead of just one for soft deletes?

The speaker recommends adding two fields—'isDeleted' (a boolean flag) and 'deletedAtUtc' (a nullable timestamp)—because the 'deletedAtUtc' timestamp allows tracking when the record was deleted. This provides additional useful information for auditing and restoration purposes.

How does Entity Framework (EF) Core handle soft deletes in the implementation shown in the video?

In the video, EF Core is used to implement soft deletes by defining a global query filter on the 'movie' entity. This filter ensures that all queries by default exclude soft-deleted entities. Additionally, EF Core’s `ExecuteUpdateAsync` method is used to update the 'isDeleted' flag and set the 'deletedAtUtc' timestamp.

What is the role of the 'global query filter' in the soft delete implementation?

The global query filter automatically excludes soft-deleted entities from any query results. This ensures that deleted records do not appear in queries like 'GetAllMovies', unless explicitly specified otherwise.

What does the speaker mean by a 'filtered index' and why is it useful?

A filtered index is an index created on a column (like 'isDeleted') with a condition that only includes non-deleted entities. This helps optimize performance by ensuring that queries involving non-deleted data are faster, even as the size of the database grows.

How can the 'IgnoreQueryFilters' method be useful in the context of soft deletes?

The 'IgnoreQueryFilters' method can be used when you want to include soft-deleted records in a query. By using this method, the global query filter for soft deletes is bypassed, allowing you to retrieve records that would normally be excluded.

Why is the 'ExecuteUpdateAsync' method used in the soft delete process?

The 'ExecuteUpdateAsync' method is used to update the entities marked for deletion. It modifies the 'isDeleted' flag and sets the 'deletedAtUtc' timestamp, ensuring that data is not fully deleted from the database but is marked as deleted efficiently.

What is the difference between soft deletes and hard deletes in terms of data recovery?

Soft deletes allow for data recovery since the record is merely marked as deleted and can be restored later. Hard deletes, on the other hand, completely remove the data from the database, making recovery difficult or impossible.

Can the soft delete approach be used with NoSQL databases?

Yes, the concept of soft deletes can be applied to NoSQL databases as well, as the underlying idea is universal. Instead of deleting a record, it is marked as deleted, which can be tracked and excluded from queries.