Dynamic query with OR conditions in Entity Framework

Question

I am creating an application that searches the database and allows the user to dynamically add any criteria (around 50 possible), much like the following SO question: Creating dynamic queries with entity framework. I currently have working a search that checks each criteria, and if it is not blank it adds it to the query.

C#

var query = Db.Names.AsQueryable();   if (!string.IsNullOrWhiteSpace(first))       query = query.Where(q => q.first.Contains(first));   if (!string.IsNullOrWhiteSpace(last))       query = query.Where(q => q.last.Contains(last));   //.. around 50 additional criteria   return query.ToList(); 

This code produces something similar to the following in sql server (I simplified for easier understanding)

SQL

SELECT     [Id],     [FirstName],     [LastName],     ...etc FROM [dbo].[Names] WHERE [FirstName] LIKE '%first%'   AND [LastName] LIKE '%last%' 

I am now trying to add a way to generate the following SQL with C# through entity framework but with an OR instead of an AND, while still keeping the ability to add criteria dynamically.

SQL

SELECT     [Id],     [FirstName],     [LastName],     ...etc   FROM [dbo].[Names] WHERE [FirstName] LIKE '%first%'   OR [LastName] LIKE '%last%' <-- NOTICE THE "OR" 

Usually the criteria wont be larger than two or three items for a query but combining them into one gigantic query is not an option. I have tried concat, union, and intersect and they just all duplicate the query and join them with UNION.

Is there a simple and clean way to add "OR" conditions to a dynamically generated query using entity framework?

Edit with my solution - 9/29/2015

Since posting this, I have noticed this has received a little attention, so I decided to post my solution

// Make sure to add required nuget // PM> Install-Package LinqKit  var searchCriteria = new  {     FirstName = "sha",     LastName = "hill",     Address = string.Empty,     Dob = (DateTime?)new DateTime(1970, 1, 1),     MaritalStatus = "S",     HireDate = (DateTime?)null,     LoginId = string.Empty, };  var predicate = PredicateBuilder.False<Person>(); if (!string.IsNullOrWhiteSpace(searchCriteria.FirstName)) {     predicate = predicate.Or(p => p.FirstName.Contains(searchCriteria.FirstName)); }  if (!string.IsNullOrWhiteSpace(searchCriteria.LastName)) {     predicate = predicate.Or(p => p.LastName.Contains(searchCriteria.LastName)); }  // Quite a few more conditions...  foreach(var person in this.Persons.Where(predicate.Compile())) {     Console.WriteLine("First: {0} Last: {1}", person.FirstName, person.LastName); } 

Answer 1

You're probably looking for something like Predicate Builder which allows you to control the AND's and OR's of the where statement easier.

There's also Dynamic Linq which allows you to submit the WHERE clause like a SQL string and it will parse it into the correct predicate for a WHERE.

Answer 2

While LINQKit and its PredicateBuilder are fairly versatile, it's possible to do this more directly with a few simple utilities (each of which can serve as the foundation for other Expression-manipulating operations):

First, a general-purpose Expression Replacer:

public class ExpressionReplacer : ExpressionVisitor {     private readonly Func<Expression, Expression> replacer;      public ExpressionReplacer(Func<Expression, Expression> replacer)     {         this.replacer = replacer;     }      public override Expression Visit(Expression node)     {         return base.Visit(replacer(node));     } } 

Next, a simple utility method to replace one parameter's usage with another parameter in a given expression:

public static T ReplaceParameter<T>(T expr, ParameterExpression toReplace, ParameterExpression replacement)     where T : Expression {     var replacer = new ExpressionReplacer(e => e == toReplace ? replacement : e);     return (T)replacer.Visit(expr); } 

This is necessary because the lambda parameters in two different expressions are actually different parameters, even when they have the same name. For example, if you want to end up with q => q.first.Contains(first) || q.last.Contains(last), then the q in q.last.Contains(last) must be the exact same q that's provided at the beginning of the lambda expression.

Next we need a general-purpose Join method that's capable of joining Func<T, TReturn>-style Lambda Expressions together with a given Binary Expression generator.

public static Expression<Func<T, TReturn>> Join<T, TReturn>(Func<Expression, Expression, BinaryExpression> joiner, IReadOnlyCollection<Expression<Func<T, TReturn>>> expressions) {     if (!expressions.Any())     {         throw new ArgumentException("No expressions were provided");     }     var firstExpression = expressions.First();     var otherExpressions = expressions.Skip(1);     var firstParameter = firstExpression.Parameters.Single();     var otherExpressionsWithParameterReplaced = otherExpressions.Select(e => ReplaceParameter(e.Body, e.Parameters.Single(), firstParameter));     var bodies = new[] { firstExpression.Body }.Concat(otherExpressionsWithParameterReplaced);     var joinedBodies = bodies.Aggregate(joiner);     return Expression.Lambda<Func<T, TReturn>>(joinedBodies, firstParameter); } 

We'll use this with Expression.Or, but you could use the same method for a variety of purposes, like combining numeric expressions with Expression.Add.

Finally, putting it all together, you can have something like this:

var searchCriteria = new List<Expression<Func<Name, bool>>();    if (!string.IsNullOrWhiteSpace(first))       searchCriteria.Add(q => q.first.Contains(first));   if (!string.IsNullOrWhiteSpace(last))       searchCriteria.Add(q => q.last.Contains(last));   //.. around 50 additional criteria var query = Db.Names.AsQueryable(); if(searchCriteria.Any()) {     var joinedSearchCriteria = Join(Expression.Or, searchCriteria);     query = query.Where(joinedSearchCriteria); }   return query.ToList();