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)

If you add a parameter of `Expression>` you could pass in the predicate for your where clause. Your caller would still need to know which dates on the object it wanted to be compared, but it wouldn't be tied to a particular method implementation. — Jonathon Chase, Jun 01 '18 at 17:24
Wouldn't I still need to create a separate predicate for each date field? — James in Indy, Jun 01 '18 at 17:39
From the call site, yes. You're going to have to specify the date field _somewhere_. — Jonathon Chase, Jun 01 '18 at 17:44
My issue here is, if I create a predicate for each of the 6+ different date fields in my app (that all use the same compare rules), I need to have unit tests for all of them. It's a problem I already have. I'm looking for a way to bring the method count down to 1 to make it nice and DRY. — James in Indy, Jun 01 '18 at 17:49
Well, you have 6+ different logic flows. They may be similar, but they're all distinct. If you go the expression route, you could test your cases with one method by providing the expression and IQueryable as parameters for the test method, and have everything call through a single method, but that's just turning your problem of multiple unit tests into multiple test cases. I don't see how you'll get away from that, but I may be missing something. — Jonathon Chase, Jun 01 '18 at 17:53
@JonathonChase, I believe Richardissimo's answer below is where you were trying to lead me... thanks for your help! — James in Indy, Jun 04 '18 at 12:01

Richardissimo · Accepted Answer · 2018-07-12T05:53:05.447

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.

Summarizing prior discussion: Thanks for the very detailed example! The IEnumerable query works in Linq-to-SQL, but actually executes in memory. Against a DB table with 10K+ records, it has to load them all before executing "Between," killing performance. The IQueryable part at the end doesn't work against SQL--it crashes with "LINQ expression node type 'Invoke' is not supported in LINQ to Entities." — James in Indy, Jul 11 '18 at 17:23
@JamesinIndy Aaargh. I was overcomplicating it. The only change is to the "key" line. — Richardissimo, Jul 12 '18 at 05:56
That did the trick! I'll have to do some further testing next week to make sure this will meet my needs, but I believe it will. Thanks so much! — James in Indy, Jul 13 '18 at 20:16
Woohoo! This was so much fun, with us virtually working together towards the solution across the timezones, and the twist that it led to finding a bug in **my** code. I love this site. — Richardissimo, Jul 14 '18 at 02:26
Glad you got something out of this too! Further testing looks good. I've marked this as the official answer. Thanks for sticking with it! — James in Indy, Jul 17 '18 at 18:45

C# Method to Compare DateTime Fields Specified During Execution?

1 Answers1