NHibernate Multi Criteria
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()
.Add(s.CreateCriteria(typeof(Customer)).SetMaxResults(15))
.Add(s.CreateCriteria(typeof(Customer)).SetProjection(Projections.RowCount()))
.List();
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.