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";