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: 10 | Comments: 37

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. Production postmortem: The case of the memory eater and high load - 6 hours from now
  2. Production postmortem: The case of the lying configuration file - about one day from now
  3. Production postmortem: The industry at large - 2 days from now
  4. The insidious cost of allocations - 3 days from now
  5. Find the bug: The concurrent memory buster - 4 days from now

And 4 more posts are pending...

There are posts all the way to Sep 10, 2015

RECENT SERIES

  1. Find the bug (5):
    20 Apr 2011 - Why do I get a Null Reference Exception?
  2. Production postmortem (10):
    14 Aug 2015 - The case of the man in the middle
  3. What is new in RavenDB 3.5 (7):
    12 Aug 2015 - Monitoring support
  4. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats