Getting index of a row in a list

Question

Hi,

I have a paging method that uses the criteria API's SetMaxResults and SetFirstResult. Functionally, it works fine.

What I'd like to do now is provide another method that retrieves the index of a given item within the set of all items, thus allowing me to calculate what page that item is in.

I am able to calculate the index correctly with the following SQL:

SELECT TOP 1
    ROW_NUMBER() OVER
    (
        ORDER BY PublicationTimestamp   -- this is the order the user has sorted by
    ) AS [Index]
FROM
    Articles
ORDER BY                                -- this is an order to get the relevant article at the top of the list and permit the top 1 clause
    CASE
        WHEN Id = 8604 THEN 0           -- this would be set to the ID of the article whose index is to be determined
        ELSE 1
    END

However, I'm having trouble translating this into either HQL or the criteria API.

Is there an accepted way of achieving this with the criteria API or with HQL?

For reference, my paging method looks like this:

var multiCriteria = session.CreateMultiCriteria();

// this criteria finds all distinct article IDs that match any supplied filter
var idsDetachedCriteria = DetachedCriteria.For<Article>()
    .SetProjection(Projections.Distinct(Projections.Property("Id")));

// this criteria counts the total number of articles that match the filter
var countQuery = session.CreateCriteria<Article>()
    .SetProjection(Projections.Count("Id"))
    .Add(Subqueries.PropertyIn("Id", idsDetachedCriteria));

// this criteria gets a specific page of articles that match the filter
var listQuery = session.CreateCriteria<Article>()
    .Add(Subqueries.PropertyIn("Id", idsDetachedCriteria))
    .SetMaxResults(maximumResults)
    .SetFirstResult(resultPage * maximumResults)
    .AddOrder(new Order(GetPropertyForSortBy(sortBy), sortOrder == SortOrder.Ascending));

if (filter != null)
{
    // this applies the filter to the IDs detached criteria
    var criteriaNodeVisitor = new CriteriaNodeVisitor(idsDetachedCriteria);
    filter.Expression.Visit(criteriaNodeVisitor);
}

multiCriteria.Add(countQuery);
multiCriteria.Add(listQuery);
var results = multiCriteria.List();

return new ArticleListPage((int)((IList)results[0])[0], ((IList)results[1]).Cast<Article>());

Thanks,
Kent