Linq chaining where clauses as an Or instead of And

Question

There are multiple where clauses chained in this code. I need to change the logic for the last where clause in the below code snippet to behave as an "OR" instead of "AND". The logic behind this is that I want all transactions that fall between the passed in date range AND all transactions that have a status of TransactionStatus.Outstanding. Currently the logic is doing an AND so it is only giving me the intersect between the two.

I have seen many other posts in Stack overflow addressing the scenario I am dealing with, but none seem to help me.

Any guidance with the code syntax would be greatly appreciated!

IQueryable<Transaction> transactions = 
    _db.Transactions
        .Include(t => t.DepositSource)
        .Include(t => t.WithdrawalSource)
        .Include(t => t.ReconciliationReport)
        .Where(t => t.FundId == fundId);

if (criteria.UseClearedDate)
{
    transactions = transactions
        .Where(t => t.ReconciliationReport != null 
                    && (t.ReconciliationReport.DateCreated.Date >= criteria.StartDate.Date
                    && t.ReconciliationReport.DateCreated.Date <= criteria.EndDate.Date));
}
else
{
    transactions = transactions
        .Where(t => t.Date.Date >= criteria.StartDate.Date
                    && t.Date.Date <= criteria.EndDate.Date);
}
transactions = transactions
    .Where(t => (criteria.Statuses.Contains(t.ReconciliationId == null 
                ? t.IsVoided 
                    ? TransactionStatus.Void 
                    : TransactionStatus.Outstanding 
                : TransactionStatus.Cleared))
                && criteria.Sources.Contains(t.TransactionSource) 
                && criteria.Types.Contains(t.TransactionType));

@JSteward Can you possibly show me how to implement a PredicateBuilder in my code snippet? Thank you so much! — user11351969, Jun 17 '19 at 19:26
If you're having difficulty applying PredicateBuilder, you might be able to get by with a simple UNION of the result of your `if/else` with a new query that applies your final `Where` clause to the original set of Transactions. — StriplingWarrior, Jun 17 '19 at 19:55

score -1 · Answer 1 · answered Jun 17 '19 at 19:29

-1

There's not a way to "chain" where clauses and have them act as an or. The first Where clause will filter out anything that does not match its predicate, so there's no way for the second filter to know that the ones that don't match the first filter even exist.

You can either use the third party PredicateBuilder as suggested in the duplicate question or use a custom extension method that does it for you:

Something like (untested):

public IQueryable<T> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource,Boolean>> pred1, Expression<Func<TSource,Boolean>> pred2)
{
    return source.Where(t => pred1(t) || pred2(t));
}

answered Jun 17 '19 at 19:29

D Stanley

149,601
11
178
240

What about doing the where clauses in the reverse order? The first where clause will include all the records possible for the second where clause. The second where clause will act as an OR this way it will return everything from the first where clause AND the records that also meet the requirement of the second where clause. – user11351969 Jun 17 '19 at 19:36
That's still an AND, not an OR. Order will not matter. Suppose an item matches the second clause but not the first? – D Stanley Jun 17 '19 at 19:40
Can you please show me how to implement your solution using my code? Thank you so much! – user11351969 Jun 17 '19 at 19:44

Linq chaining where clauses as an Or instead of And

1 Answers1