NHIBERNATE line number exceeds partition C #

select * from
(
 select *, row_number() over (partition by questionaireId order by stepnumber desc) rid
 from myTable 
) t
where t.rid=1

SELECT TOP 1 Code, Name
, row_number() over (partition by questionaireId order by stepnumber desc) rid
 FROM myTable

var list = session
 .CreateCriteria ()
 .AddOrder(new Order("stepnumber", false)) // the property to be used for ORDER BY
 .Add(... where conditions ala ICriterion
 ... 
 .SetFirstResult(100) // skip 100 rows
 .SetMaxResults(20) // take 20 rows
 .List();

I am new to nhibernate and need your help.

How to execute this sql query in nhibernate-criteria C#

select * from
(
 select *, row_number() over (partition by questionaireId order by stepnumber desc) rid
 from myTable 
) t< br />where t.rid=1

First of all, a small note: selecting * will not work, all columns must be clearly named. Secondly, I am not sure What do you want to achieve. If this should do some kind of pagination, I have better suggestions (number 2 below). But, let’s try to do what you want first.

1) Therefore, considering that we should explicitly select columns, this is a way of how to convert SQL statements to Criteria syntax:

var list = session
. CreateCriteria() // mapped to'myTable' table 
 .SetProjection
 (
 Projections.SqlProjection
 (
 "Code, Name, row_number() over (partition by questionaireId order by stepnumber desc) as rid"
, new string[] {"Code", "Name"}
, new IType[] {NHibernate.NHibernateUtil.String
, NHibernate.NHibernateUtil.String }
 )
 )
 .SetMaxResults(1) // this is something like t.rid = 1
 .SetResultTransformer(Transformers.AliasToBean( ))
 .List()
;

If your mapping class MyEntity has attributes Code and Name… this will execute select as shown below:

SELECT TOP 1 Code, Name
, row_number() over (partition by questionaireId order by stepnumber desc) rid
 FROM myTable

And when executed, the result is limited to the attributes (code, name), and only the selection is made by your The first row of clause sorting.

2) Native NHibernate paging

Because you have already mentioned: I am a newbie to NHibernate, I guess you are trying to paging based on the Order By property If this is the case, your solution will be much simpler. Use this criterion:

var list = session
 .CreateCriteria() 
 .AddOrder(new Order("stepnumber", false)) // the property to be used for ORDER BY
 .Add(... where conditions ala ICriterion
 ...
 .SetFirstResult(100) // skip 100 rows
 .SetMaxResults(20) // take 20 rows
 .List();

< /p>