C# Method to Compare DateTime Fields Specified During Execution?

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<Func<T, bool>> 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 1 at 17:24

Expression<Func<T, bool>>

Wouldn't I still need to create a separate predicate for each date field?
– James in Indy
Jun 1 at 17:39

From the call site, yes. You're going to have to specify the date field somewhere.
– Jonathon Chase
Jun 1 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 1 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 1 at 17:53

1 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.

IQueryable

public static IQueryable<TSource> Between<TSource, TKey>(
this IQueryable<TSource> source, TKey key,
Expression<Func<TSource, TKey>> lowSelector,
Expression<Func<TSource, TKey>> highSelector)
where TKey : IComparable<TKey>
{
Expression low = lowSelector.Body;
Expression high = highSelector.Body;

Expression lowerBound = Expression.LessThanOrEqual(
low, Expression.Constant(key));
Expression upperBound = Expression.LessThanOrEqual(
Expression.Constant(key), high);

var lowLambda = Expression.Lambda<Func<TSource, bool>>(
lowerBound, lowSelector.Parameters);
var highLambda = Expression.Lambda<Func<TSource, bool>>(
upperBound, highSelector.Parameters);

return source.Where(lowLambda).Where(highLambda);
}


which would still be used like this:

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


Hope this helps.

This seems to be exactly what I'm looking for, and it works in Linq-To-SQL! Thank you for the very detailed answer.
– James in Indy
Jun 4 at 11:56

...though it seems to have one fatal drawback: IEnumerable executes in memory, so if i'm trying to do this query against a database table with 10K+ records, it has to load them all before executing "Between," which kills performance. I"m afraid I have to un-accept this as my answer.
– James in Indy
Jun 29 at 12:07

I realize there may not be a way to make my dream of DRY come true... any solution would have to work against IQueryable (execute out-of-memory).
– James in Indy
Jun 29 at 12:09

It crashes because "Between" is not supported in LINQ to Entities.
– James in Indy
yesterday

@JamesinIndy I've worked out the problem with IQueryable and updated my answer.
– Richardissimo
16 hours ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.