Entity Framework Custom Query Function

Question

I have an Entity Framework 4.0 Entity Object called Revision w/ Nullable DateEffectiveFrom and DateEffectiveTo dates. I was wondering if there was a short-hand way of querying an object's RevisionHistory based on a particular QueryDate date instead of having to use the following query structure:

var results = EntityObject.Revisions.Where(x =>
    (x.DateEffectiveFrom == null && x.DateEffectiveTo == null) ||
    (x.DateEffectiveFrom == null && x.DateEffectiveTo >= QueryDate) ||
    (x.DateEffectiveFrom <= QueryDate && x.DateEffectiveTo == null) ||
    (x.DateEffectiveFrom <= QueryDate && x.DateEffectiveTo >= QueryDate));

I've tried creating the following boolean function in the Revision class:

partial class Revision
{
    public bool IsEffectiveOn(DateTime date)
    {
        return (x.DateEffectiveFrom == null && x.DateEffectiveTo == null) ||
            (x.DateEffectiveFrom == null && x.DateEffectiveTo >= date) ||
            (x.DateEffectiveFrom <= date && x.DateEffectiveTo == null) ||
            (x.DateEffectiveFrom <= date && x.DateEffectiveTo >= date));
    }
    ...
}

And then updating the query to:

var results = EntityObject.Revisions.Where(x => x.IsEffectiveOn(QueryDate));

but this obviously doesn't translate to SQL. Any ideas would be much appreciated.

Answer 1

You can try Predicate Builder, and see if that translates to the appropriate SQL.

Answer 2

The way you craft this query depends partly on what NULL means for EffectiveFrom and EffectiveTo.

If EffectiveFrom is NULL, should that mean that it is effective for all dates before EffectiveTo, and the reverse for NULL EffectiveTo? If that is the case, you can use DateTime.MinValue as a replacement for NULL EffectiveFrom values, and DateTime.MaxValue for EffectiveTo. At that point you can simply use BETWEEN-style queries:

Where(x => x.DateEffectiveFrom > QueryDate < x.DateEffectiveTo);

Answer 3

You can make your function return an Expression, rather than a bool:

partial class Revision
{
    public static Expression<Func<Revision, bool>> IsEffectiveOn(DateTime date)
    {
        return x => (x.DateEffectiveFrom == null && x.DateEffectiveTo == null) ||
            (x.DateEffectiveFrom == null && x.DateEffectiveTo >= date) ||
            (x.DateEffectiveFrom <= date && x.DateEffectiveTo == null) ||
            (x.DateEffectiveFrom <= date && x.DateEffectiveTo >= date));
    }
}

Then you can use it:

var predicate = Revision.IsEffectiveOn(DateTime.Now);
var results = EntityObject.Revisions.Where(predicate);

...and that will translate to SQL.