nHibernate 3.0 DateTimes

Ok, so - this sucks.

I want to be able to filter date ranges in an NH 3.0 query, and I don't want to have to first scan the whole table and then feed the results through LINQ to get my range. This table could contain millions of rows and my query will be hit on each request of a certain page.

This works: -

public IList InvoiceSearch(InvoiceSearchCriteria criteria)
        {
            IList list = null;

            using (var session = SessionFactory.OpenSession())
            {
                var query = session.QueryOver < Invoice > ();

                if (criteria.DateCreatedFrom != null)
                {
                    query = query.AndRestrictionOn(x => x.DateCreated).IsBetween(criteria.DateCreatedFrom.Value).And(criteria.DateCreatedTo.Value);
                }

  .. snip

What I'm doing here is filtering my results by a date range using nHibernate.Criterion.Lambda.IQueryOverRestrictionBuilder's innate ability to write good SQL and therefore limit the set I am querying. So far so good.

I have a requirement to show all thingos (Invoices in this case) in a given month and year. According to those that know, this is simply (?) solved by mapping a YEAR and MONTH function to my Fluent entity, and then querying by those properties in my QueryOver expression, thusly: -

Entity: -

        public virtual int InvoiceYear     { get; set; }
        public virtual int InvoiceMonth    { get; set; }

        Map: -

        Map(x => x.InvoiceYear).Formula("SELECT YEAR(DateCreated)");
        Map(x => x.InvoiceMonth).Formula("SELECT MONTH(DateCreated)");     

and then doing suchlike in my querying codeage: -

    list = session.QueryOver()
                  .Where(x => x.InvoiceYear == year)
                  .And(x => x.InvoiceMonth == month)
                  .List();

Well - it doesn't work. nHibernate throws a fit when trying to compile the query expression.

In the end, the only workable solution - one that doesn't involve a complete table scan into NH entities and from there a filter, cos that'll be scalable !!! - is as follows: -

using (var session = SessionFactory.OpenSession())
            {
                DateTime from = new DateTime(year, month, 1, 0, 0, 0),
                         to   = new DateTime(year, month, DateTime.DaysInMonth(year, month), 23, 59, 59);

                list = session.QueryOver < Invoice > ()
                              .Where(x => x.Active == true)
                              .AndRestrictionOn(x => x.DateCreated).IsBetween(from).And(to)
                              .List();

            }

Got to say that annoys me somewhat, but then I guess I need to spend more time studying expression trees. On the other hand, I have business requirements that a) need solving and b) must scale to meet demand.

Wish me luck :)