Implement a Generic Filtering Module Using Dynamic LINQ to Provide a Search Functionality

Recently, I had to implement search functionality in an Entity Framework-based application. After some research, I realized that ideally I had two choices.

1. Write a stored procedure to create dynamic SQL based on input parameters and apply my filter criteria over targeted tables, and return the result. It's an old and traditional way to implement a search/filter module. Although it does provide faster response for every search request, imagine a case where you have to apply your filter criteria on 10 different tables; the size of the dynamic SQL will be quite large, which again will not be good in terms of performance. On the other hand, I feel it's really not an easy job to debug dynamic SQL and maintain it in the long run.
2. Implement a dynamic LINQ that can provide a sort of framework to filter my entities based on filter criteria. The main advantages are that it provides native .NET debugging, which means it is easily supportable and scalable in the long run, and it lets you apply your filter criteria over entities (in case you are using Entity Framework), which gives one main advantage, which I have highlighted below.

The first most important thing to do is to determine what we really need; suppose we have a product table and the user wants to search for a specific product name or product sub-category name; using lambda expressions, we can do it in the following way.

switch (FilterColumn)
{
    case "ProductName":
        IEnumerable<Product> productList = dbContext.Products
            .Where(product => product.ProductName.Contains(FilterCriteria))
            .Select(p => p);
        break;

    case "ProductCategoryName":
        IEnumerable<Product> productList = dbContext.Products
            .Where(product => product.ProductCategoryName.Contains(FilterCriteria))
            .Select(p => p);
        break;
}

Later, suppose you want to add search functionality to another table, such as Company, then what we can do is the following.

switch (FilterColumn)
{
    case "CompanyName":
        IEnumerable<Company> companyList = dbContext.Companies
            .Where(c => c.CompanyName.Contains(FilterCriteria))
            .Select(p => p);
        break;
}

Yes, I completely agree with you, it really does not make sense to create a switch case for every new column. You can still do it, but that's something that does not look good. What we are doing here is duplicating lots of code, which is definitely not recommended.

One of my seniors often recommended that I think more and code less. The implication is that we should try to develop logic where we can avoid writing a specific lambda expression for every column or table. It should be a sort of generic implementation where you pass an entity type, filter criteria & column name, and your generic filter implementation should be able to interpret it. Then it should be able to apply a filter criterion over a given entity type, obviously without duplicating lambda expressions for multiple filter conditions. I also had the additional requirement to provide an option to save filters in the database, which a user can use at a later point in time.

Let's see how we can actually develop something like this; I will start with my database design so that I can explain a database design aspect of a filter module as well, which I personally feel makes it really scalable in the long run.

Structure of 2 of the main Filter tables out of 6 other Filter tables (A detailed description of the other tables is given in the attached source code).

Filter Table

ColumnName	DataType
FilterId	Int
FilterName	Varchar(100)
UserId	Int

FilterRule Table

ColumnName	DataType
FilterRuleId	Int
FilterId	Int
FilterColumnId	Int
SearchTerm	Varchar(100)
RelationalOperatorId	Int
LogicalOperatorId	Int

RelationalOperatorId is from a RelationalOperator table, which has a list of relational operators such as StartsWith, EndsWith, Like, Equal, NotEqual, DateFrom, and DateTo (Any new relational operator can be added to the list).

LogicalOperatorId is from the LogicalOperator table, which has a list of logical operators such as AND & OR.

FilterColumnId is from the FilterColumn table, which has a list of column names on which a user will be creating a filter rule, such as ProductName, ProductCategoryName & CompanyName.

SearchTerm is the actual Text to be searched.

Let's look into dynamic LINQ implementation.

I have a GenericFilter function ApplyFilterRules, which accepts a generic entity type as input parameter, and it returns a filtered generic entity type. The following is the complete implementation of the generic filter module. I've broken the ice over this implementation just after this code block.

/// <summary>
/// Apply Filter Rules on given type of data container
/// </summary>
/// <typeparam name="T">generic type</typeparam>
/// <param name="filterId">filter id</param>
/// <param name="dataContainer">type of data container on which filter needs to be applied</param>
/// <returns></returns>
public static IEnumerable<T> ApplyFilterRules<T>(int filterId, IEnumerable<T> dataContainer) where T : class
{
    try
    {
        if (filterId != 0)
        {
            Expression filterExpression = null;
            MethodCallExpression methodCallExpression = null;
            BinaryExpression binaryExpression = null;

            using (var context = DocGenEntities.CreateContext())
            {
                var parameter = Expression.Parameter(typeof(T), "dataContainer");
                IEnumerable<Entities.FilterRule> filterRules = 
                    (from f in context.FilterRules where f.FilterId == filterId select f).ToList();

                if (filterRules.Count() > 0)
                {
                    foreach (Entities.FilterRule filterRule in filterRules)
                    {
                        Entities.FilterRule searchTerm = filterRule; // Avoid access modifier error
                        var property = typeof(T).GetProperty(searchTerm.FilterColumn.FilterColumnName);
                        var propertyType = property.PropertyType;
                        var propertyAccess = Expression.MakeMemberAccess(parameter, property);

                        switch (searchTerm.RelationalOperator.RelationalOperatorId)
                        {
                            case Entities.RelationalOperator.TextEqual:
                                binaryExpression = GetEqualBinaryExpression(propertyAccess, searchTerm.ColumnValue);
                                filterExpression = GetLogicalExpression(searchTerm.LogicalOperatorId, filterExpression, binaryExpression);
                                break;

                            case Entities.RelationalOperator.TextNotEqual:
                                binaryExpression = GetNotEqualBinaryExpression(propertyAccess, searchTerm.ColumnValue);
                                filterExpression = GetLogicalExpression(searchTerm.LogicalOperatorId, filterExpression, binaryExpression);
                                break;

                            case Entities.RelationalOperator.TextLike:
                                methodCallExpression = GetLikeExpression(propertyAccess, searchTerm.ColumnValue);
                                filterExpression = GetMethodCallExpression(searchTerm.LogicalOperatorId, filterExpression, methodCallExpression);
                                break;

                            case Entities.RelationalOperator.TextStartsWith:
                                methodCallExpression = GetStartsWithExpression(propertyAccess, searchTerm.ColumnValue);
                                filterExpression = GetMethodCallExpression(searchTerm.LogicalOperatorId, filterExpression, methodCallExpression);
                                break;

                            case Entities.RelationalOperator.TextEndsWith:
                                methodCallExpression = GetEndsWithExpression(propertyAccess, searchTerm.ColumnValue);
                                filterExpression = GetMethodCallExpression(searchTerm.LogicalOperatorId, filterExpression, methodCallExpression);
                                break;

                            case Entities.RelationalOperator.DateRangeFrom:
                                binaryExpression = GetDateGreaterThanOrEqualExp(propertyAccess, searchTerm.ColumnValue, propertyType);
                                filterExpression = GetLogicalExpression(searchTerm.LogicalOperatorId, filterExpression, binaryExpression);
                                break;

                            case Entities.RelationalOperator.DateRangeTo:
                                binaryExpression = GetDateLessThanOrEqualExp(propertyAccess, searchTerm.ColumnValue, propertyType);
                                filterExpression = GetLogicalExpression(searchTerm.LogicalOperatorId, filterExpression, binaryExpression);
                                break;
                        }
                    }
                }

                if (filterExpression != null)
                {
                    Expression<Func<T, bool>> predicate = Expression.Lambda<Func<T, bool>>(filterExpression, parameter);
                    Func<T, bool> compiled = predicate.Compile();
                    return dataContainer.Where(compiled).ToList();
                }
            }
        }

        return dataContainer.ToList();
    }
    catch (Exception exception)
    {
        throw;
    }
}

/// <summary>
/// Get Equal Binary Expression for Equal relational operator
/// </summary>
/// <param name="propertyAccess"></param>
/// <param name="columnValue"></param>
/// <returns></returns>
static BinaryExpression GetEqualBinaryExpression(MemberExpression propertyAccess, string columnValue)
{
    return Expression.Equal(GetLowerCasePropertyAccess(propertyAccess), Expression.Constant(columnValue.ToLower()));
}

/// <summary>
/// Get Lower Case Property Access
/// </summary>
/// <param name="propertyAccess"></param>
/// <returns></returns>
static MethodCallExpression GetLowerCasePropertyAccess(MemberExpression propertyAccess)
{
    return Expression.Call(
        Expression.Call(propertyAccess, "ToString", new Type[0]),
        typeof(string).GetMethod("ToLower", new Type[0])
    );
}

/// <summary>
/// Get Method Call Expression for Like/Contains relational operator
/// </summary>
/// <param name="propertyAccess"></param>
/// <param name="columnValue"></param>
/// <returns></returns>
static MethodCallExpression GetLikeExpression(MemberExpression propertyAccess, string columnValue)
{
    MethodCallExpression methodCallExpression = Expression.Call(
        GetLowerCasePropertyAccess(propertyAccess),
        ContainsMethod,
        Expression.Constant(columnValue.ToLower())
    );
    return methodCallExpression;
}

/// <summary>
/// Get Method Call Expression
/// </summary>
/// <param name="logicalOperatorId"></param>
/// <param name="filterExpression"></param>
/// <param name="methodCallExpression"></param>
/// <returns></returns>
static Expression GetMethodCallExpression(int logicalOperatorId, Expression filterExpression, MethodCallExpression methodCallExpression)
{
    switch (logicalOperatorId)
    {
        case Entities.LogicalOperator.And:
            filterExpression = filterExpression == null
                ? methodCallExpression
                : Expression.And(filterExpression, methodCallExpression);
            break;

        case Entities.LogicalOperator.Or:
            filterExpression = filterExpression == null
                ? methodCallExpression
                : Expression.Or(filterExpression, methodCallExpression);
            break;

        default:
            filterExpression = filterExpression == null
                ? methodCallExpression
                : Expression.And(filterExpression, methodCallExpression);
            break;
    }

    return filterExpression;
}

/// <summary>
/// Get Logical Expression
/// </summary>
/// <param name="logicalOperatorId"></param>
/// <param name="filterExpression"></param>
/// <param name="binaryExpression"></param>
/// <returns></returns>
static Expression GetLogicalExpression(int logicalOperatorId, Expression filterExpression, BinaryExpression binaryExpression)
{
    switch (logicalOperatorId)
    {
        case Entities.LogicalOperator.And:
            filterExpression = filterExpression == null
                ? binaryExpression
                : Expression.And(filterExpression, binaryExpression);
            break;

        case Entities.LogicalOperator.Or:
            filterExpression = filterExpression == null
                ? binaryExpression
                : Expression.Or(filterExpression, binaryExpression);
            break;

        default:
            filterExpression = filterExpression == null
                ? binaryExpression
                : Expression.And(filterExpression, binaryExpression);
            break;
    }

    return filterExpression;
}

Region Readonly Fields

private static readonly MethodInfo ContainsMethod = 
    typeof(string).GetMethod("Contains", new Type[] { typeof(string) });

private static readonly MethodInfo StartsWithMethod = 
    typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });

private static readonly MethodInfo EndsWithMethod = 
    typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });

Endregion

Let's look in depth at every important bit in the above generic filter module.

This generic filter function takes filterId (the id of a selected filter by the user, which he might have created in the past) and datacontainer (entity on which you want to apply the filter criteria) as parameters.

Later, I am creating a parameter, property, propertyType & propertyAccess using the entity type and the column name on which we need to apply the filter:

var parameter = Expression.Parameter(typeof(T), "dataContainer");

var property = typeof(T).GetProperty(searchTerm.FilterColumn.FilterColumnName); // Assign filter column name

var propertyType = property.PropertyType;

var propertyAccess = Expression.MakeMemberAccess(parameter, property);

Once we have these things, we can go and check the relational operator (ex, Text Like or Text Equal) selected by a user to filter the data. We then create LINQ MethodCallExpression & BinaryExpression depending upon the selected relational operator. Now, no matter which table or which you want to filter, the following will create an appropriate LINQ expression which we will use to filter the data.

Expression filterExpression = null;

switch (searchTerm.RelationalOperator.RelationalOperatorId)
{
    case Entities.RelationalOperator.TextEqual:
        binaryExpression = GetEqualBinaryExpression(propertyAccess, searchTerm.ColumnValue);
        filterExpression = GetLogicalExpression(searchTerm.LogicalOperatorId, filterExpression, binaryExpression);
        break;

    case Entities.RelationalOperator.TextLike:
        methodCallExpression = GetLikeExpression(propertyAccess, searchTerm.ColumnValue);
        filterExpression = GetMethodCallExpression(searchTerm.LogicalOperatorId, filterExpression, methodCallExpression);
        break;
}

Once we have our filter expression ready, then we can use the following generic lambda expression (also called a predicate) to apply the filter on a given data container (entity type ) using the ready filter expression.

if (filterExpression != null)
{
    Expression<Func<T, bool>> predicate = Expression.Lambda<Func<T, bool>>(filterExpression, parameter);
    Func<T, bool> compiled = predicate.Compile();
    return dataContainer.Where(compiled).ToList();
}

Conclusion

LINQ expressions and generic lambda expressions really make our life easier; otherwise, we might have written n lines of code just to achieve the same thing, which we could do in a more compact way. It lets you develop a generic filter module, which is quite scalable in terms of adding filtering to new entities. All you may have to do is go and add details of a new column, FilterColumn, to the table, and you are then all set. You can easily add any additional relational operator conditions, such as initially I had only Text Like & Text Equal expressions, later I just added two more expressions for StartWith & EndsWith, and then I am all set to use them in a filtering module. I could not include a complete schema of a database structure & a working source code due to missing reference entities (this is the reason why we see some of the variables highlighted with red color), which ideally I cannot share, being a real-time project.

Though soon I will be developing an independent demo project, which will clear out the remaining fog in this implementation, if there is any. Meanwhile, if anyone needs me to explain any grey area in this, then please post your comment or email me. I will try to provide any required details of a specific section.