title: SELECT MAX() with JOIN in NHibernate
body: <p>I have a Vendor.  Every Vendor has several Reservations, with a ReservationDate on it.</p>
  
  <p>I want a list of Vendors that have <strong>not</strong> made a reservation yet today.</p>
  
  <p>In SQL I would do something like this:</p>
  
  <pre><code>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) &lt;&gt; '2010-06-04'
  </code></pre>
  
  <p>I'm trying do it it in NHibernate like this:</p>
  
  <pre><code>session.CreateCriteria&lt;Vendor&gt;()
                      .CreateAlias("Reservations", "r")
                      .SetProjection(Projections.Alias(Projections.Max("r.ReservationDate"), "MaxDate"))
                      .Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)))
                      .List&lt;Vendor&gt;();
  </code></pre>
  
  <p>This clearly isn't working.  What am I doing wrong?</p>
  
  <p><strong>EDIT!</strong> I played around some more, and got to this point, which is working better:</p>
  
  <pre><code>var c = Session.CreateCriteria&lt;Vendor&gt;();
  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&lt;Vendor&gt;();
  </code></pre>
  
  <p>And to answer the comment, I'm getting the error "NHibernate.QueryException: could not resolve property: MaxDate of: Vendor"</p>