Recently I had to implement search functionality in an Entity Framework based application. After some research I realized that ideally I had two choices:
- Write a stored procedure to create dynamic SQL based on input parameters and apply my filter criteria over targeted tables and return back 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 but imagine a case where you have to apply your filter criteria on 10 different tables, the size of 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 longer run.
- Implement a dynamic LINQ which can provide a sort of framework to filter my entities based on filter criteria. The main advantages are, it provides native .Net debugging, which means easily supportable and scalable in the longer 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 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 other new column, you can still do it but that's something 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 criteria over a given entity type obviously without duplicating lamda expressions for mutilple 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 of 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 db design aspect of a filter module as well, which I personally feel makes it really scalable in the longer run.
Structure of 2 of the main Filter tables out of 6 other Filter tables (A detailed description of 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 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 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. Following is the complete implementation of generic filter module. I've broken 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; // To avoid possibility if access modifier error
var property = typeof(T).GetProperty(searchTerm.FilterColumn.FilterColumnName); // Assign filter column name
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:
if (filterExpression == null)
filterExpression = methodCallExpression;
else
filterExpression = Expression.And(filterExpression, methodCallExpression);
break;
case Entities.LogicalOperator.Or:
if (filterExpression == null)
filterExpression = methodCallExpression;
else
filterExpression = Expression.Or(filterExpression, methodCallExpression);
break;
default:
if (filterExpression == null)
filterExpression = methodCallExpression;
else
filterExpression = 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 into depth in every important bits in above generic filter module:
This generic filter function take 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 entity type and 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 filterExpression ready then we can use the following generic lambda expression (also called as predicate) to apply the filter on a given dataContainer (entity type ) using the ready filterExpression.
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 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 to go and add details of a new column FilterColumn 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 expression, 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 db 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 are 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.