Dynamic query with OR conditions in Entity Framework
C#Sql ServerEntity FrameworkLinqLinq to-EntitiesC# Problem Overview
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: https://stackoverflow.com/q/5541234/810850. 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);
}
C# Solutions
Solution 1 - C#
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.
Solution 2 - C#
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();
Solution 3 - C#
> Is there a simple and clean way to add "OR" conditions to a dynamically generated query using entity framework?
Yes, you can achieve this by simply relying on a single where clause containing a single boolean expression whose OR parts are "disabled" or "enabled" dynamically at runtime, thus, avoiding having to install LINQKit or writing a custom predicate builder.
In reference to your example:
var isFirstValid = !string.IsNullOrWhiteSpace(first);
var isLastValid = !string.IsNullOrWhiteSpace(last);
var query = db.Names
.AsQueryable()
.Where(name =>
(isFirstValid && name.first.Contains(first)) ||
(isLastValid && name.last.Contains(last))
)
.ToList();
As you can see in the example above, we are dynamically switching "on" or "off" the OR-parts of the where-filter expression based on previously evaluated premises (e.g isFirstValid).
For example if isFirstValid is not true, then name.first.Contains(first) is short-circuited and will neither be executed nor affect the resultset. Moreover, EF Core's DefaultQuerySqlGenerator will further optimize and reduce the boolean expression inside where before executing it (e.g. false && x || true && y || false && z may be reduced to simply y through simple static analysis).
Please note: If none of the premises are true, then the result-set will be empty – which I assume is the desired behavior in your case. However, if you for some reason rather prefer to select all elements from your IQueryable source, then you may add a final variable to the expression evaluating to true (e.g. .Where( ... || shouldReturnAll) with var shouldReturnAll = !(isFirstValid || isLastValid) or something similar).
A final remark: The downside of this technique is that it forces you to build a "centralized" boolean expression that resides in the same method body in which your query lies (more precisely the where part of the query). If you, for some reason, want to decentralize the build process of your predicates and inject them as arguments or chain them via the query builder, then you should better stick with a predicate builder as suggested in the other answers. Otherwise, enjoy this simple technique :)
Solution 4 - C#
Basedon StriplingWarrior's answer, I write my linq extension to do this work in linq way:
https://github.com/Flithor/ReusableCodes/blob/main/EFCore/OrPredicate.cs
The codes(may not newest):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace Flithors_ReusableCodes
{
/// <summary>
/// Make <see cref="IQueryable{T}"/> support or predicate in linq way
/// </summary>
/// <typeparam name="T"></typeparam>
public interface IQueryOr<T>
{
IQueryOr<T> WhereOr(Expression<Func<T, bool>> predicate);
IQueryable<T> AsQueryable();
}
/// <summary>
/// The extension methods about or predicate
/// </summary>
/// <typeparam name="T"></typeparam>
public static class OrPredicate
{
/// <summary>
/// Private or predicate builder
/// </summary>
/// <typeparam name="T"></typeparam>
private class OrPredicateBuilder<T> : IQueryOr<T>
{
List<Expression<Func<T, bool>>> predicates = new List<Expression<Func<T, bool>>>();
IQueryable<T> sourceQueryable;
#region private methods
internal OrPredicateBuilder(IQueryable<T> sourceQueryable) => this.sourceQueryable = sourceQueryable;
private OrPredicate(IQueryable<T> sourceQueryable, IEnumerable<Expression<Func<T, bool>>> predicates)
{
this.sourceQueryable = sourceQueryable;
this.predicates.AddRange(predicates);
}
//===============================================
// Code From: https://stackoverflow.com/a/50414456/6859121
private 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));
}
}
private static TExpression ReplaceParameter<TExpression>(TExpression expr, ParameterExpression toReplace, ParameterExpression replacement) where TExpression : Expression
{
var replacer = new ExpressionReplacer(e => e == toReplace ? replacement : e);
return (TExpression)replacer.Visit(expr);
}
private static Expression<Func<TEntity, TReturn>> Join<TEntity, TReturn>(Func<Expression, Expression, BinaryExpression> joiner, IReadOnlyCollection<Expression<Func<TEntity, TReturn>>> expressions)
{
if (!expressions.Any())
{
throw new ArgumentException("No expressions were provided");
}
var firstExpression = expressions.First();
if (expressions.Count == 1)
{
return firstExpression;
}
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<TEntity, TReturn>>(joinedBodies, firstParameter);
}
//================================================
private Expression<Func<T, bool>> GetExpression() => Join(Expression.Or, predicates);
#endregion
#region public methods
public IQueryOr<T> WhereOr(Expression<Func<T, bool>> predicate)
{
return new OrPredicate<T>(sourceQueryable, predicates.Append(predicate));
}
public IQueryable<T> AsQueryable()
{
if (predicates.Count > 0)
return sourceQueryable.Where(GetExpression());
else // If not any predicates exists, returns orignal query
return sourceQueryable;
}
#endregion
}
/// <summary>
/// Convert <see cref="IQueryable{T}"/> to <see cref="IQueryOr{T}"/> to make next condition append as or predicate.
/// Call <see cref="IQueryOr{T}.AsQueryable"/> back to <see cref="IQueryable{T}"/> linq.
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <param name="source"></param>
/// <returns></returns>
public static IQueryOr<TSource> AsWhereOr<TSource>(this IQueryable<TSource> source)
{
return new OrPredicateBuilder<TSource>(source);
}
}
}
How to use it:
// IQueryable<ClassA> myQuery = ....;
var queryOr = myQuery.AsWhereOr();
// for a condition list ...
// queryOr = queryOr.WhereOr(a => /*some condition*/)
myQuery = queryOr.AsQueryable();
Enjoy!