A generic filter method for Linq-EF queries

Question

I have various types of EF entities, all of them have a navigation property called "Employee". When generating reports the user will have the option to filter the report according to the different employee propertoes (Cost Center, gender, etc.).

Currently I am filtering each query individually, for example:

var courses = context.Courses
              .Where(c => c.Employee.CostCenterID == ccID
                     && c.Employee.Rank == rankID
                     ....
                    )
                    .ToList();

The real filter code is much more longer but this was just a hint. Anyway, is there a way to create a generic method to filter the result by the employee? all the entities I will supply to this filter method will have an Employee navigation property. I will simply supply a IQueryable<entity> or ObjectSet<entity> and then get a filtered IQueryable<entity> or ObjectSet<entity>.

How to do that?

score 3 · Answer 1 · answered Jan 29 '14 at 19:29

If you want to avoid implementing an interface:

public Expression<Func<TEntity, bool>> EmployeeFilterDelegateExp<TEntity>( 
    int costCenterId, 
    int rankId )
{
    var parm = Expression.Parameter( typeof( TEntity ), "entity" );
    var employeeProperty = Expression.Property( parm, "Employee" );

    return ( Expression<Func<TEntity, bool>> )Expression.Lambda(
        Expression.AndAlso(
            Expression.Equal( Expression.Property( employeeProperty, "CostCenterID" ), 
                Expression.Constant( costCenterId ) ),
            Expression.Equal( Expression.Property( employeeProperty, "Rank" ), 
                Expression.Constant( rankId ) ) ),
        parm );
}

Usage:

var courses = context.Courses
    .Where( EmployeeFilterDelegateExp<Course>( ccID, rankID ) )
    .ToList();

Nean Der Thal · Answer 2 · 2014-01-29T16:47:26.097

1

I just figured it out! since we can not add a constraint in generic methods to check for a certain property, I managed to do it using an interface:

public interface IFilterable
{
    Employee Employee
    {
        get;
        set;
    }
}

Then, I added partial classes to inherit the previous interface for different entities that has the Employee navigation property, for example:

public partial class Course: IFilterable
{
}

Then created the following generic method:

public static IQueryable<T> Filter<T>(this IQueryable<T> source, SearchCriteria sc) 
    where T : class, IFilterable
{
    var filtered = source.Where(e => e.Employee.CostCenterID == sc.CostCenterID 
        && e.Employee.Gender == sc.Gender);

     return filtered;
}

then simply I can use it like this on any class that inherits IFilterable:

var list = context.Courses.Filter(sc).ToList();

Note: the SearchCriteria is just a simple class that holds different employee properties.

Please if there is a better way of doing this, post it.

edited Jan 29 '14 at 16:47

answered Jan 29 '14 at 16:37

Nean Der Thal

3,189
3
22
40

I think you should post this code as question on http://codereview.stackexchange.com/ instead of answering your own question and asking for better solutions – Sergey Berezovskiy Jan 29 '14 at 16:45
1

@SergeyBerezovskiy At the time of asking the question I did not know the answer, I figured it out after posting the question. So I wasn't like giving a tutorial or something. You know those moments when the answer just pops up in your head... – Nean Der Thal Jan 29 '14 at 16:46
1

@SergeyBerezovskiy anyway, thanks. I also posted in Code Review. – Nean Der Thal Jan 29 '14 at 16:52
Would the interface property (`Employee`) work for LINQ-to-SQL or Entity Framework LINQ class if the property implementation is mapping to a different property? – Petrus Theron Feb 10 '17 at 17:02

A generic filter method for Linq-EF queries

2 Answers2