EF Core 6 - How to have multiple DbSets to prefilter same table-CodePudding

I am trying to find the best solution to this problem. I have a database table named Customer. This table has a boolean column named Status, which tells me if this Customer is active or not. I would like create a sort of “global query filter” to extract only the list of customers which are active, but I know that if I configure a queryfilter on the only DbSet that I currently have, I would ALWAYS filter only the active customers, and that is not good for me since sometimes i need to have also the inactive ones (for example in admin pages).

My idea is to create something like this but I don’t know how to configure it:

public class Customer {
   //…properties

   public bool Status {get; set;}
}

public DbSet<Customer> Customers {get; set;}
public DbSet<Customer> CustomersActive {get; set;}

Is this possible?

CodePudding user response：

Expose IQueryable<Customer> properties instead:

public DbSet<Customer> Customers {get; set;}
public IQueryable<Customer> CustomersActive => Customers.Where(c => c.Status);
public IQueryable<Customer> CustomersInactive => Customers.Where(c => !c.Status);

Btw., a better name for bool Status would be Active or IsActive.

Note that you can chain Where calls. This is equivalent to combining the conditions with &&.

var result = Customers
    .Where(c => c.Status)
    .Where(c => c.Name.StartsWith("A"));

is equivalent to

var result = Customers
    .Where(c => c.Status && c.Name.StartsWith("A"));

So, this approach does not limit your possibilities to query.