Entity Framework IsDeleted or IsDisabled column

Question

I never want to delete rows from some of my tables, so I added a IsDeleted column.

The POCS's that represent these tables all implement IDeleteable interface.

When I do something like -

context.Users.Where(u => u.Firstname == "Dave")

I want to get back just the rows that don't have the IsDeleted bit set.

I don't want to add the && IsDeleted == false to Where

I also want this solution to work for Single, First, or just context.Users, etc

I'd like to have something like the solution to this question.

This project can help you: https://github.com/jcachat/EntityFramework.DynamicFilters and works better than accepted answer — trailmax, Aug 17 '15 at 13:37
A more fail-proof solution has been posted here: http://stackoverflow.com/questions/12698793/how-can-i-automatically-filter-out-soft-deleted-entities-with-entity-framework — Laoujin, Mar 16 '17 at 22:36

David L · Accepted Answer · 2015-08-17T15:28:24.523

In light of trailmax's comment below, yes, the original answer below does load all data into memory since it's exposing chained enumerables.

There are two issues that arise when trying to chain IQueryable calls together.

EF does not know how to translate the resulting .Invoke() call
If you try to maintain a naming convention like .Where(), you will end up picking the wrong extension since IDeletable only exposes a single column on which you can build a predicate.

The following relies on the LinqKit library to chain expressions and the syntax is no longer fluid, but it does avoid any immediate execution.

var users = context.Users.WhereIsNotDeleted(x => x.Id > 0).ToList();

public static class Extension
{
    public static IEnumerable<T> WhereIsNotDeleted<T>(this IQueryable<T> source,
        Expression<Func<T, bool>> predicate) where T : IDeletable
    {
        var query = source.AsExpandable().Where(x => !x.IsDeleted);
        return query.Where(predicate);
    }
}

Assuming that IDeletable guarantees IsDeleted exists, you can create an extension method that always performs this check for you.

public interface IDeletable
{
    bool IsDeleted { get; set; }
}

public static class Extension
{
    public static IEnumerable<T> WhereNotDeleted<T>(this IEnumerable<T> source, 
        Func<T, bool> predicate) where T : IDeletable
    {
        return source.Where(x => !x.IsDeleted).Where(predicate);
    }
}

In the following simple test, only two records are returned since the third has been soft-deleted.

void Main()
{
    var x = new List<Test>();

    x.Add(new Test{ Number = "one" });
    x.Add(new Test{ Number = "two" });
    x.Add(new Test{ Number = "three", IsDeleted = true });

    var y = x.WhereNotDeleted(a => a != null);
    y.Count().Dump();
}

public class Test : IDeletable
{
    public string Number { get; set; }
    public bool IsDeleted { get; set; }
}

In light of your comment, if you wanted it to work with EXISTING LINQ extension methods, you wouldn't actually be able to, since you'd run into call ambiguity.

If you wanted to call .Where() and have it translate to your condition ONLY for your objects that implement IDeletable, you would need to wrap each LINQ extension method.

public static class Extension
{
    public static IEnumerable<IDeletable> Where(this IEnumerable<IDeletable> source, 
        Func<IDeletable, bool> predicate)
    {
        return System.Linq.Enumerable.Where(source, (x => predicate(x) && !x.IsDeleted));
    }
}

In these extension methods you have to call the base LINQ methods via instance invocation instead of as an extension method, else you'll run into a stack overflow exception.

I know I didn't say it, but I'd like it to work for more than just a Where. Single, FirstOrDefault, etc — Bryan, Aug 12 '15 at 18:51
It can't. They're extension methods and can't be overridden, else you'd receive an ambiguous call error. — David L, Aug 12 '15 at 18:53
Every time I see `IEnumerable` in LINQ next to EF, I smell trouble. I think this might end up with doing filtering in memory rather than filtering in the database. And on a large set it might be very bad for performance. Though I have not tried/confirmed this, but this is something to be aware of. — trailmax, Aug 17 '15 at 13:41
Just run a small test and I can confirm that this way of filtering is very inefficient - EF does `Select * from tableName` and then does the filtering in memory. Which is fine for a tiny data-sets, but very bad on performance and memory consumption on larger sets. So I would not advise on using this way to filter in a production system. — trailmax, Aug 17 '15 at 13:51
@trailmax You are absolutely correct and I've updated my answer with an efficient alternative. Unfortunately, the original answer was created to solve the OP's request for easy reuse of `.Where()`, but obviously that causes far greater issues. — David L, Aug 17 '15 at 15:29

score 0 · Answer 2 · edited Aug 17 '15 at 13:27

0

You could create a view for each table that has that where clause acting as its filter. Then you can access that view from entity framework instead of the underlying table and not worry about it. Your users never know about the underlying table, just the view.

edited Aug 17 '15 at 13:27

David L

32,885
8
62
93

answered Aug 12 '15 at 18:45

Clay Sills

235
1
9

Entity Framework IsDeleted or IsDisabled column

2 Answers2