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: 6,125 | Comments: 45,488

filter by tags archive

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

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

  1. The design of RavenDB 4.0: Physically segregating collections - 14 hours from now
  2. RavenDB 3.5 Whirlwind tour: I need to be free to explore my data - about one day from now
  3. RavenDB 3.5 whirl wind tour: I'll have the 3+1 goodies to go, please - 5 days from now
  4. The design of RavenDB 4.0: Voron has a one track mind - 6 days from now
  5. RavenDB 3.5 whirl wind tour: Digging deep into the internals - 7 days from now

And 12 more posts are pending...

There are posts all the way to May 30, 2016

RECENT SERIES

  1. The design of RavenDB 4.0 (14):
    03 May 2016 - Making Lucene reliable
  2. RavenDB 3.5 whirl wind tour (14):
    04 May 2016 - I’ll find who is taking my I/O bandwidth and they SHALL pay
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats