Entity framework add where clause to all queries

Question

I have a project that is using Entity framework and AspNet Identity.

One of the parameters of the IdentityUser is a deactivated field.

Within the app, if I wanted to get the users, I would do this:

var users = Context.Users //etc....

However, I do not want this query to return any deactivated users. I know I could do this

var users = Context.Users.Where(x => x.Deactivated != true);

However, I am reluctant to do this, as I am sure that in time someone will forget to add this where clause.

Is there a way for entity to do this automatically to all context queries? I have found this:

https://learn.microsoft.com/en-us/ef/core/querying/filters

But I do not have EF core and cannot upgrade to it.

I know I could make a wrapper function and call this, but I am trying to find a better solution...

Are you sure you want to do this for _all_ context queries? e.g. How would you re-activate a user? — RikRak, Oct 02 '18 at 12:41
Maybe https://www.agile-code.com/blog/entity-framework-code-first-applying-global-filters/ or https://stackoverflow.com/questions/12698793/how-can-i-automatically-filter-out-soft-deleted-entities-with-entity-framework?fireglass_rsn=true#fireglass_params|&tabid=af13892376ccfce3&application_server_address=fg-vb2.dmz.local&popup=true&is_right_side_popup=false&start_with_session_counter=1 — sagi, Oct 02 '18 at 12:43
You can also check here: https://stackoverflow.com/questions/40441204/multitenancy-with-dbcontext-and-tenantid-interceptors-filters-ef-code-first — JoanComasFdz, Mar 21 '22 at 14:16

score 5 · Accepted Answer · answered Oct 02 '18 at 12:51

I often use a combination of XXXStore members and XXX members where the XXXStore is the DBSet and the XXX is a query with AsNoTracking(). I then use the XXX members in queries responding to GETs and the XXXStore only when I want to update the database. E.g.

    public DbSet<User> UserStore { get; set; }

    public IQueryable<User> Users => UserStore.AsNoTracking();

This gives the advantage of not tracking entities that won't be updated, but with a DRY approach that means not adding the AsNoTracking() verbosely all over the place.

This approach can easily be combined with a where clause:

    public DbSet<User> UserStore { get; set; }

    public IQueryable<User> Users => UserStore.AsNoTracking().Where(u => !u.Deactivated);

Filters can also work, but an advantage to this approach is that it's easy to make an exception when you really do need to access a deactivated user, too.

score 1 · Answer 2 · edited Dec 19 '18 at 02:20

1

You can use Global Filters:

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");
    // Configure entity filters
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}

edited Dec 19 '18 at 02:20

Ishaan Javali

1,711
3
13
23

answered Dec 19 '18 at 01:48

Ralph

11
2

1

Where do you get _tenantId? – Marcel Dec 19 '18 at 02:22
2

just an example, it can be much simpler : modelBuilder.Entity().HasQueryFilter(p => !p.IsDeleted); assuming that isDeleted is a field – Ralph Dec 21 '18 at 07:51

Entity framework add where clause to all queries

2 Answers2