Querying with nHibernate where todays date is between publishDate and Expiry date

Question

I am trying to figure out how to best query in NHibernate so that the returned results are between for entries where todays time is >= PublishDateTime and <=ExpiryDateTime

The expiry date can be null so I need to allow for that. I found a couple of examples here and here but they seem to work in a different way and accept 2 values and compare to one DB field. I want the other way wrong really.

Query so far:

var query = _session.CreateCriteria<Message>()
                .AddOrder(Order.Desc("PublishedDateTime"))
                .List<Message>();
                return query;

Any suggestions would be greatly received!

Answer 1

Easiest Linq query:

return _session.Query<Message>()
               .Where(m => DateTime.Today >= m.PublishDateTime &&
                          (m.ExpiryDateTime == null ||
                           DateTime.Now <= m.ExpiryDateTime)
               .OrderByDescending(m => m.PublishDateTime)
               .ToList();

Criteria:

return _session.CreateCriteria<Message>()
               .Add(Restrictions.Le("PublishedDateTime", DateTime.Today) & 
                                    (Restrictions.IsNull("ExpiryDateTime") |
                                     Restrictions.Ge("ExpiryDateTime",
                                                     DateTime.Now)))
               .AddOrder(Order.Desc("PublishedDateTime"))
               .List<Message>();