NHhibernate – multiple queries in one roundtrip to server

Usually when we need to retrieve data from database server, we write code which looks like that

This code will execute two queries,each of them in separate roundtrip to the server.
This is the most popular way of getting data for multiple tables etc. However it is possible to enhance the code above and execute several queries in only one roundtrip to the database server. NHibernate provides us with three different mechanisms to accomplish this goal:

  • MultiQuery
  • First way to execute several queries at once is MultiQuery. In my opinion this is not very handy mechanism because of the fact, that we must use HQL. Here is an example of usage

    As You can see thanks to CreateMultiQuery method we are able to aggregate multiple queries. In order to get access to results, we have to execute code below

    Now we can get result of specific query using indexer

    If we don’t like this approach, the CreateMultiQuery method has an overload which allows to give “names” to queries. Thanks to this we can get result of query by its name.

  • MultiCriteria
  • Much better way of creating multiple queries is MultiCriteria. In this case we can use QueryOver API, so we don’t have to rely on HQL query strings.

    As You can see the usage of CreateMultiCriteria method is pretty consistent with CreateMultiQuery. Unfortunately we still have to retrieve data from specific query by index or by query name.

  • Future
  • My personal favorite for creating multi queries is Future. The usage is pretty straightforward

    As You can see, we can leverage QueryOver API and instead of calling List we call Future. NHibernate will aggregate our queries and will execute them once we start to iterate over one of them.

Despite the fact that all three mechanism has completely different syntax ,SQL created by all of them is the same and looks like that
multiple queries

NHhibernate – multiple queries in one roundtrip to server