How to set filter option on DbSet property in Entity Framework?

Question

I have a DbSet property in my context class as follows:

public class ProjectContext: DbContext
{
    public ProjectContext(): base("name=DBCS")
    {
    }

    public DbSet<Employee> EmployeeDbSet { get; set; }
}

This property is being used in many places of my project. Now I need to set a generic condition on Employee such as I need only active employees. I can get active employees by simply filtering my linq queries like this:

var employees = context.EmployeeDbSet.where(e => e.IsActive).ToList();

Since I've already written many queries in many places of my project, now I have to rewrite them all which is very difficult, time-consuming and error prone.

As this is a generic condition and I want to set this on all queries, I'm looking for a way to set the condition on EmployeeDbSet property instead.

Can it be accomplished?

There are loads of answers on SO regarding generic EF methods: See [here](https://stackoverflow.com/questions/25508595/generic-linq-to-entities-filter-method-that-accepts-filter-criteria-and-properti) for example. — IronAces, Sep 06 '17 at 12:10

Alper Tokcan · Accepted Answer · 2017-09-07T07:19:32.633

3

You can add a IQueryable<Employee> property to your DbContext that returns the active where condition set.

Example;

public IQueryable<Employee> ActiveEmployees 
{
    get
    {
        return EmployeeDbSet.Where(e => e.IsActive);
    }
}

This way you can combine it with other where conditions by just doing;

context.ActiveEmployees.Where(x=> x.name == "john");

After you do this you cant use methods that declared directly in DbSet<T>. To use find method after you use where, You can add a new find method yourself as follows;

public static class DbModelExtensions
{
    public static Employee Find(this IQueryable<Employee> query, int id)
    {
        return query.Where(x => x.Id == id).FirstOrDefault();
    }
}

This adds a extension method Find to only IQueryable<Employee> if its namespace is referenced in current file.

Then you can use it like this;

context.ActiveEmployees.Find(15);

edited Sep 07 '17 at 07:19

answered Sep 06 '17 at 12:37

Alper Tokcan

306
2
7

I've already used my existing `EmployeeDbSet` in many queries and joining. According to your suggestion, I've renamed `EmployeeDbSet` to `Employees` and added `IQueryable EmployeeDbSet`. But I'm facing a small problem that `IQuerable` is not supporting `Find` method. If I replace `Find` with `First`, everything works as expected. But I'm afraid if I can use all necessary extension method on `IQuerable` that DbSet offers. Can you please explain me? – Chanchal Zoarder Sep 07 '17 at 05:05
When using IQueryable you can use all methods given by linq.But Find is a special method declared directly on DbSet class itself. So you cant use it after you use where.But you can add a find method yourself to IQueryable.I've updated my answer to include that. – Alper Tokcan Sep 07 '17 at 07:19
Thank you very much for spending your valuable time. It helped me a lot. – Chanchal Zoarder Sep 07 '17 at 10:15

How to set filter option on DbSet property in Entity Framework?

1 Answers1