1

How can I do something like this in nHibernate:

select count(*)
from (subquery)

It is a rather simple query in SQL, but the solution is not so obvious in nHibernate. An obvious solution would be something along the line of:

    var rowcount = Session.QueryOver<Entity>()
       .Select(Projections.Alias(Projections.Count(Projections.SubQuery(detachedQuery)), "count"))
        .FutureValue<int>();

However, this results in an ArgumentOutOfRangeException:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

This SO answer doesn't work for me, as I have a more complex grouping. My question originates from an earlier question where I tried to use ToRowCountQuery, but that function strips groupings form the query.

Community
  • 1
  • 1
Pieter
  • 3,339
  • 5
  • 30
  • 63

2 Answers2

0

Once you have a session, you could 

var criteria = session.CreateCriteria(....)

int count = (int) criteria.UniqueResult();
VoodooChild
  • 9,776
  • 8
  • 66
  • 99
  • See http://stackoverflow.com/questions/116188/how-do-i-select-the-count-of-an-nhibernate-subquerys-results for more info. It is couple of years old, but hope it helps. – VoodooChild Nov 08 '11 at 08:26
  • Hmmm, I'm not sure how that would work. Where do I put my subquery? The related SO answer doesn't use a subquery, but just provides an alternative solution, which also works in that particular case. The Criteria-solution seems to be similar to my QueryOver solution which results in an exception. Next to that, I would prefer a solution using QueryOver. – Pieter Nov 08 '11 at 08:32
0

I found an old post by Ayende which gave me a solution (Counting paged data).

I created my own dialect as described in that post and added the rowcount function to my paged query. And presto, I got my rowcount in a single query to the database.

Pieter
  • 3,339
  • 5
  • 30
  • 63