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!