NHibernate
The object-relational mapper for .NET
NOTE: this post was originally published on June 14, 2008
One of my favorite NHibernate features must be the MultiCriteria/MultiQuery support. This basically allows you to execute a set of queries in one database call. In case you don't know, an ICriteria instance is basically a programmatic query. If you use MultiCriteria, you can batch either ICriteria or DetachedCriteria instances. And if you use MultiQuery, you can batch regular HQL strings, IQuery instances (which are in fact also HQL queries) or references to named queries. That gives you quite a few options to batch your queries. The only downside to using MultiCriteria and/or MultiQuery is that you have to retrieve the results with an index, based on the order in which you added the criteria or queries. Using index values in your code often reduces readability, so if i can i try to avoid using them. So i wrote a little QueryBatcher class which allows me to retrieve the results based on a key value. The class supports MultiCriteria as well as MultiQuery so you can use it to batch all kinds of queries. I didn't find a way to combine the batched criteria and the batched HQL queries in one database call, so if you mix criteria with hql queries, it will use one database call to execute all the criteria, and one database call to execute the hql queries.
And now i can write code like this:
[Test]
public void ReturnsProperResultsWhenUsingMultipleQueries()
{
queryBatcher.AddCriteria("Products", session.CreateCriteria(typeof(Product)));
queryBatcher.AddCriteria("Suppliers", DetachedCriteria.For<Supplier>());
queryBatcher.AddHqlQuery("ProductCategories", "from ProductCategory");
queryBatcher.AddHqlQuery("SomeProducts",
session.CreateQuery("from Product where Discontinued = :discontinued")
.SetBoolean("discontinued", false));
Assert.That(queryBatcher.GetEnumerableResult<Product>("Products").Count() > 0);
Assert.That(queryBatcher.GetEnumerableResult<Supplier>("Suppliers").Count() > 0);
Assert.That(queryBatcher.GetEnumerableResult<ProductCategory>("ProductCategories").Count() > 0);
Assert.That(queryBatcher.GetEnumerableResult<Product>("SomeProducts").Count() > 0);
}
Obviously, this is a very simple example with extremely simple queries... but you can of course batch very complex queries with this as well. You can also add queries that return scalar values so you could for instance do do something like queryBatcher.GetSingleResult<long>("TotalOutstandingAmount") and it would return that scalar value.
This is the code of the QueryBatcher class:
public class QueryBatcher : IQueryBatcher
{
private readonly Dictionary<string, int> criteriaResultPositions;
private readonly List<ICriteria> criteriaList;
private readonly List<IQuery> hqlQueryList;
private readonly Dictionary<string, int> queryResultPositions;
private readonly ISession session;
private IList criteriaResults;
private IList queryResults;
public QueryBatcher(IActiveSessionManager activeSessionManager)
{
session = activeSessionManager.GetActiveSession();
criteriaList = new List<ICriteria>();
hqlQueryList = new List<IQuery>();
criteriaResultPositions = new Dictionary<string, int>();
queryResultPositions = new Dictionary<string, int>();
}
public void AddCriteria(string key, ICriteria criteria)
{
criteriaList.Add(criteria);
criteriaResultPositions.Add(key, criteriaList.Count - 1);
}
public void AddCriteria(string key, DetachedCriteria detachedCriteria)
{
AddCriteria(key, detachedCriteria.GetExecutableCriteria(session));
}
public object GetResult(string key)
{
ExecuteQueriesIfNecessary();
object result = GetResultFromList(key, criteriaResults, criteriaResultPositions);
if (result != null) return result;
result = GetResultFromList(key, queryResults, queryResultPositions);
if (result != null) return result;
return null;
}
public IEnumerable<T> GetEnumerableResult<T>(string key)
{
var list = GetResult<IList>(key);
return list.Cast<T>();
}
public T GetSingleResult<T>(string key)
{
var result = GetResult<IList>(key);
return (T)result[0];
}
public void AddHqlQuery(string key, IQuery query)
{
hqlQueryList.Add(query);
queryResultPositions.Add(key, hqlQueryList.Count - 1);
}
public void AddHqlQuery(string key, string query)
{
AddHqlQuery(key, session.CreateQuery(query));
}
public void ExecuteQueriesIfNecessary()
{
ExecuteCriteriaIfNecessary();
ExecuteHqlIfNecessary();
}
private void ExecuteCriteriaIfNecessary()
{
if (criteriaList.Count > 0 && criteriaResults == null)
{
if (criteriaList.Count == 1)
{
criteriaResults = new ArrayList { criteriaList[0].List() };
}
else
{
var multiCriteria = session.CreateMultiCriteria();
criteriaList.ForEach(c => multiCriteria.Add(c));
criteriaResults = multiCriteria.List();
}
}
}
private void ExecuteHqlIfNecessary()
{
if (hqlQueryList.Count > 0 && queryResults == null)
{
if (hqlQueryList.Count == 1)
{
queryResults = new ArrayList { hqlQueryList[0].List() };
}
else
{
var multiQuery = session.CreateMultiQuery();
hqlQueryList.ForEach(q => multiQuery.Add(q));
queryResults = multiQuery.List();
}
}
}
private T GetResult<T>(string key)
{
return (T)GetResult(key);
}
private static object GetResultFromList(string key, IList list, IDictionary<string, int> positions)
{
if (positions.ContainsKey(key)) return list[positions[key]];
return null;
}
}