Ayende @ Rahien

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


+972 52-548-6969

, @ Q c

Posts: 6,128 | Comments: 45,548

filter by tags archive

NHibernate Multi Criteria

time to read 6 min | 1061 words

A while ago I added Mutli Query to NHibernate, but I didn't have the time to add Multi Criteria capabilties in time for the 1.2 release. As it turn out, I managed to implement it about three weeks ago, and only now I had enough time to sit and document it.

Just as a reminder, the end goal is being able to use NHibernate to send this query in a single roundtrip to the database:

SELECT TOP 15 * FROM Customers;  SELECT COUNT(*) FROM Customers;

That is the canonical example for this feature. I think that it falls into the "optimization tricks" category, but I have run into several cases where the functionality was critical to satisfying the time constraints.

At any rate, now we can use this functionality with Criteria as well:

IList multiResults = s.CreateMultiCriteria()




IList customers = (IList)results[0];

IList counts = (IList)results[1];

int count = (int)counts[0];

This result in a single query being send to the DB, and multiply result sets returned to NHibernate.

A more complex scenario may be a page where you have to get several types of data:

  • Policy Types
  • Insurance Types
  • Paged Customers List
  • User personalizaztion info

Now, instead of hitting the database four times, you can package this into a single query, with four results sets being returned.

Caching is supported, but only for the entire mutli criteria query, so you can't cache just the policy types and hit the DB for everything else.

Have fun, and remember the rules of optimization:

  • Don't do it
  • For experts only: Don't do it, yet.


Pete w

I for one am very excited about this one

Ive come across a few anecdotal situations where performance was of the essence and I had to bypass NHibernate to gain this kind of control over my data layer.

Increasing the granularity of communication between clients and servers reduces overhead performance costs incurred by "chatty" servers. Normally, you wouldn't care very much about an extra roundtrip to the database, but when you are considering this kind of performance adjustment, you are probably cognizant of your situation.


Is there any way to get an IList from a search query in a multiCriteria? If not, we maybe able to add this capability if we modify add to take a typeparam like so .Add(myCriteria)

Ayende Rahien

Right now, no, there isn't.

Feel free to send me a patch :-)

Jeff Brown

Nice stuff!

Now what I still need is a way to perform bulk updates in-situ in the database without resorting to custom SQL or sprocs...

Ayende Rahien


that is probably on the horizon for NH 2.0.

In the mean time, you can use batch size to handle that.



Please can you tell me how can i implement following

select x.*, y.Count from

(select * from Users u where u.Id between 1 and 100) as x,

(select Count(*), b.Id from User b group by b.Id ) as y

where x.Id = y.Id


select x.*, y.Count from

(select * from Users u where u.Id between 1 and 100) as x

inner join

(select Count(b.Id), b.Id from User b group by b.Id ) as y

on x.Id = y.Id

in NHibernate 1.2 or any version of NHIbernate. using HQL or Create Criteria

Please do reply

Ayende Rahien

please ask in the forums, this is not really the place for such questions


thanks anyway....

Comment preview

Comments have been closed on this topic.


  1. The low level interview question - 7 hours from now
  2. The worker pattern - 3 days from now

There are posts all the way to May 30, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  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



Main feed Feed Stats
Comments feed   Comments Feed Stats