[ http://jira.nhibernate.org/browse/NH-835?page=comments#action_14740 ]
Ayende Rahien commented on NH-835:
----------------------------------
Multi Query
This functionality allows you to execute several HQL queries in one round-trip against the database server. The common use case is executing a paged query but also wanting to get the count of total results, in a single round-trip.
Here is a simple example;
<code>
IMultiQuery multiQuery = s.CreateMultiQuery()
.Add(s.CreateQuery("from Item i where i.Id > ?")
.SetInt32(0, 50).SetFirstResult(10))
.Add(s.CreateQuery("select count(*) from Item i where i.Id > ?")
.SetInt32(0, 50));
IList results = multiQuery.List();
IList items = (IList)results[0];
long count = (long)((IList)results[1])[0];
</code>
The result is a list of query results, ordered according to the order of queries added to the mutli query.
Named parameters can be set on the multi query, and are shared among all the queries contained in the multi query, like this:
<code>
IList results = s.CreateMultiQuery()
.Add(s.CreateQuery("from Item i where i.Id > :id")
.SetFirstResult(10) )
.Add("select count(*) from Item i where i.Id > :id")
.SetInt32("id", 50)
.List();
IList items = (IList)results[0];
long count = (long)((IList)results[1])[0];
</code>
Positional parameters are not supported on the multi query, only on the seperated queries.
Note that, as shown above, if you do not need to configure the query seperatedly, you can simply pass the HQL directly to the Add() method.
Please note that this functionality works by concatenating the queries and sending the query to the database as a single string, this means that the database should support returning several result sets in a single query, at the moment, this functionality is only enabled for SQL Server.
Please note that this query is limited to the maximum amount of parameters supported by the database server, queries using <literal>in</literal> with large number of parameters may exceed this limit. For instance, SQL Server has a limit of 2,100 parameters per round-trip, and this query will throw an exception:
An interesting usage of this feature is to load several collections of an object in one round-trip:
<code>
Blog blog = s.CreateMutliQuery()
.Add("select b from Blog b left join fetch b.Users where b.Id = :id")
.Add("select b from Blog b left join fetch b.Posts where b.Id = :id")
.SetInt32("id", 123)
.UniqueResult<Blog>();
</code>
> Document MultiQuery
> --------------------
>
> Key: NH-835
> URL: http://jira.nhibernate.org/browse/NH-835
> Project: NHibernate
> Type: Improvement
> Components: Documentation
> Versions: 1.2.0.Beta2
> Reporter: Ayende Rahien
> Assignee: Ayende Rahien
> Priority: Trivial
> Fix For: 1.2.0.Beta3
>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
|
