Entity Framework Custom Query Function

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(Da开发者_如何学编程teTime 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.

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.

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

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);