Enterprise Data Access: Fully Automated Soft-Delete

In professional software development, permanent data deletion is often a non-starter. Auditing, compliance, and recovery mandates require that we soft-delete records, marking them as inactive rather than physically removing them.

The implementation, however, is a common source of bugs and security vulnerabilities. A manual approach requiring developers to remember a Where(x => x.RemovedAt == null) clause on every query is simply untenable.

Architectural Imperatives

A production-grade soft-delete system must satisfy two non-negotiable requirements automatically:

1. Read Consistency: Soft-deleted records must be filtered from every SELECT query in the application.
2. Association Filtering: The filter must propagate when querying entity associations. Loading a Post must automatically exclude any soft-deleted Comments from its collection.

We achieved this by centralizing the logic using Global Query Filters and engineering a composable filter architecture.

The Composable Filter Solution

A major limitation with many ORMs is the restriction to a single Global Query Filter per entity. Enterprise systems, however, require multiple cross-cutting concerns: soft-delete, multi-tenancy, and row-level security, each needing its own filter.

Our solution is a system that aggregates multiple filter expressions:

• Behavior Definition: An interface like IRemovable defines the RemovedAt property and uses a custom attribute to point to its static filtering method.
• Dynamic Composition: At runtime, a process scans the entity hierarchy for all filtering attributes. It collects all individual filter lambdas (e.g., x.RemovedAt == null, x.TenantId == @CurrentTenant) and combines them using the logical AND operator (Expression.AndAlso).
• Application: The single, resulting aggregated expression is applied as the entity's Global Query Filter, ensuring all required conditions are met automatically on every query.

This system guarantees that any entity implementing IRemovable is always filtered correctly, regardless of how or where it is queried.

Transparent Delete Interception

To ensure data integrity and full auditability, we must intercept the DELETE command.

We implement new RemoveAsync extension methods that check for the IRemovable interface. When present, instead of executing a physical DELETE, the DAL executes a secure UPDATE statement that sets the RemovedAt timestamp.

This not only performs the soft-delete but, by chaining into our existing data modification logic, also automatically updates the ModifiedAt timestamp, giving us a complete audit trail for the "deletion."

The output SQL is a confirmation of success: the hard delete is converted into an auditable update, and the global filter is even applied to the update query itself to prevent re-deleting an already soft-deleted record.

Conclusion and Next Steps

By moving soft-delete from an application concern to a core DAL feature, we have eliminated a significant source of developer error and enhanced system security and consistency. The composable filter system is the key takeaway, providing a pattern for managing multiple cross-cutting data concerns simultaneously.

This foundational architecture is now being leveraged to implement our next critical feature: multi-tenancy.

For the full source code and complete architectural deep dive, visit the original article on my blog.