Lesser-Known NHibernate Features: Future Queries

Sometimes it makes sense to send several queries at the same time to the database server. This prevents multiple roundtrips and normally can provide results faster. Knowing this, NHibernate offers two features: multiqueries and future queries. Future queries build upon multiqueries and because they use LINQ, which is probably the most common querying API in NHibernate, I will focus in this.

In order to use future queries, you queue them, by calling the ToFuture() or ToFutureValue() extension methods on any number of queries that you wish to retrieve together:

   1: var futureProducts = session.Query<Product>().Where(p => p.Price > 1000)
.ToFuture();
   2:  
   3: var futureCustomers = session.Query<Customer>().Where(c => c.Orders.Any()).ToFuture();
   4:  
   5: //no query is sent to the database

Only after you actually access its results are the queries sent:

   1: var productsCount = futureProducts.Count();

And the generated SQL is:

   1: select
   2:     product0_.product_id as product1_6_,
   3:     product0_.name as name6_,
   4:     product0_.price as price6_
   5: from
   6:     product product0_
   7: where
   8:     product0_.price>@p0;
   9: select
  10:     customer0_.customer_id as customer1_3_,
  11:     customer0_.name as name3_,
  12:     customer0_.email as email3_
  13: from
  14:     customer customer0_
  15: where
  16:     exists (
  17:         select
  18:             orders1_.order_id
  19:         from
  20:             [
  21:         order] orders1_ where
  22:             customer0_.customer_id=orders1_.customer_id
  23:     );
  24: ;
  25: @p0 = 1000 [Type: Decimal (0)]

Notice how the two queries are sent at the same time, separated by a semicolon.

It also works for single values, they can be combined with multi value queries:

   1: var mostExpensiveFutureProduct = session.Query<Product>()
.OrderByDescending(x => x.Price).Take(1).ToFutureValue();

The caveat is that this, like multiqueries, is right now only supported in SQL Server, Oracle and MySQL. In all the other cases the results will be retrieved immediately.





                             

6 Comments

  • What you have specified is nice but one thing is missing, when executing the last query (var productsCount = futureProducts.Count();) this actually will result in two roundtrips to the database: the first one is the sql you specified in the blog where the first two queries: futreProducts and futureCustomers are sent in one shot, and the second trip is for the Count query, and that is because Count query doesnt support the nhiberntae future.

    So it would be nice if all the 3 queries where sent in one roundtrip to the database, I have stumpled upon this before and found this fantastic solution, an extension method to support query execution on non supported nhibernate linq extensions (e.g Count()), here is a link to it:
    http://sessionfactory.blogspot.ae/2011/02/getting-row-count-with-future-linq.html

  • Hi,
    You're right about Count(), it wasn't a good example. I will update the post, thanks!

  • No, you are wrong! My fault! :-)
    Count is not called using "LINQ to NHibernate", but "LINQ to Objects". This works perfectly!

  • -reCAPTCHA not working
    -spend more time on blog(only 2 paras ) and less on wierd styling that u gave to the code

  • how that batch select will run. it will run sequentially or parally. when we have single query with multiple selects while using futures

  • @testuser: sequentially!

Add a Comment

As it will appear on the website

Not displayed

Your website