How to use ROW_NUMBER() in HQL?

My databse is MS SQL. I want to use ROW_NUMBER() in HQL. Is it possible? How? I know about Custom Functions. But prefer a way that does no开发者_StackOverflow社区t need to modify web.config.

UPDATE:

My final goal is to delete all records in a table/entity except than last n records with HQL. I don't like to load all of them in memory then deleting them.

---

as of NH v2 the HQL supports delete and update statements via IQuery.ExecuteUpdate(). You could experiment using also the IQuery.SetMaxResults() and IQuery.SetFirstResult() (which use ROW_NUMBER()) to get the desired effect.

EDIT: i experimented my self, the SetMaxResults and SetFirstResult are ignored on sql generation for ExecuteUpdate() so this will not work.

Also @ the comment below, an HQL ExecuteUpdate (and at least for DELETE) does not necessarily mean an in-memory load of object state.

---

I would use plain SQL for this. Mainly because of "I don't like to load all of them in memory". This is not a very object oriented task, so you don't need an ORM.

Note: plain SQL means that the session cache gets broken. You don't get any troubles as long as you don't do other stuff within the same (non-stateless) session. If you do, I would choose the OO way and actually load the items into memory.

---

I would use a combination of HQL's ExecuteUpdate and Desc ordering, similar to:

delete Person p1 
where p1.Id in 
            (select p2.Id 
             from Person p2 
             where rownum <= 10
             order by p2.Id desc)