score:3

Accepted answer

You could try something like that

public static IQueryable<TElement> IsDateBetween<TElement>(this IQueryable<TElement> queryable, 
                                                           Expression<Func<TElement, DateTime>> fromDate, 
                                                           Expression<Func<TElement, DateTime>> toDate, 
                                                           DateTime date)
{
    var p = fromDate.Parameters.Single();
    Expression member = p;

    Expression fromExpression = Expression.Property(member, (fromDate.Body as MemberExpression).Member.Name);
    Expression toExpression = Expression.Property(member, (toDate.Body as MemberExpression).Member.Name);

    var after = Expression.LessThanOrEqual(fromExpression,
         Expression.Constant(date, typeof(DateTime)));

    var before = Expression.GreaterThanOrEqual(
        toExpression, Expression.Constant(date, typeof(DateTime)));

    Expression body = Expression.And(after, before);

    var predicate = Expression.Lambda<Func<TElement, bool>>(body, p);
    return queryable.Where(predicate);
}

usage

DataContext.EventHistories.WhereDateBetween(h =>  h.FromDate, h => h.ToDate, dateInTheMiddle));

but it's quite complicated to do

var myDate = DateTime.Now();

var result = DataContext.EventHistories.Where(m => m.FromDate >= myDate && m.ToDate<=myDate );

EDIT

Well, to simulate the DateTime.Date, you could do :

var myDate = DateTime.Now.Date();
var res = histories.AsQueryable()
.Where(m => 
EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) >= DateTime.Now && 
EntityFunctions.CreateDateTime(m.ToDate.Year, m.ToDate.Month, m.ToDate.Day, 0, 0, 0) <= DateTime.Now)

Or create an interface

public interface IFromDateToDate
{
    DateTime FromDate { get; set; }
    DateTime ToDate { get; set; }
}

and an extension method

public static IQueryable<T> WhereDatesBetween<T>(this IQueryable<T> queryable, DateTime myDate) where T : IFromDateToDate
{
    myDate = myDate.Date;
    return queryable.Where(m =>
                           EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) >= myDate &&
                           EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) <= myDate);
}

usage :

DataContext.EventHistories.WhereDatesBetween(dateInTheMiddle));

score:-1

Why bother with all that magic? Why not just do this?

if (date >= startDate and date <= endDate) {

}

score:1

LinQ to Entities DOES support DateTime - i use it in a project and there's no problem about it:

var now = DateTime.Now;    
var expiredEntities = entities.Repositories.Where(repository => repository.ExpiryDate < now).ToList();

score:0

Now, if I follow this correct, you would really like to write:

from item in MyTable
where FromDate <= Item.Somedate.Date 
   && Item.Somedate.Date <= ToDate
select item

But, you can't because the Date property of DateTime isn't supported in LINQ to entities.

Similarly, you can't write:

from item in MyTable
where FromDate <= Item.SomeDate 
   && Item.SomeDate <= ToDate
select item

because, I assume, ToDate is set to midnight, and if Item.SomeDate has a time, it will be after ToDate, even if it's on ToDate.

If that's the case, the proper solution, which is the common practice, is to make the upper limit an exclusive value (i.e, the first thing that isnot part of the set). Hence:

  toDate = toDate.Date.AddDay(1);

from item in MyTable
where FromDate <= Item.SomeDate 
   && Item.SomeDate < ToDate
select item

Related Articles