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?

Fun

Use Session.QueryOver instead - it saves intellisense and 'compileability', and there is a SelectCount method. If you need I can provide detail example of using it – Rausch

Yes please, QueryOver seems to work well too! – Fun

(I spotted an error in my solution and deleted it to avoid confusion. I'll post the fixed version shortly) – Spinster

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.

Spinster

the extension method in your blog post is a very nice solution – Shorthand

Answer 2

The following blog post has an implementation of ToFutureValue that works with LINQ.

http://sessionfactory.blogspot.com.br/2011/02/getting-row-count-with-future-linq.html

It has a small error on the following line that must be changed from this.

var provider = (NhQueryProvider)source.Provider;

To this:

var provider = (INhQueryProvider)source.Provider;

After apply the change you can use que queries in this way:

var query = session.Query<Foo>();
var futureCount = query.ToFutureValue(x => x.Count());
var page = query.Skip(pageIndex * pageSize).Take(pageSize).ToFuture();

Cohette

Answer 3

var query = Session.QueryOver<Payment>()
    .OrderByDescending(payment => payment.Created)
    .Skip((page -1 ) * pageSize)
    .Take(pageSize)

This is something I just discovered that the Linq to NH handles just fine, the ToRowCountQuery removes take/skip from the query and does a future row count.

var rowCount = query.ToRowCountQuery().FutureValue<int>();

var result = query.Future();

var asArray = result.ToArray();
var count = rowCount.Value();

Carport

Works well with SQL Server, but not with SQL Server Compact Edition... :( System.Data.SqlServerCe.SqlCeException : There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = SELECT ] – Fun

The question specifically asked for a linq solution, not QueryOver. – Tomkins

Strangely, ToRowCountQuery seems to remove groupings... #8013466 – Beguin

Answer 4

Ok, it seems it should be working in your case, but I not tested:

return session.QueryOver<Payment>()
  .Skip((page - 1) * pageSize)
  .Take(pageSize)
  .SelectList(r => r.SelectCount(f => f.Id))
  .List<object[]>().First();

Test first before upvoting ;)

UPD: sorry, as I understand you now, you need to get Count of all items. Then you need to run the query without paging:

return session.QueryOver<Payment>()
  .SelectList(r => r.SelectCount(f => f.Id))
  .List<object[]>().First();

Rausch

Neither of those queries will do what is required. Get the paged set of object + get a count of the total number of objects that meet the criteria. – Ingrain

I think it is clearly mentioned in the question to use NHibernate + Linq instead of QueryOver. – Scever