Combine Where clauses with OR in lambda query

Question

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);

Answer 1

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'