Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,953 | Comments: 44,408

filter by tags archive

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

Very intuitive....lol :)

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

Ayende Rahien

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

josh

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

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

Where is the scan alias set and where is criteria created

Ayende Rahien

craig,

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

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats