Using DateTime?.Value.TimeOfDay in LINQ Query

Question

I'm trying to do a Query with LINQ on ASP.NET MVC 3.

I have a model, lets call it Event. This Event object has a Date property, of DateTime?. What I want is to fetch the Events that are between 2 TimeSpans.

Right now my code looks like the following:

TimeSpan From = new TimeSpan(8,0,0);
TimeSpan Until = new TimeSpan(22,0,0);

var events =
    from e in db.Events
    where e.Date.Value.TimeOfDay >= From,
          e.Date.Value.TimeOfDay <= Until
    select e;

An exception is thrown, telling me that "The specified type member 'TimeOfDay' is not supported in LINQ to Entities."

I don't get a way around this problem, and I have been all day trying. Please help me, I'm so frustrated. :(

EDIT:

I Forgot to write here the "TimeOfDay" after e.Date.Value. Anyway, I did in my code.

I can't use DateTime because I have to filter Events that occur between certain time of the day, despite the date of the event.

Burgundy

Your sample code doesn't even use TimeOfDay - I assume in reality it does, instead of using Date (as per your code)? – Kane

Why are you using two TimeSpan objects instead of two DateTime objects to filter the events? – Kaete

Answer 1

Use the Date and Time Canonical Functions for LINQ-to-Entities. Specifically, look at

CreateTime(hour, minute, second)

If you need help calling a canonical function, look at How To: Call Canonical Functions.

Northman

This seems to be on the track, I didn't know about Cannonical Functions. Thanks! But, how can I convert the DateTime to Time using these? – Burgundy

@Umagon: Use datetime.Hour, datetime.Minute and datetime.Second. – Northman

Oh my god! This solved my problem! I think I love you, LOL. Thank you very much! :D – Burgundy

@Umagon: I create feelings in others that they themselves don't understand. – Northman

So can I use this like context.MyTable.Where(entity => CreateTime(8, 0, 0) < CreateTime(entity.CreateTimestamp.Hour, entity.CreateTimestamp.Minute, entity.CreateTimestamp.Second) < CreateTime(16, 0, 0) to get entities which are created between 8 and 16 of each day? – Payton

Because I cannot use this with Ef core I decided to save TimeOfDay into database to ease my comparision. – Payton