Projections.RowCountInt64() returns 0 when i set SetFirstResult

Answer 1

First thing would be to use a sqlprofiler and check the sql that is getting executed. Maybe there's something wrong with that. I can't see something wrong in the criteria.

Answer 2

There are two queries are getting executed. But i couldnt figure it why rowCount returns 0. I didn't assign Order to this query, would it be a reason ?

Thanks

SELECT TOP 3 id22_0_, Name22_0_, LastName22_0_, Descript4_22_0_, Phone22_0_, Mobile22_0_,
    Email7_22_0_, Email8_22_0_, AddDate22_0_, Country22_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER
    BY __hibernate_sort_expr_0__) as row, query.id22_0_, query.Name22_0_, query.LastName22_0_,
    query.Descript4_22_0_, query.Phone22_0_, query.Mobile22_0_, query.Email7_22_0_,
    query.Email8_22_0_, query.AddDate22_0_, query.Country22_0_, query.__hibernate_sort_expr_0__ 
    FROM (SELECT this_.id as id22_0_, this_.Name as Name22_0_, this_.LastName as LastName22_0_, 
    this_.Description as Descript4_22_0_, this_.Phone as Phone22_0_, this_.Mobile as 
    Mobile22_0_, this_.Email1 as Email7_22_0_, this_.Email2 as Email8_22_0_, this_.AddDate as 
    AddDate22_0_, this_.Country as Country22_0_, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ 
    FROM Customer this_) query ) page WHERE page.row > 3 ORDER BY __hibernate_sort_expr_0__

SELECT TOP 3 y0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as
row, query.y0_, query.__hibernate_sort_expr_0__ FROM (SELECT count(*) as y0_, 
CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ FROM Customer this_) query ) page WHERE 
page.row > 3 ORDER BY __hibernate_sort_expr_0__

Answer 3

I have solved my problem. The thing is, there is a buggy staff with Nhibernate about CriteriaTransformer.TransformToRowCount. It returns int not long.

And what i did wrong above is, i was trying to get the row count of limited result set(the query on which SetFirstResult and SetMaxResult setted)

ISession session = NHibernateHttpModule.CurrentSession;

            var countCriteria = CriteriaTransformer.TransformToRowCount(query);
            rowCount = Convert.ToInt64(countCriteria.GetExecutableCriteria(session).UniqueResult());

            query.SetMaxResults(pageSize).SetFirstResult(pageIndex * pageSize);
            var customers = query.GetExecutableCriteria(session).List<Customer>();

This is the solution for my scenerio. I hope it helps someone who has the same issue.

Thaks