C# Method to Compare DateTime Fields Specified During Execution?

Question

My project has many objects with date fields, and I often need to select everything where one such field is within a date range.

For example:

public class Contract
{
    public DateTime SignDate { get; set; }
    public DateTime ReleaseDate { get; set; }
}

public class PersonalCheck
{
    public DateTime SignDate { get; set; }
    public DateTime ProcessDate { get; set; }
    public DateTime VoidDate { get; set; }
}

If I only cared about SignDate, it would be easy. I would declare an Interface...

public interface IObjectWithSignDate
{
    DateTime SignDate { get; set; }
}

...change my other objects to inherit from it, then create a method like this:

    public static IQueryable<T> SignedWithin<T>(this IQueryable<T> items, DateTime start, DateTime end) where T : IObjectWithSignDate
    {
        return items.Where(q => q.SignDate >= start && q.SignDate <= end);
    }

How can I avoid rewriting this function for ReleaseDate, ProcessDate, VoidDate, etc.? Can I make this method take in an IQueryable of any object and a variable telling it which date field to run this selector against?

Note this would have to be able to a) execute in LinqToEntities to run against a database and b) not add a lot of overhead (as I'm fearful reflection might do)

Answer 1

Simple but specific

You can add an extension method like this:

public static class DateTimeExtensions
{
    public static bool IsBetween(this DateTime thisDateTime, DateTime start, DateTime end)
    {
        return thisDateTime >= start && thisDateTime <= end;
    }
}

which you can unit test in isolation.

Then you can use this on whichever DateTime field you want to check. For example:

var start = new DateTime(2017, 1, 1);
var end = new DateTime(2017, 12, 31, 23, 59, 59);
IList<Contract> contracts = new List<Contract>(); // or anything enumerable
var contractsSignedBetween = contracts.Where(x => x.SignDate.IsBetween(start, end));
var contractsReleasedBetween = contracts.Where(x => x.ReleaseDate.IsBetween(start, end));

(Notice how I set the start datetime to have 00:00:00 time, and the end datetime to have 23:59:59 time [feel free to include milliseconds as well], so that times within the last day are included.)

Making that reusable

If you find yourself needing to do that a lot, you could do an extension for that

public static class EnumerableContractsExtensions
{
    public static IEnumerable<Contract> SignedBetween(this IEnumerable<Contract> contracts, DateTime start, DateTime end)
    {
        return contracts.Where(x => x.SignDate.IsBetween(start, end));
    }
}

and use it like this

 var contractsSignedBetween = contracts.SignedBetween(start, end);

which could also be unit tested in isolation.

More flexible but specific

Use an expression to say which date you want...

public static class EnumerableContractsExtensions
{
    public static IEnumerable<Contract> Between(this IEnumerable<Contract> contracts, Func<Contract, DateTime> selector, DateTime start, DateTime end)
    {
        return contracts.Where(x => selector(x).IsBetween(start, end));
    }
}

and then do:

var contractsSignedBetween = contracts.Between(x => x.SignDate, start, end);
var contractsReleasedBetween = contracts.Between(x => x.ReleaseDate, start, end);

Flexible and generic

Go the whole hog and do it generically (although you can't make it an extension method since it's generic):

public static class EnumerableExtensions
{
    public static IEnumerable<T> Between<T>(IEnumerable<T> items, Func<T, DateTime> selector, DateTime start, DateTime end)
    {
        return items.Where(x => selector(x).IsBetween(start, end));
    }
}

again, this is testable in its own right, and can be used like this:

IList<Contract> contracts = new List<Contract>();
IList<PersonalCheck> personalChecks = new List<PersonalCheck>();
var contractsSignedBetween = EnumerableExtensions.Between(contracts, x => x.SignDate, start, end);
var checksSignedBetween = EnumerableExtensions.Between(personalChecks, x => x.SignDate, start, end);

Making it IQueryable

To make this work as IQueryable the approach needs to shift to an expression tree, since LINQ to Entities does not know how to translate a method into SQL.

public static IQueryable<TSource> Between<TSource, TKey>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    TKey low,
    TKey high)
    where TKey : IComparable<TKey>
{
    Expression key = keySelector.Body;
    Expression lowerBound = Expression.LessThanOrEqual(Expression.Constant(low), key);
    Expression upperBound = Expression.LessThanOrEqual(key, Expression.Constant(high));
    Expression and = Expression.AndAlso(lowerBound, upperBound);
    Expression<Func<TSource, bool>> lambda =
        Expression.Lambda<Func<TSource, bool>>(and, keySelector.Parameters);
    return source.Where(lambda);
}

which would still be used like this:

var contractsSignedBetween = contracts.Between(x => x.SignDate, start, end);

And this works for things other than DateTimes as well. Hope this helps.