Why is this LINQ Where clause not returning results?

Question

We have an entity with DateTime property DateDestroyed. A query needs to return results where this value is between nullable DateTimes startDate and endDate.

The where clauses I have are:

.Where(x => startDate.HasValue ? startDate <= x.DateDestroyed : true)
.Where(x => endDate.HasValue ? x.DateDestroyed <= endDate : true);

The query always return no results. I'm pretty sure I haven't written this query properly but don't know how it should be written or why it's not working?

Answer 1

let's say you have a variable called "query" that you've stored the beginning part of your linq statement. Try this to dynamically construct the where clause:

if (startDate.HasValue) {
    query = query.Where(x => x.DateDestroyed >= startDate);
}
if (endDate.HasValue) {
    query = query.Where(x => x.DateDestroyed <= endDate);
}

LINQ works on deferred execution so the WHERE clause will correctly resolve when the code executes.

Answer 2

You could create/use an extension method for WhereIf:

Given a boolean condition, append a Where clause.

 var foo = db.Customers.WhereIf(startDate.HasValue, 
                                   x => startDate <= x.DateDestroyed)
                       .WhereIf(endDate.HasValue,  
                                   x => x.DateDestroyed <= endDate );

More details at WhereIf at ExtensionMethod.net. You can find the code there for IEnumerable<T> and IQueryable<T>.

Answer 3

Are you always re-assigning your query with the Where() filter applied?

This pattern should work as expected:

var query = getResults();
query = query.Where(x => startDate.HasValue ? startDate <= x.DateDestroyed : true)
query = query.Where(x => endDate.HasValue ? x.DateDestroyed <= endDate : true);

Answer 4

My code requires IQueryable so I adapted the work by @p.campbell at ExtensionMethod.net as follows:

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Func<TSource, bool> predicate)
{
    return condition ? source.Where(predicate).AsQueryable() : source;
}

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Func<TSource, int, bool> predicate)
{
    return condition ? source.Where(predicate).AsQueryable() : source;
}