A Tip For Writing Entity Framework Queries Using DateTime Values

Today I found a nice utility method for working with DateTime values in Entity Framework LINQ queries. I wanted a list of Events which fell between a date range, but my query needed to ignore the time portion of the DateTime values. My first attempt was this:

var events = this.coreDomainContext.Events.Where(
    e => e.EventDate.Value.Date >= DateTime.Today
      && e.EventDate.Value.Date <= endPeriod.Date)
    .OrderByDescending(e => e.EventDate)
    .ToList();

But when I ran the code, I got an exception because I was using the .Date property of DateTime - basically EF did not know what to do here to convert this into a query. So after some research I found EntityFunctions.TruncateTime. This worked like a charm:

var events = this.coreDomainContext.Events.Where(
    e => EntityFunctions.TruncateTime(e.EventDate.Value) >= DateTime.Today
      && EntityFunctions.TruncateTime(e.EventDate.Value) <= EntityFunctions.TruncateTime(endPeriod))
    .OrderByDescending(e => e.EventDate)
    .ToList();

EntityFunctions contains a ton of methods for working with EF entities, have a look through what’s on offer as it could save you from rolling your own EF hacks!