+972 52-548-6969
Slicing & Dicing Queries with NHibernate
time to read 1 min | 89 words
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();
Comments
Very intuitive....lol :)
What does the SetProjection call do as far as the TSQL generated?
Think about SetProjection as setting what will be in the SELECT clause.
intersting.. (still new to nhibernate) basically you're setting up the subqueries in seperate statements.?
I wish that such examples with explanation were part of the NHibernate Reference. Its section on Criteria Queries is quite short and covers only the basics :-(
-Markus
Where is the scan alias set and where is criteria created
craig,
ICriteria criteria = DetachedCriteria.For<ScanUrl>("scan");
Comment preview