Extensions for IQuerable to filter a DateTime-Range-CodePudding

I have a WebApi (ASP.NET Core) and a route that is returning a set of transactions. I want to add two filter-parameters via QueryString, the start-date and the end-date to filter these transactions by date (a date-range).

These parameters are both optional, so I have them as Nullable-Types.

Since this kind of filtering by date is very common in my app I want to avoid code like that:

if (start.HasValue) {
    transactions = transaction.Where(x => x.TimestampUtc >= start.Value);
}

if (end.HasValue) {
    transactions = transaction.Where(x => x.TimestampUtc <= end.Value);
}

So the idea was to create an extension-method called DateTimeRange for IQueryable. This method takes 3 parameters, the field (TimestampUtc in my case) for date-filtering and the start and end-date.

[HttpGet("transactions")]
public IActionResult GetTransactions(DateTime? start, DateTime? end) {
    IQueryable<Transaction> transactions = this.TransactionRepository.All
        .DateTimeRange(x => x.TimestampUtc, start, end)
    ;

    return this.Ok(transactions);
}

public static IQueryable<TSource> DateTimeRange<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, DateTime>> keySelector, DateTime? start, DateTime? end) {
    // what to do here to filter my set?

    return source;
}

I am stuck what to do in this extension-method to filter my set. It's important to me that I am working with IQueryable so the actual database-action is done on the database and NOT in memory for performanc-reasons.

Thanks!

CodePudding user response：

Something like this

public static IQueryable<TSource> DateTimeRange<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, DateTime>> selector, DateTime? start, DateTime? end)
{
    var startNotNull = start ?? DateTime.MinValue;
    var endNotNull = end ?? DateTime.MaxValue;

    var selectStatement = selector.Compile();
        
    return source.Where(i => selectStatement(i) >= startNotNull && selectStatement(i) <= endNotNull);
}

Edit

As @GuyVdN said ...

Performance wise I don't think it is a good idea to always execute the Where statement when start, end or both are null. It would be better to adjust the Where statement or not execute it at all when these values are null.

Here's my new version fixing the optimization issues.

public static IQueryable<TSource> DateTimeRange<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, DateTime>> selector, DateTime? start, DateTime? end)
{
    var compiledSelect = selector.Compile();

    return start switch
    {
        null when end is not null => source.Where(i => compiledSelect(i) <= end),
        not null when end is null => source.Where(i => compiledSelect(i) >= start),
        not null => source.Where(i => compiledSelect(i) >= start && compiledSelect(i) <= end),
        _ => throw new InvalidOperationException()
    };
}

CodePudding user response：

Using Expression.Compile may not work in EF (as it need expression to build SQL from it)

So here is solution which should work (as it's based on expressions)

    public static IQueryable<TSource> DateTimeRange<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, DateTime>> keySelector, DateTime? start, DateTime? end)
    {
        if (!start.HasValue && !end.HasValue)
            return source;
        var parameter = keySelector.Parameters.Single();
        var key = Expression.Property(parameter, (keySelector.Body as MemberExpression).Member.Name);
        Expression before = end.HasValue ?  Expression.LessThanOrEqual(key, Expression.Constant(end, typeof(DateTime))) : null;
        Expression after = start.HasValue ? Expression.GreaterThanOrEqual(key, Expression.Constant(start, typeof(DateTime))) : null;
        Expression<Func<TSource, bool>> predicate;
        if (before == null)
        {
            predicate = Expression.Lambda<Func<TSource, bool>>(after, parameter);
        }
        else
        {
            if (after == null)
                predicate = Expression.Lambda<Func<TSource, bool>>(before, parameter);
            else
                predicate = Expression.Lambda<Func<TSource, bool>>(Expression.And(after, before), parameter);
        }
        return source.Where(predicate);
    }

You can check the results here

It is based on this SO answer