NHibernate filter criteria - Hour of DateTime

Question

I am having hard times convincing NHibernate (EF was able to do it) to filter based on the Hour property of a DateTime entity property. My entity goes something like :

public class Invoice {
    // ...
    public DateTime Time { get; set; }
    // ...
}

I need to retrieve all the invoices that were "made" at a certain hour (let's say 22). A possible query could be :

from   i in s.Linq<Invoice>()
where  i.Type.Id == Invoice.Type.Local &&
       i.TimeOfRegister.Hour == 22
select i

However I am being thrown an exception stating that the property Hour of TimeOfRegister could not be resolved...

LE : The details of the exception : could not resolve property: TimeOfRegister.Hour of: MyCompany.Entities.Invoice

Answer 1

Sounds like NHibernate does not see the a DateTime as a component which has properties it can select on, it sees it as a single property. You probably have to use a function to obtain the Hour part of the DateTime.

In HQL for SqlServer2005: hour(i.TimeOfRegister)

Answer 2

Well I was advised by a colleague to take another route :

Define an int (or a byte) property on the entity (let's say "HourOfRegister") and then in the mapping class map it to a formula :

Map(a => a.HourOfRegister).Formula("DATEPART(HOUR, TimeOfRegister)");

Then I can use it in the query as I like it.