Combine Where clauses with OR in lambda query

I try to dynamically create a query but want to use or between the conditions. Currently my code looks like this:

if (filtersMinDate != default)
{
    query = query.Where(tv => tv.TimeStamp >= filtersMinDate);
}

if (filtersMaxDate != default)
{
    query = query.Where(tv => tv.TimeStamp <= filtersMaxDate);
}

If both dates are set, I get a final query with an and, would be the same as following:

query = query.Where(tv => tv.TimeStamp >= filtersMinDate && tv.TimeStamp <= filtersMaxDate);

But what I actually want is to dynamically generate a query that combines all conditions with an or, so should be the same as following:

query = query.Where(tv => tv.TimeStamp >= filtersMinDate || tv.TimeStamp <= filtersMaxDate);

You can implement with 2 ways (Linq or LinqKit)

1.Linq

context.MyClass.Where(tv =>
               (filtersMinDate != default && tv.Date >= filtersMinDate)
            || (filtersMaxDate != default && tv.Date <= filtersMaxDate)).ToList();

I qet Query with Profiler

exec sp_executesql N'SELECT [m].[Id], [m].[Date], [m].[LetterEnum]
FROM [MyClass] AS [m]
WHERE ([m].[Date] >= @__filtersMinDate_0) OR ([m].[Date] <= @__filtersMaxDate_1)'
,N'@__filtersMinDate_0 datetime2(7),@__filtersMaxDate_1 datetime2(7)'
,@__filtersMinDate_0='2023-06-12 03:54:02.6118428'
,@__filtersMaxDate_1='2023-06-12 03:54:03.3649045'

2.LinqKit(LINQKit is a free set of extensions for LINQ to SQL and Entity Framework power users)efcore-linqkit

var pre = PredicateBuilder.New<MyClass>(true);
var query = context.MyClass;
 if (filtersMinDate != default) 
pre.Or(tv => tv.Date >= filtersMinDate);

if (filtersMaxDate != default) 
pre.Or(tv => tv.Date <= filtersMaxDate);

I qet Query with Profiler

exec sp_executesql N'SELECT [m].[Id], [m].[Date], [m].[LetterEnum]
FROM [MyClass] AS [m]
WHERE ([m].[Date] >= @__filtersMinDate_0) OR ([m].[Date] <= @__filtersMaxDate_1)'
,N'@__filtersMinDate_0 datetime2(7),@__filtersMaxDate_1 datetime2(7)'
,@__filtersMinDate_0='2023-06-12 03:54:02.6118428'
,@__filtersMaxDate_1='2023-06-12 03:54:03.3649045'

Sounds like a job for Expression composition.

When you're constructing an IQueryable<> the various lambdas you write (like the lambda for the Where predicate) aren't compiled to IL or native code, they are instead used to build LINQ Expressions: objects that represent the code you write as syntax trees. The way they function is all kinds of interesting, but I'll save that lecture for another time. The important part is that you can tease those expressions apart and weave them back together again in interesting ways.

In this case we want to take two compatible expressions and combine their bodies into a third using the OrElse expression node. While doing so we'll have to make sure that references to the row object parameter (tv in your examples) are normalized so that the whole expression references the correct object.

Here's one of my favorite little expression helper classes to do sub-expression replacement, which is handy for getting the parameter instances to match up during expression composition:

// using System.Linq.Expression;

// Implement simple sub-expression replacement
internal class ReplaceVisitor : ExpressionVisitor
{
	private Expression _from;
	private Expression _to;
	
	private ReplaceVisitor(Expression from, Expression to) 
	{
		_from = from;
		_to = to;
	}
	
	// Return a new expression of the same type with 'from' replaced with 'to'
	public static T Replace<T>(T expression, Expression from, Expression to)
		where T : Expression
	{
		var visitor = new ReplaceVisitor(from, to);
		return (T)visitor.Visit(expression)!;
	}
	
	// This does the actual replacing when 'from' is located.
	public override Expression? Visit(Expression? node)
	{
		if (node == _from)
			return _to;
		return base.Visit(node);
	}
}

That will fix up our parameter references for us, now onto the fun part.

Let's make an extension method named Or that will combine two predicate expressions together:

internal static class ExpressionComposition
{
	// Return a predicate equivalent to 'left || right'
	public static Expression<Func<T, bool>>? Or<T>(this Expression<Func<T, bool>>? left, Expression<Func<T, bool>>? right)
	{
		// First let's eliminate null clauses
		if (left is null)
			return right;
		if (right is null)
			return left;
		
		// Now get a compatible version of the right clause's body
		var rightBody = ReplaceVisitor.Replace
		(
			right.Body, 
			right.Parameters[0], 
			left.Parameters[0]
		);
		
		// This creates the new expression using 'OrElse'
		return Expression.Lambda<Func<T, bool>>
		(
			Expression.OrElse(left.Body, rightBody), 
			left.Parameters[0]
		);
	}
}

Now your filter method can combine multiple predicates together simply:

// 'RowType' here is your... row type.
Expression<Func<RowType, bool>>? predicate = null;

if (filtersMinDate != default)
	predicate = predicate.Or(tv => tv.TimeStamp >= filtersMinDate);

if (filtersMaxDate != default)
{
	// Capture filter value to avoid mutability
	DateTime maxDate = filtersMaxDate;	
	predicate = predicate.Or(tv => tv.TimeStamp <= maxDate);
}

// Finally, filter your query with the constructed predicate:
if (predicate is not null)
	query = query.Where(predicate);

One thing to be careful of here is that captured variables can change between when you create the expression and when it is finally invoked. In the code above I've captured filtersMaxDate into a scoped variable before creating the expression. Since you can't alter that variable outside of the scope of the enclosing if statement (at least not easily) the value is fixed immediately. Changing filterMaxDate before the query is executed won't change the value used by the query. Changing filterMinDate however will change the results of the query.

Pick one of the two and stick with it in all the cases. Either your query should be responsive to changes in the filter values or it should produce a (relatively) immutable output.

And if you want you can compile the expression and execute it directly against objects in memory:

Func<RowType, bool> fn = predicate.Compile();

if (fn(row)) 
{
	// ...
}