In general the same series of operations on IEnumerable<T> and IQueryable<T> with the same contents should produce the same output... give or take case sensitive comparisons and so on.
Consider the following:
int[] data = new[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
var query = data.Where(i => i > 3);
query = query.Where(i => i < 8);
When you enumerate over query (which is of type IEnumerable<int>) the operations are applied in sequence. Values which fail the first Where do not get evaluated by the second, so can never reach the result set:
| Value |
First Where |
Second Where |
Output |
| 1 |
Fail |
-- |
No |
| 2 |
Fail |
-- |
No |
| 3 |
Fail |
-- |
No |
| 4 |
Pass |
Pass |
Yes |
| 5 |
Pass |
Pass |
Yes |
| 6 |
Pass |
Pass |
Yes |
| 7 |
Pass |
Pass |
Yes |
| 8 |
Pass |
Fail |
No |
| 9 |
Pass |
Fail |
No |
| 10 |
Pass |
Fail |
No |
This is equivalent to:
query = data.Where(i => i > 3 && i < 8);
The equivalent SQL WHERE would then be:
WHERE i > 3 AND i < 8
While this works in the case of restrictive filters (where each term must applied), it doesn't do much for permissive filters (where any term may apply). Instead we have to look at other ways to do query composition.
For IEnumerable<T> we can simply use function composition:
string title = "A Title";
int level = 1;
Func<recordType, bool> predicate = x => x.Title == title;
predicate = x => predicate(x) || x.Level == level;
var query = data.Where(predicate);
Unfortunately this isn't quite as simple for IQueryable<T> since the predicate type is a LINQ Expression of type Expression<Func<T, bool>>, and we can't create a suitable expression tree from a Func<T, bool>. Instead we have to do lambda expression composition.
The simplest point to start at is implementing an OrElse expression compositor that takes two lambda expressions and invokes them as parameters to an OrElse expression:
public static Expression<Func<T, bool>> OrElse<T>(Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
{
if (left is null)
return right;
if (right is null)
return left;
var parm = Expression.Parameter(typeof(T), "row");
var result = Expression.Lambda<Func<T, bool>>
(
Expression.OrElse
(
Expression.Invoke(left, parm),
Expression.Invoke(right, parm)
),
parm
);
return result;
}
(This could be an extension method, but I think it's bad form to design extensions that explicitly permit targets.)
We can then use this to compose a predicate expression from optional parts:
// 'Book' in this case is a placeholder for your record type.
Expression<Func<Book, bool>> MakePermissiveFilter(string title, int? level)
{
Expression<Func<Book, bool>> result = null;
if (!string.IsNullOrEmpty(title))
result = OrElse(result, b => b.Title == title);
if (level is not null)
result = OrElse(result, b => b.Level == level);
// if nothing selected return a default 'always true' predicate
if (result is null)
result = b => true;
return result;
}
Now we can invoke that to generate the required filter expression for your Where clause:
// 'query' previously defined with select and optional ordering
query = query.Where(MakePermissiveFilter(text, level));
This will work for any compliant IQueryable<T>: LinqToObjects, LinqToSQL, Entity Framework (all versions) and so on.
The resultant predicate is slightly inelegant, but SQL generation will reduce the noise in most cases. You might end up with a WHERE 1 = 1 in there if the filter terms are empty.
We could go a lot further and unwrap the supplied lamdba expressions, replace their parameters and build a new lambda, but it's a fair amount of work for relatively little gain.
