Hello all,

I'm using the ICriteria API to perform custom paging for an ASP.Net GridView. So, I can specify that I'd like to have entities 31-40 out of a result set for which I can dynamically specify sort order and additional filters.

Now my problem is, that I have links from other parts of my app that link to a specific entity. Given two specific sort orders or set of filters, an entity will show up on different pages in the grid.

I'm looking for a way to determine the position of an entity in a given result set. However, loading this result set into memory is out of question, so I need NHibernate to return a Scalar.

NHibernate aside thinking in pure SQL (bottom up approach), I have problems coming up with a feasible solution. I'm using SQL 2005, which has the ROW_NUMBER() feature but so far I couldn't find a way how to put it to good use (I don't even want to think about yet how I could make NHibernate to submit ROW_NUMBER() statements).

Does anybody have an idea how I can solve this problem?

Thanks, Christoph

I found a way, even though it's kind of complicated. I'm building on the assumption that the result set I'm looking through has a deterministic order (so e.g. if I'm ordering by a non-distict value with a second-level criteria I need to order by ID, timestamp, or so)

Now I need to construct a query that returns all entries that show up before the selected entry plus the selected entity itself. About those entities I know that either:

-Their values in the Order By column must be less than the value in the Order By column of my selected entity.
-If their value in the Order By column is the same as the one of the selected entity, then the second-level Order By criteria (e.g. ID) must be less or equal.

On top of that I simply put a RowCount projection so that I get the number of entries instead of the data.

Still, I'm interested whether there is an easier solution for this (I think) frequently occurring problem.

Thanks, Christoph