Linq2SQL Where Between

Question

Is is possible to do this in SQL in Linq to SQL?

Select field from table where date between '2010-01-01' and '2010-01-31';

I realize I could do:

where (monthBeginDate < a.StopDateActual && a.StopDateActual < monthEndDate)

But I was curious if I could do the former. I have a bad habit of screwing up the less than and greater than on statements like that.

Answer 1

Nope, that's the way to do it. C# does not have a between operator I believe.

You could always cheat and write an extension method like I do:

public static bool BetweenDates (this DateTime checker, DateTime floor, DateTime ceiling)
{
    return (checker <= ceiling) && (checker >= floor);
}

Then you can do this ;-)

.Where(s => s.StopDateActual.BetweenDates(monthBeginDate, monthEndDate));

Answer 2

You could do the following:

public bool Between(DateTime value, DateTime from, DateTime To) {
   return value > from && value < to;
}

where Between(a.StopDateActual, monthBeginDate, monthEndDate)

However, you should note that this works on IEnumerable and not on IQueryable, i.e. the results will be pulled from the data source before the where is applied. This could be a performance issue in the case of a large ammount of data, therefore, your where clasue is the best you can do ... just be careful with the > and < !!!