Slicing & Dicing Queries with NHibernate
This was how I started the day, interesting problem, and I like the solution:
DetachedCriteria getMaxScanDateForScan = DetachedCriteria.For<ScanResult>()
.SetProjection(Projections.Max("ScanDate"))
.Add(Property.ForName("ScanUri").EqProperty("scan.Id"));
DetachedCriteria hasResults = DetachedCriteria.For<ScanResult>()
.SetProjection(Projections.Id())
.Add(Property.ForName("ScanUri").EqProperty("scan.Id"));
criteria
.CreateCriteria("Results", "result", JoinType.LeftOuterJoin)
.SetProjection(
Projections.ProjectionList()
.Add(Projections.Property("scan.Id"), "Id")
.Add(Projections.Property("scan.Uri"), "Uri")
.Add(Projections.Property("scan.DaysNotice"), "DaysNotice")
.Add(Projections.Property("scan.EnableScan"), "EnableScan")
.Add(Projections.Property("result.ScanDate"), "LastScan")
.Add(Projections.Property("result.Status"), "Status")
.Add(Projections.Property("result.Message"), "Message")
)
.Add(Expression.Disjunction()
.Add(Subqueries.PropertyEq("result.ScanDate", getMaxScanDateForScan))
.Add(Subqueries.NotExists(hasResults))
);
IList list = criteria
.GetExecutableCriteria(session)
.SetResultTransformer(new AliasToBeanResultTransformer(typeof (ScanURIReport)))
.List();