Getting count with NHibernate + Linq + Future

Question

I want to do paging with NHibernate when writing a Linq query. It's easy to do something like this:

return session.Query<Payment>()
    .OrderByDescending(payment => payment.Created)
    .Skip((page - 1)*pageSize)
    .Take(pageSize)
    .ToArray();

But with this I don't get any info about the total number of items. And if I just do a simple .Count(), that will generate a new call to the database.

I found this answer which solved it by using future. But it uses Criteria. How can I do this with Linq?

Answer 1

The difficulty with using Futures with LINQ is that operations like Count execute immediately.

As @vandalo found out, Count() after ToFuture() actually runs the Count in memory, which is bad.

The only way to get the count in a future LINQ query is to use GroupBy in an invariant field. A good choice would be something that is already part of your filters (like an "IsActive" property)

Here's an example assuming you have such a property in Payment:

//Create base query. Filters should be specified here.
var query = session.Query<Payment>().Where(x => x.IsActive == 1);
//Create a sorted, paged, future query,
//that will execute together with other statements
var futureResults = query.OrderByDescending(payment => payment.Created)
                         .Skip((page - 1) * pageSize)
                         .Take(pageSize)
                         .ToFuture();
//Create a Count future query based on the original one.
//The paged query will be sent to the server in the same roundtrip.
var futureCount = query.GroupBy(x => x.IsActive)
                       .Select(x => x.Count())
                       .ToFutureValue();
//Get the results.
var results = futureResults.ToArray();
var count = futureCount.Value;

Of course, the alternative is doing two roundtrips, which is not that bad anyway. You can still reuse the original IQueryable, which is useful when you want to do paging in a higher-level layer:

//Create base query. Filters should be specified here.
var query = session.Query<Payment>();
//Create a sorted, paged query,
var pagedQuery = query.OrderByDescending(payment => payment.Created)
                      .Skip((page - 1) * pageSize)
                      .Take(pageSize);
//Get the count from the original query
var count = query.Count();
//Get the results.
var results = pagedQuery.ToArray();

Update (2011-02-22): I wrote a blog post about this issue and a much better solution.