Building an Enterprise Data Access Layer: Automated Soft-Delete

Welcome to the fourth installment of our series on building a robust, enterprise-grade Data Access Layer (DAL) in C#. In our previous post, we established a powerful automated auditing system to track entity creation and modification times (CreatedAt/ModifiedAt). Now, we will tackle another critical enterprise requirement: soft-delete.

In many systems, physically deleting data (DELETE FROM ...) is unacceptable. Regulatory compliance, audit trails, and the simple need for data recovery demand that records are preserved, even when a user "deletes" them. The standard solution is soft-deletion, where a record is marked as inactive instead of being removed.

This post will detail how we implement a fully automated, transparent, and secure soft-delete mechanism. We will not only intercept delete operations but also ensure that soft-deleted records are automatically filtered out of all read operations, including through entity associations.

The complete code for this part of the series is available on our GitHub repository: https://github.com/ByteAether/EnterpriseDal/tree/part4.

The Soft-Delete Contract: The IRemovable Interface

Consistent with our architecture, we define entity behaviors through interfaces. For soft-delete, we introduce the IRemovable interface in the DAL.Base/EntityBehavior namespace.

namespace DAL.Base.EntityBehavior;

[EntityFilter<IRemovable>(nameof(Filter))]
public interface IRemovable : IEntity
{
	DateTime? RemovedAt { get; set; }

	private static IQueryable<T> Filter<T>(IQueryable<T> q, IDbCtx _)
		where T : IRemovable
		=> q.Where(x => x.RemovedAt == null);
}

It introduces a nullable RemovedAt property. When this property is NULL, the record is active. When it contains a timestamp, the record is considered deleted. Any entity that should support soft-deletion will implement this interface.

Our database schema has been updated accordingly, with a nullable removed_at DATETIME column added to all tables.

The Core Challenge: Global and Composable Query Filters

Implementing soft-delete presents two significant challenges that a naive approach cannot solve:

1. Ubiquitous Filtering: Soft-deleted records must be hidden from every query by default. Manually adding a Where(x => x.RemovedAt == null) clause to every data retrieval call is brittle, error-prone, and violates the core principle of our DAL, which is to handle cross-cutting concerns automatically.
2. Filtering Through Associations: This is a more subtle but critical problem. Imagine fetching a Post entity and then accessing its associated Comments collection. The DAL must ensure that any soft-deleted comments are automatically filtered from that collection. A simple WHERE clause on the initial Post query will not propagate to lazily or eagerly loaded associations.

The solution is to use Global Query Filters, a feature supported by ORMs like Linq2Db and Entity Framework Core. A global filter is a WHERE condition that the ORM automatically appends to every query for a specific entity type.

However, these ORMs typically allow only a single filter expression per entity. Our architectural vision requires multiple, independent behaviors (soft-delete, multi-tenancy , row-level security), each potentially contributing its own filter. An entity could implement IRemovable and ITenanted, requiring two separate filters to be applied simultaneously.

A Composable Architecture for Global Filters

To overcome this limitation, we have engineered a system to aggregate multiple filter expressions into a single, cohesive filter for each entity. This logic is encapsulated within the new DAL.Base/EntityFilter folder.

The system works in three main steps:

1. Declaration: We use a custom attribute, [EntityFilter<T>(nameof(MethodName))], to associate a filtering method with an interface or class. As seen in the IRemovable interface code above, we declare that its filter logic is located in a static method named Filter. This makes each behavioral interface self-contained.

2. Discovery & Aggregation: We created a helper that, upon database context initialization, scans all our DAL entities (classes implementing IEntity). For each entity, it traverses its entire inheritance tree including all implemented interfaces. It collects all lambda expressions from the filter methods declared via the EntityFilter attribute. These individual expressions are then combined into a single, larger lambda expression using Expression.AndAlso.

3. Application: The final, aggregated expression is then applied as a single global filter to the entity using Linq2Db's mapping schema. We trigger this entire process with a single call in our database context DbCtx.InitDataContext():

   ...
   MappingSchema.ApplyEntityFilters<DbCtx>();
   ...

This architecture is incredibly flexible. It allows us to define filtering logic alongside the relevant behavior (like IRemovable or, in the future, ITenanted), and the DAL automatically composes these rules at runtime.

Integrating Soft-Delete into the DAL

With the filter aggregation system in place, we can now integrate the soft-delete logic.

1. Automated Scaffolding (Interceptor.cs)

Our custom scaffolding interceptor is extended to automatically add the IRemovable interface to any generated entity partial class that has a corresponding RemovedAt column in the database. This ensures our C# entity model stays perfectly in sync with our database-first philosophy.

2. Overriding Delete Operations (CrudExtensions.cs)

Next, we introduce new RemoveAsync extension methods that replace direct calls to Linq2Db's DeleteAsync. These methods contain the core soft-delete logic.

public static Task<int> RemoveAsync<T>(
	this IQueryable<T> source,
	 CancellationToken cancellationToken = default
) where T : class
{
	// Check if the entity type T implements IRemovable
	if (typeof(IRemovable).IsAssignableFrom(typeof(T)))
	{
		// If so, build an UPDATE query instead of a DELETE
		return source
			.Set(x => ((IRemovable)x).RemovedAt, DateTime.UtcNow)
			.ModifyAsync(cancellationToken); // Reuse our existing ModifyAsync
	}

	// Otherwise, perform a hard delete
	return source.DeleteAsync(cancellationToken);
}

This implementation is transparent to the developer. When they call RemoveAsync on a query for an IRemovable entity, the DAL automatically translates it into an UPDATE statement that sets the RemovedAt timestamp. By cleverly chaining this into our existing ModifyAsync method, we also get ModifiedAt auditing for free.

Verification: Seeing It in Action

Let's validate our implementation. We can write a simple test in Program.cs to remove a user and inspect the generated SQL.

• Code in Program.cs:

  // Assuming 'ctx' is our DbCtx instance and 'u' is a User entity
  await ctx.GetTable<User>()
  	.Where(x => x.Id == u.Id)
  	.RemoveAsync();
  
  Console.WriteLine(ctx.LastQuery);

• Generated SQL Output:

  UPDATE
  	[user]
  SET
  	[removed_at] = '2025-10-10 10:30:31.029',
  	[modified_at] = '2025-10-10 10:30:31.029'
  WHERE
  	[user].[removed_at] IS NULL
  	AND [user].[id] = X'0199CDAC13F5AB8EF21DB4A49A36D2F5'

The output confirms our success. The DELETE operation was transparently converted into an UPDATE that sets both removed_at and modified_at. Crucially, notice the WHERE [user].[removed_at] IS NULL clause. Our global filter is automatically applied even to the UPDATE operation itself, ensuring we don't try to re-delete an already deleted record. Any subsequent SELECT query for this user will now automatically fail to find it, as it will not satisfy the global filter.

Conclusion and Next Steps

In this post, we have successfully implemented a comprehensive and automated soft-delete system. By building a composable global filter architecture, we have not only solved the immediate challenge but also laid a robust foundation for future DAL capabilities. Our DAL now automatically handles soft-deletes and ensures data integrity across all queries and associations, preventing common security and consistency issues.

The powerful filter system we built is now ready for its next major task: multi-tenancy. In the next article, we will leverage this exact architecture to automatically isolate data between different tenants, preventing one of the most critical types of data leakage in SaaS applications.

For a deeper dive into the implementation, please explore the source code on our GitHub repository: https://github.com/ByteAether/EnterpriseDal/tree/part4.