We had some support questions recently where our customers had the need to combine multiple smaller predicate expressions with either an OR or an AND (these will be the || and && operators if you are using C#) logical operator. And because the code from the answer that we sent to these customers is very interesting and can easily be refactored into something reusable we decided to write this blog post.
The key thing that one must know is that if you want your predicate to be translated by OpenAccess ORM to SQL and executed on the server you must have a LINQ Expression that is not compiled.
So, let’s say that you have these smaller predicate expressions:
Expression<Func<Customer, bool>> filter1 = c => c.City.StartsWith("S");
Expression<Func<Customer, bool>> filter2 = c => c.City.StartsWith("M");
Expression<Func<Customer, bool>> filter3 = c => c.ContactTitle == "Owner";
And you want to combine them with OR and AND (in that order).
In order to do this we write the following generic method that we can reuse for all our business classes:
public Expression<Func<T, bool>> CombinePredicates<T>(IList<Expression<Func<T, bool>>> predicateExpressions,
IList<Func<Expression, Expression, BinaryExpression>> logicalFunctions)
{
Expression<Func<T, bool>> filter = null;
if (predicateExpressions.Count > 0)
{
Expression<Func<T, bool>> firstPredicate = predicateExpressions[0];
Expression body = firstPredicate.Body;
for (int i = 1; i < predicateExpressions.Count; i++)
{
body = logicalFunctions[i - 1](body, predicateExpressions[i].Body);
}
filter = Expression.Lambda<Func<T, bool>>(body, firstPredicate.Parameters);
}
return filter;
}
And in order to combine the predicate expressions that we mentioned above we invoke the method like in the sample bellow:
Expression<Func<Customer, bool>>[] filterExpressions = new Expression<Func<Customer, bool>>[] { filter1, filter2, filter3 };
Func<Expression, Expression, BinaryExpression>[] operators = new Func<Expression, Expression, BinaryExpression>[] { Expression.OrElse , Expression.AndAlso};
Expression<Func<Customer, bool>> filter = this.CombinePredicates<Customer>(filterExpressions, operators);
What we get in the filter variable is the following expression:
filter = {c => ((c.City.StartsWith("S") || c.City.StartsWith("M")) && (c.ContactTitle = "Owner"))}
And finally when this combined predicate expression is used in a query like bellow:
IObjectScope objectScope = ObjectScopeProvider1.GetNewObjectScope();
IQueryable<Customer> query = objectScope.Extent<Customer>().Where(filter);
We get the following SQL generated for us by OpenAccess ORM:
"SELECT [CustomerID] AS COL1, [Address] AS COL2, [City] AS COL3, [CompanyName] AS COL4, [ContactName] AS COL5, [ContactTitle] AS COL6, [Country] AS COL7, [Fax] AS COL8, [Phone] AS COL9, [PostalCode] AS COL10, [Region] AS COL11 FROM [Customers] WHERE ([City] LIKE 'S%' OR [City] LIKE 'M%') AND [ContactTitle] = 'Owner' "
Happy LINQ-ing.