Imam Ramadhan
Back to writing
•
9 min read

Why My Soft-Delete Filter Moved into EF Core

What I learned when the rule that hides deleted tasks stopped living in every query and started living in the EF Core model, and why one query still had to opt out.

  • Architecture
  • Databases

When I added soft delete to the task feature, deleting a task stopped removing its row. Instead, the service marks the task and moves on:

task.IsDeleted = true;
task.DeletedAt = DateTime.UtcNow;
await _dbContext.SaveChangesAsync(cancellationToken);

The row is still in the database. From the database’s point of view, nothing was deleted. The only thing that changed is a flag. That means “deleted” in this API is not a fact about storage. It is a rule about visibility, and rules about visibility have to be applied somewhere.

The Rule Every Query Had to Remember

My first version applied that rule in each query. I wrote a small extension so I would not repeat the predicate by hand:

public static IQueryable<TaskItem> WhereActive(this IQueryable<TaskItem> query)
{
    return query.Where(task => !task.IsDeleted);
}

Then every task query called it:

var tasks = await _dbContext.Tasks
    .WhereActive()
    .Where(task => task.UserId == userId)
    .ToListAsync(cancellationToken);

At first this felt tidy. The condition had a name, WhereActive, and the name explained what it meant. But there was a detail I did not notice until later. The rule was not defined once. It was called many times. The definition lived in one file, but the obligation to apply it lived in every query.

The user service made this visible. It needed “users with no active tasks”, and it did not use the helper at all. It wrote the condition itself:

.Where(user => !_dbContext.Tasks.Any(task =>
    task.UserId == user.Id &&
    !task.IsDeleted))

So the same rule, !IsDeleted, now existed in two forms in two files. One was the helper, the other was a hand-written copy inside a subquery. They agreed with each other at that moment, but nothing forced them to keep agreeing.

Why Repetition Was a Correctness Problem

It would be easy to frame this as “writing .Where() everywhere is annoying”. That is not the real issue. The real issue is that a correctness rule depended on being remembered.

If a task is soft-deleted, it should be invisible to normal reads. That is the whole point of soft delete in this project. When the rule lives in each query, every new query is a chance to forget it:

list query        -> filter present
get by id query   -> filter present
new query later   -> filter forgotten

A query that forgets the filter still compiles. It still runs. It returns rows. It just returns one extra row that should not be there, and nothing in the code points at the mistake. That is a structural risk rather than a dramatic failure, and I want to describe it as what it is: the code allowed a forgotten filter, and I could see that the rule was already duplicated in two places.

I did not observe a deleted task leaking to a user. There was no incident and no report. I found this while reviewing how task visibility was enforced, and the duplication was the evidence that the rule was fragile. The fix is worth writing about because of the shape of the problem, not because something broke.

Moving Visibility into the Model

EF Core lets you attach a predicate to an entity in the model itself. I added it in AppDbContext:

modelBuilder.Entity<TaskItem>()
    .HasQueryFilter(task => !task.IsDeleted);

After that, the predicate is no longer something a query chooses to apply. It is part of how TaskItem is normally queried. A query that looks like this:

var tasks = await _dbContext.Tasks
    .Where(task => task.UserId == userId)
    .ToListAsync(cancellationToken);

now excludes soft-deleted tasks without saying so. I removed the WhereActive calls from the task service and deleted the extension file entirely, because the rule no longer needed to be invoked. The hand-written condition in the user service also disappeared, because the subquery over Tasks is subject to the same filter.

The mental model changed with the code. It used to be “remember to exclude deleted tasks”. Now it is closer to “deleted tasks are hidden by default”. The row is still there, and the flag is still there, but the default behavior of querying the entity is to leave those rows out.

I added a matching filter for the join entity as well, so category links that point at a soft-deleted task are excluded consistently:

modelBuilder.Entity<TaskCategory>()
    .HasQueryFilter(taskCategory => !taskCategory.TaskItem.IsDeleted);

Global query filters can interact with relationships in ways that deserve their own discussion, and I am deliberately not going deep on that here. The point for this article is narrower: the visibility rule moved from the queries into the model.

When I Needed the Deleted Rows Back

Centralizing the rule introduced a new responsibility. If deleted tasks are hidden by default, then any operation that cares about the physical rows has to say so explicitly, because the default will not show them.

There is exactly one place in this project where that matters: deleting a user. The rule there is that a user cannot be deleted while they still own tasks. That check has to consider all of a user’s tasks, including the soft-deleted ones, because those rows still exist and still reference the user. If the check used the normal filter, it would only see the active tasks and would happily let a user with only deleted tasks through, only for the database to reject the delete because the rows are still there.

So that one query opts out:

var hasTasks = await _dbContext.Tasks
    .IgnoreQueryFilters()
    .AnyAsync(task => task.UserId == id, cancellationToken);

IgnoreQueryFilters() turns off the default for that query, so it evaluates the physical data instead of the normal visibility. The lesson I take from this is not that global filters mean I never have to think about deleted rows again. It is the opposite. The filter defines the default, and an operation that needs the deleted rows has to ask for them on purpose. The fact that there is exactly one such call site, and that it is documented with a comment, is a sign the boundary is clear rather than hidden.

What the Tests Protect

There is a test file dedicated to this behavior, and its three tests cover the default, the escape hatch, and the subtle case.

The first test inserts one active task and one deleted task, then queries the context with no filter at all:

var tasks = await context.Tasks.ToListAsync();

var task = Assert.Single(tasks);
Assert.Equal("Active", task.Title);

This is the test that pins the new behavior. If someone removed the HasQueryFilter line, this test would fail, because both tasks would come back. The comment in the test says the missing .Where is intentional, so a future reader can see that the filtering is supposed to come from the model.

The second test uses the same data but calls .IgnoreQueryFilters() and asserts that both rows come back. That is what protects the escape hatch: it proves the filter can be turned off when a caller genuinely needs the deleted rows.

The third test is the one that surprised me. It builds a subquery over Tasks and checks that the filter still applies inside it:

var usersWithoutActiveTasks = await context.Users
    .Where(user => !context.Tasks.Any(task => task.UserId == user.Id))
    .Select(user => user.Id)
    .ToListAsync();

The user with only a deleted task is expected to appear in this result. It confirms that the filter is not limited to the top-level query, which is exactly the behavior the old hand-written condition in the user service used to provide by hand.

Alongside these, the user-deletion tests check the integrity side. One of them creates a user whose only task is soft-deleted and asserts that deleting the user still fails, which is what proves the IgnoreQueryFilters() call is doing its job.

The Trade-off

I do not think global query filters are simply better. They trade one problem for another.

With a manual filter, the behavior is visible where the query is written. A reader sees .Where(task => !task.IsDeleted) and knows exactly what the query does. With a global filter, a query like _dbContext.Tasks.ToListAsync() reads as “all tasks” even though EF Core quietly adds a predicate that is not in the code. The behavior is still correct, but it is no longer local. A reader who does not know the filter exists can misread the query.

What I gained is that the rule cannot be forgotten, and it is defined once. What I gave up is some of the directness of reading a query in isolation. For a rule like “deleted rows are invisible by default”, which has to hold everywhere and is easy to omit, the central version was worth it in this project. For a rule that only matters in one or two places, keeping it visible in those queries would probably be clearer.

What I Learned

The important change was not deleting a few .Where() calls. It was moving a repeated visibility rule out of individual queries and into the model that defines how the entity is normally queried. Once the rule lives there, the default is correct everywhere, and the only thing left to get right is knowing when to opt out.

That second part is the part I had not expected. Centralizing the rule did not remove the need to think about deleted rows. It changed where that thinking happens: not in every query, but in the rare operation that genuinely needs to see the physical data. IgnoreQueryFilters() is not an escape from the concept. It is the place where the exception to the default is stated, on purpose, in one line.

References