Entity Framework Custom Query Function
- by Josh
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.
