Ayende @ Rahien

Refunds available at head office

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();

Comments

Steve
08/30/2007 02:42 PM by
Steve

Very intuitive....lol :)

What does the SetProjection call do as far as the TSQL generated?

Ayende Rahien
08/30/2007 05:59 PM by
Ayende Rahien

Think about SetProjection as setting what will be in the SELECT clause.

josh
08/30/2007 07:49 PM by
josh

intersting.. (still new to nhibernate) basically you're setting up the subqueries in seperate statements.?

Markus Zywitza
08/31/2007 06:44 AM by
Markus Zywitza

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

Craig Neuwirt
08/31/2007 01:59 PM by
Craig Neuwirt

Where is the scan alias set and where is criteria created

Ayende Rahien
09/02/2007 11:25 AM by
Ayende Rahien

craig,

ICriteria criteria = DetachedCriteria.For("scan");

Comments have been closed on this topic.