NHibernate: SELECT MAX() with JOIN

Question

I have a Vendor. Every Vendor has several Reservations, with a ReservationDate on it.

I want a list of Vendors that have not made a reservation yet today.

In SQL I would do something like this:

SELECT v.Id, MAX(r.ReservationDate) AS MaxDate FROM Vendor v 
INNER JOIN DailyReservation r ON v.Id = r.Vendor_Id
GROUP BY v.Id
HAVING MAX(r.ReservationDate) <> '2010-06-04'

I'm trying do it it in NHibernate like this:

session.CreateCriteria<Vendor>()
                    .CreateAlias("Reservations", "r")
                    .SetProjection(Projections.Alias(Projections.Max("r.ReservationDate"), "MaxDate"))
                    .Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)))
                    .List<Vendor>();

This clearly isn't working. What am I doing wrong?

EDIT! I played around some more, and got to this point, which is working better:

var c = Session.CreateCriteria<Vendor>();
c.CreateAlias("Reservations", "r");

ProjectionList projections = Projections.ProjectionList();
projections.Add(Projections.Max("r.ReservationDate"), "MaxDate");
projections.Add(Projections.GroupProperty("Id"));
c.SetProjection(projections);
c.Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)));

return c.List<Vendor>();

And to answer the comment, I'm getting the error "NHibernate.QueryException: could not resolve property: MaxDate of: Vendor"

Answer 1

As Mauricio mentioned, HQL is a better fit for this problem.

Here's your query:

session.CreateQuery(
  @"
  SELECT v.Id, MAX(r.ReservationDate)
  FROM Vendor v
  JOIN v.Reservations r
  GROUP BY v.Id
  HAVING MAX(r.ReservationDate) <> :MaxDate
  ")
  .SetParameter("MaxDate", DateTime.Today)
  .List();

As you see, it's not much different from the SQL, except for the fact that NH already knows about the relationships, so you don't have to specify the field for the Join.

One thing to consider, though, is that this query does not return a List of Vendor. It returns a List of object[], where each row contains two elements: the Id and the MAX projection.