SetFirstResult() crashes in nHibernate C# for first page

I have a query that I build in C#. Assume this is the query - sb is a string builder - the query works fine in DB

 string queryString = @"   SELECT 
                                   *
                                   ,
                                    COUNT(*) OVER() AS TotalCount
                            FROM (" + sb.ToString() + @") AS subquery order by 
                             PlanTranDate "

For the first page when the following code is run on NHibernate :

query.SetResultTransformer(Transformers.AliasToBean<StorageActivityListVo>());
                    rvalue = query.SetFirstResult((page -1) * sizeToShowOnOnePage).SetMaxResults(sizeToShowOnOnePage).Future<abcList>().ToList();

It adds the Top(?) where there is Select * in the query and it crashes.Failed to execute multi query. Can Anyone tell what is it that I am doing wrong? shall It be just page and not (page -1 ) ?

Answer

Looks like you might be trying to achieve some kind of pagination but are trying to mixing raw SQL and library generated SQl, which won't work.

Just do it all in your SQL query and provide the relevant values when building it e.g.

var skip = (page - 1) * sizeToShowOnOnePage;
string queryString = @"SELECT *, COUNT(*) OVER() AS TotalCount
                        FROM (" + sb.ToString() + @") AS subquery 
                        ORDER BY PlanTranDate
                        OFFSET " + skip.ToString() + @" ROWS
                        FETCH NEXT " + sizeToShowOnOnePage.ToString() + @" ROWS ONLY";