In enterprise when applications are developed using Entity Framework it is always essential to have a generic code which can be used in entire application. For this purpose, different practices are adopted. One of the popular one is repository pattern Unit of Work. The generic repository is written which is responsible for all CRUD operations. This enhances the code reusability and ensures no further changes in the data layer logic.
Referring to the pattern, when it comes to the search functionality, the generic repository is ready to return the data for the specific predicate. Here, the main concern is generating predicates for individual filter. For instance, there are n number of grids each having some filter conditions. There will be multiple permutations combinations of the filters among each grid. For each of these combinations we can't hard-code the filter. Somehow we have to create the filters dynamically at run-time which involves creation of expression tree dynamically.
The generated expression tree so far involves all the filters you want to apply one by one. The LINQ to SQL in turn generates the SQL from it.
Let us have a look at the demonstration of the expression tree builder by considering following project architecture:
- Single Page Application in AngularJS with Web API 2
- Entity Framework
- LINQ
- Repository Pattern with Unit Of Work
From a presentation layer the filter conditions are passed to the web API. It can be passed as a separate JSON object in body or in a request header. The web API controller now converts it to an object adding some filtering conditions and properties to it.
public class SearchFilter
{
public string PropertyName { get; set; }
public object PropertyValue { get; set; }
private OperatorEnum op = OperatorEnum.Contains;
public OperatorEnum Operator
{
get
{
return op;
}
set
{
op = value;
}
}
}
public enum OperatorEnum
{
Contains,
Equals,
DateEquals,
IntegerEquals
}
Each filter conditions is converted into SearchFilter object.
Ex: We are applying the filter on FirstName for value John. The search object will be created as
SearchFilter searchFilter=new SearchFilter{
PropertyName=”FirstName”,
PropertyValue=”John”,
Op=OperatorEnum.Contains // First name is a string
}
Thus each filter condition is converted to the object of SearchFilter class. The list of SearchFilter object is then passed to the utility class “ExpressionTreeBuilder” which returns the expression against the SearchFilter objects.
As of now, filters list for only two filters have been taken into consideration in expression tree builder class. The scope can be extended by using foreach loop for each filter.
The Utility Class ExpressionTreeBuilder is as follows:
public static class ExpressionTreeBuilder
{
#region Declaration
private static MethodInfo integerEquals = typeof(int).GetMethod("Equals", new[] { typeof(int) });
private static MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
private static MethodInfo dateEqualsMethod = typeof(DateTime).GetMethod("Equals", new[] { typeof(DateTime) });
#endregion
#region Private Class
private class ParameterVisitor : ExpressionVisitor
{
public Expression Parameter
{
get;
private set;
}
protected override Expression VisitParameter(ParameterExpression node)
{
Parameter = node;
return node;
}
}
#endregion
#region Public Methods
public static Expression<Func<T, bool>> GetExpression<T>(List<SearchFilter> searchFilters)
{
Expression exp = null;
var parameter = Expression.Parameter(typeof(T), "Item");
if (searchFilters.Count > 1)
{
exp = GetExpression(parameter, searchFilters[0], searchFilters[1]);
}
else
{
exp = GetExpression(parameter, searchFilters[0]);
var lambdaExpression = exp as LambdaExpression;
return Expression.Lambda<Func<T, bool>>(lambdaExpression.Body, lambdaExpression.Parameters);
}
return Expression.Lambda<Func<T, bool>>(exp, parameter);
}
#endregion
#region Private Methods
private static Expression GetExpression(Expression parameter, SearchFilter searchFilter)
{
Expression resultExpression = null;
Expression childParameter, navigationPropertyPredicate;
Type childType = null;
string[] propertyArray = searchFilter.PropertyName.Split('.');
if (propertyArray.Count() > 1)
{
parameter = Expression.Property(parameter, propertyArray[0]);
var isCollection = typeof(IEnumerable).IsAssignableFrom(parameter.Type);
if (isCollection)
{
childType = parameter.Type.GetGenericArguments()[0];
childParameter = Expression.Parameter(childType, childType.Name);
}
else
{
childParameter = parameter;
}
searchFilter.PropertyName = string.Join(".", propertyArray.Skip(1).ToArray());
navigationPropertyPredicate = GetExpression(childParameter, searchFilter);
if (isCollection)
{
var anyMethodDef = typeof(Enumerable).GetMethods().Single(item => item.Name == "Any" && item.GetParameters().Length == 2);
anyMethodDef = anyMethodDef.MakeGenericMethod(childType);
navigationPropertyPredicate = Expression.Call(anyMethodDef, parameter, navigationPropertyPredicate);
resultExpression = MakeLambda(parameter, navigationPropertyPredicate);
}
else
{
resultExpression = navigationPropertyPredicate;
}
}
else
{
var childProperty = parameter.Type.GetProperty(propertyArray[0]);
var left = Expression.Property(parameter, childProperty);
navigationPropertyPredicate = GetChildExpression(left, searchFilter);
resultExpression = MakeLambda(parameter, navigationPropertyPredicate);
}
return resultExpression;
}
private static Expression MakeLambda(Expression parameter, Expression predicate)
{
var resultParameterVisitor = new ParameterVisitor();
resultParameterVisitor.Visit(parameter);
var resultParameter = resultParameterVisitor.Parameter;
return Expression.Lambda(predicate, (ParameterExpression)resultParameter);
}
private static Expression GetChildExpression(MemberExpression member, SearchFilter searchFilter)
{
ConstantExpression constant = null;
constant = Expression.Constant(searchFilter.PropertyValue);
switch (filter.Operator)
{
case OperatorEnum.Equals:
return Expression.Equal(member, constant);
case OperatorEnum.Contains:
return Expression.Call(member, containsMethod, constant);
case OperatorEnum.DateEquals:
{
constant = Expression.Constant(DateTime.Parse(searchFilter.PropertyValue.ToString()));
return Expression.Call(member, dateEqualsMethod, constant);
}
case OperatorEnum.IntegerEquals:
{
constant = Expression.Constant(Int32.Parse(searchFilter.PropertyValue.ToString()));
return Expression.Call(member, integerEquals, constant);
}
}
return null;
}
private static BinaryExpression GetExpression(ParameterExpression param, SearchFilter searchFilter1, SearchFilter searchFilterilter2)
{
Expression result1 = GetExpression(param, searchFilter1);
Expression result2 = GetExpression(param, searchFilter2);
var lambdaExp1 = result1 as LambdaExpression;
var lambdaExp2 = result2 as LambdaExpression;
return Expression.AndAlso(lambdaExp1.Body, lambdaExp2.Body);
}
#endregion
}
In ExpressionTreeBuilder class, reflections have been used for creating the lambda expressions. Reflections of methods which are necessary for comparing the values are created. In the above example, only three data types have been considered that are string, integer and DateTime. Reflections of “Contains” , “Equals” and “Equals” are created respectively. Thus, with the help of reflections, an expression is created for each filter that compares the required property in an entity with constant value which has been passed. The above code compares scalar properties along with the navigation properties. While comparing navigation properties it also creates the expression for the property of type ICollection <T> along with the property of type T.
The ExpressionTreeBuilder class returns lambda expression for the search filters. This expression is then passed to the generic repository which returns the list of entity satisfying the predicate.
With the search filter pagination is achieved by passing the page number and page size to the repository so that it fetches only those number of records from database which increase the performance.
Below is the demonstration of the same using the console application.
The ERD for the entities I am working with is as follows:
The problem statement is, I want to fetch those programs who are having users with FirstName(string) as “Doe” and Age(integer) greater than “25”. Program entity is not directly associated with the Users entity. Here, we will have to traverse through the following tables to fetch the users associated with program.
Program => ProgramRoom=>ProgramUsers=>UserRole=>Users
ProgramRoom is a navigation property of type Icollection<ProgramRoom> in program entity.
static void Main(string[] args)
{
UnitOfWork unitOfWork = new UnitOfWork();
List<SearchFiler> SearchFilersList = new List<SearchFiler>();
SearchFiler SearchFiler = new SearchFiler
{
PropertyName = "ProgramRoom.ProgramUser.UserRole.Users.Age",
Operator = OperatorEnum.IntegerEquals,
PropertyValue = "25"
};
SearchFiler SearchFiler2 = new SearchFiler
{
PropertyName = "FirstName",
Operator = OperatorEnum.Equals,
PropertyValue = "Doe"
};
SearchFilersList.Add(SearchFiler2);
var exp = ExpressionTreeBuilder.GetExpression<SearchPOC.Data.Program>(SearchFilersList);
var outputList = unitOfWork.ProgramRepository.GetBySearchFiler(item => item.IsActive == true, 1, 10, exp);
outputList.ResultList.ForEach(item => System.Console.WriteLine("{0}\n", item.ProgramName));
System.Console.ReadLine();
}
In this way, the generic repository returns the list of programs having users with first name “Doe” and age equal to “25”. The filters for DateTime data type can also be used. Using reflections the comparison can be achieved for any data type.
While creating the lambda expression , “Any” clause of LINQ is used instead of “Where” clause. “Any” returns boolean whereas “Where” returns IQuerable <T>. This optimizes the number of queries that are fired.
The limitation of this builder function is that it fails to compare the NULLABLE data type. Therefore no field in the database is NULLABLE. It is set to NOT NULLABLE with some default value.
Hence with this snippet, it is possible to apply search filter on any entity with this utility function which gives you the lambda expression for the filters.