C#•2y ago

✅ Soft Deletions with EF Core

Hey, instead of deleting the entry from the DB directly I am setting a IsDeleted property to true. Generic saving etc. works already. Now to my problem: I don't want to call .Where(x => !x.IsDeleted) every time I query an entity as it's very error prone. Is there any good design pattern you'd recommend to add this to (almost) every call? What I've tried so far are global query filters. The issue with that is that several entities depend on the entity that has this global query filter. If I remove the global query filter it works fine but then I do remove my tenant logic. So global query filters are not an option.

52 Replies

BananaPie•2y ago

maybe put that filter in an expression? https://stackoverflow.com/questions/54512134/entity-framework-core-use-extension-methods-inside-queryable

Stack Overflow

Entity Framework Core - Use Extension Methods Inside Queryable

I have this following query: db.Users.AsQueryable() .Where(u => u.Id = userResolver.LoggedUserId() && u.Packages.Where(p => p.StatusId == (int)PackageStatus.InProgre...

BananaPie•2y ago

not an expert, maybe someone know a better way

DustyOP•2y ago

Thought about this too, even making it an extension method but there has to be a better way.. :/ The saving part works as described above. I'm looking for a good pattern to retrieve the filtered data without adding the where clause every time a write a query I know I tried that already But I have another global query filter which i dont want to ignore

syao•2y ago

blogs = db.Blogs
    .Include(b => b.Posts)
    .IgnoreQueryFilters()
    .ToList();

blogs = db.Blogs
    .Include(b => b.Posts)
    .IgnoreQueryFilters()
    .ToList();

you can ignore the queryfilter

DustyOP•2y ago

Idk if I misunderstand you both or you don't read my question. I am very grateful for help but please do read. IgnoreQueryFilters() ignores ALL filters. So this isn't an option

syao•2y ago

oh you're right, I answered something that had nothing to do with your question, my bad

amio•2y ago

What about "decorating"/wrapping your DbContext? I don't know if it's a typical way to do it, but it would be fairly minimal plumbing. So when you normally have your DbContext with Blogs you could have your "decorator" expose a property like public whatever Blobs => _actualDbContext.Blogs.Where(not deleted).

PontiacGTX•2y ago

You can do a generic repository make all entities inherit from Interface and all entities will have IsDeleted then in a GetAll() implicitly add the contraint in the generic where T :IEntity and to each query you would add where(ExpressionFromParameter).Where(x=> !x.IsDeleted) If you don't want a generic repo make it a Predefined expresión and add Expression<Func<IEntity,bool>>param=(e)=>!e.IsDeleted There is also an alternative but it overrides the entire db context behavior maybe differentiate it by the Interface idk maybe add an interface to the generic contraint so you can differentiatw between IEntity<TDelete> where TDelete is a contraint for interface I dunno

amio•2y ago

EF is already a repo and you don't need to (arguably shouldn't) put much more typical repo stuff around it

PontiacGTX•2y ago

I mean I am doing it in a more easy way to work all the time without writing the query

amio•2y ago

Yes, that's what EF does on its own

DustyOP•2y ago

No worries thanks for your thoughts anyway 🙂 That was my idea aswell, just not too sure if that's the way to do it. But honestly I do not seem to find any other approach/design pattern Thought about this aswell but amio is right about repos, atleast for me it's bad practice. Thanks for ideas though 🙂

Jayy•2y ago

There's not really lol

DustyOP•2y ago

public static class QueryableExtensions
{
    public static IQueryable<TEntity> ApplyFilters<TEntity>(this IQueryable<TEntity> query)
        where TEntity : class
    {
        // This and other filters could be cached via a dictionary
        if (typeof(TEntity).IsAssignableTo(typeof(ISoftDeleteableEntity)))
        {
            query = query.Where(x => !((ISoftDeleteableEntity)x).IsDeleted);
        }

        return query;
    }
}

public static class QueryableExtensions
{
    public static IQueryable<TEntity> ApplyFilters<TEntity>(this IQueryable<TEntity> query)
        where TEntity : class
    {
        // This and other filters could be cached via a dictionary
        if (typeof(TEntity).IsAssignableTo(typeof(ISoftDeleteableEntity)))
        {
            query = query.Where(x => !((ISoftDeleteableEntity)x).IsDeleted);
        }

        return query;
    }
}

So that would be my implementation now

Jayy•2y ago

Why would u do it that way

DustyOP•2y ago

Then I could call

var filteredItems = dbContext.Blogs.ApplyFilters();

var filteredItems = dbContext.Blogs.ApplyFilters();

what would be your suggestion?

Jayy•2y ago

Actually idk if u can pattern match on TEntity like I'm thinking off the top of my head One second

DustyOP•2y ago

Nope you can't :/ You need an instance sadly

Jayy•2y ago

U have an instance

DustyOP•2y ago

You mean inside of the Where?

Jayy•2y ago

It's just wether or not it's covariant/contra? here and i can't remember the syntax one second I don't think it is

DustyOP•2y ago

Alr

Jayy•2y ago

Ohh duh I'm dumb lol, just constrain it

DustyOP•2y ago

I will probably gonna have more filters in there So i cannot really constrain it

Jayy•2y ago

Have i ever used this language at all lol 🤣 Wut Ur already checking the interface at runtime Just do it at compile time

DustyOP•2y ago

Yea How? If i do a where t : ISoftDeleteable i cannot use this method for other filters And i dont wanna call like 3 filter methods

Jayy•2y ago

U can't use it for other filter methods right now either lol Unless u want to just call it absent mindedly on all entities? So you gain nothing and lose type safety here

DustyOP•2y ago

So you would just do this:

public static IQueryable<TEntity> ApplyDeletionFilter<TEntity>(this IQueryable<TEntity> query)
        where TEntity : ISoftDeleteableEntity
    {
        return query.Where(x => !x.IsDeleted);
    }

public static IQueryable<TEntity> ApplyDeletionFilter<TEntity>(this IQueryable<TEntity> query)
        where TEntity : ISoftDeleteableEntity
    {
        return query.Where(x => !x.IsDeleted);
    }

What to do with the other filters?

    public static IQueryable<TEntity> OtherFilter1<TEntity>(this IQueryable<TEntity> query)
        where TEntity : IOtherEntity
    {
        // Impl
    }

    public static IQueryable<TEntity> OtherFilter1<TEntity>(this IQueryable<TEntity> query)
        where TEntity : IOtherEntity
    {
        // Impl
    }

With this approach I would need for each filter a separate call

var items = dbContext.Blogs
  .ApplyDeletionFilter()
  .OtherFilter1();
  // etc.

var items = dbContext.Blogs
  .ApplyDeletionFilter()
  .OtherFilter1();
  // etc.

I do agree but with caching and such it shouldn't be a big performance concern here in this context. The most time consuming part is the roundtrip

Jayy•2y ago

Why would u need a seperate call? I mean u could do it that way But mainly this entire thing is super weird design I'd argue u should have it as separate calls tbh But how many interfaces are ur models gonna implement lol

DustyOP•2y ago

It's basically checking some date fields for some entities. Currently I do have those 2 .Where statements in a few places where I fetch data And this is error prone So you'd make an extension method for every filter? Instead of the Where statements?

Jayy•2y ago

Well i wouldn't use filters or extensions at all here tbh I'd just rewrite the where everytime personally Simple, type safe, easy

DustyOP•2y ago

Ugh okay

Jayy•2y ago

But if you want to play with interfaces and stuff go for it, ya I'd have a seperate method call for every filter

DustyOP•2y ago

Sure tell me

Jayy•2y ago

If i had a filter that grouped stuff I'd make another overload If ur gonna do this you play in the type system or you don't, and if you don't then that's risky Idk why you'd take that risk for something like this i guess Trying to be clever? Overload per filter is probably a decent sweet spot i suppose, if we had DUs you could do some more shenanigans but the returns are diminishing

DustyOP•2y ago

The only issue here is trying to remove the Where statements. Once you implement a new part of the application and forget to add these filter statements and fetch data, you get incorrect results and the worst case would be if you'd notice it. Could you explain a little further? Btw I totally agree with you but at the same time would like a better design

Jayy•2y ago

I mean, how does any of ur extension methods help with the "forget to call the extension method" problem

DustyOP•2y ago

Atleast a little bit as it's only 1 statement instead of 3 The perfect solution would be 0 statements at all but that's not possible

    public IQueryable<Post> Posts => Query<Post>();

    public IQueryable<T> Query<T>() where T : class
    {
        var items = Set<T>().AsQueryable();

        items = items.ApplyFilters();

        return items;
    }

    public IQueryable<Post> Posts => Query<Post>();

    public IQueryable<T> Query<T>() where T : class
    {
        var items = Set<T>().AsQueryable();

        items = items.ApplyFilters();

        return items;
    }

That would be the only solution that would work with 0 statements/filter calls from outside code but that seems really dodgy imo. And u have the runtime checks here inside of ApplyFilters again :/

MODiX•2y ago

Jayy#6249

sharplab.io (click here)

var a = new A();
baz(a);
bax(new B());
void bax<T>(T t) where T: IBar {Console.WriteLine("hello")...
void baz<T>(T t) where T: IFoo, IBar{Console.WriteLine("hi...
interface IFoo { public int i{get;set;}}
interface IBar { public int j{get;set;}}
public class A : IFoo, IBar{public int i {get;set;} public...
public class B : IBar{ public int j {get;set;}}

var a = new A();
baz(a);
bax(new B());
void bax<T>(T t) where T: IBar {Console.WriteLine("hello")...
void baz<T>(T t) where T: IFoo, IBar{Console.WriteLine("hi...
interface IFoo { public int i{get;set;}}
interface IBar { public int j{get;set;}}
public class A : IFoo, IBar{public int i {get;set;} public...
public class B : IBar{ public int j {get;set;}}

React with ❌ to remove this embed.

Jayy•2y ago

On my phone, format it how u wish

DustyOP•2y ago

Ah I get you Thanks a lot

Jayy•2y ago

Can't find the overload resolution rules spec on my phone, but basically it will resolve the MOST SPECIFIC method overload Do with that information what u will

DustyOP•2y ago

Yea I got it already 🙂 Thanks a lot, you really helped us out here. No runtime checks now

PontiacGTX•2y ago

why would you use class instead inheriting IEntity on each entity it would work for most entities and not something someoen randomly placed on a IQueryable

Tvde1•2y ago

is it solved?

DustyOP•2y ago

Could you explain? What is IEntity? technically yes but I'd like to hear the idea above

Tvde1•2y ago

I have achieved soft delete beautifully with the query filters I have a IEntity and a interface ISoftDeleteEntity : IEntity and through reflection I set up the query filter

DustyOP•2y ago

Is soft deleteion your only query filter or do you also have multiple?

Tvde1•2y ago

it's my only

DustyOP•2y ago

Ah that's my issue

Tvde1•2y ago

I have a separate query filter for each entity

Accord•2y ago

Dusty#0001

User Information ID: 188260101868617728 Profile: <@188260101868617728> Handle: Dusty#0001 Nickname: Dusty (ping on reply) Created: <t:1464955105:f> Joined: <t:1591625256:f> First tracked: <t:1629743527:f> Guild Participation Rank: 1306 Points: 970 Percentile: 45 Message Participation Last 30 days: 106 messages Most active text channel: <#1074096292961206383> (48 messages) Voice Participation No time spent in voice channels

Gaming

Programming

✅ Soft Deletions with EF Core