How to use ROW_NUMBER() in HQL?

Question

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 not 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.

What are you planning to do? There's always http://stackoverflow.com/questions/1998560/row-number-and-nhibernate-finding-an-items-page/2034389#2034389 , but we'd need to know your needs. — rebelliard, Apr 05 '11 at 12:12
That sounds like a strange goal - is this a once off? Is HQL really necessary or can you just use plain SQL (using Session.CreateSqlQuery)? — cbp, Apr 06 '11 at 05:15

Jaguar · Answer 1 · 2011-04-06T12:59:26.793

1

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.

edited Apr 06 '11 at 12:59

answered Apr 06 '11 at 07:44

Jaguar

5,929
34
48

1

This would require to load the objects into memory. – Stefan Steinegger Apr 06 '11 at 09:02
1

You are wrong! The hql is translated to sql and is executed to the db directly! – Jaguar Apr 06 '11 at 12:57
Yes, that's true, but there you don't have SetMaxResults available, as far as I know. To make use of SetMaxResults you need to get them into memory. That's what I actually meant, sorry if I wasn't clear. – Stefan Steinegger Apr 07 '11 at 10:28

score 0 · Accepted Answer · answered Apr 06 '11 at 08:41

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.

score 0 · Answer 3 · answered Apr 06 '11 at 09:28

0

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)

answered Apr 06 '11 at 09:28

rebelliard

9,592
6
47
80

`rownum` didn't work for me. I used SQL and `ROW_NUMBER()` by the way. Thanks. – Afshar Mohebi Apr 06 '11 at 11:35

How to use ROW_NUMBER() in HQL?

3 Answers3