CODEWITHSUNDEEP
.netentity-frameworklinq-to-entities
Josh
Josh

Reputation: 85

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

Upvotes: 2

Views: 1269

Answers (3)

Craig Stuntz
Craig Stuntz

Reputation: 126547

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.

Upvotes: 3

Dave Swersky
Dave Swersky

Reputation: 34810

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

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

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

Upvotes: 1

Related Questions