NHibernate HQL's Equivalent to T-SQL's TOP Keyword

Question

What is NHibernate HQL's Equivalent to T-SQL's TOP Keyword?

Also what is the non-HQL way for saying give me the first 15 of a class?

score 33 · Accepted Answer · answered Feb 16 '09 at 23:45

33

It's actually pretty easy in HQL:

var top15 = session.CreateQuery("from SomeEntity")
                .SetFirstResult(0)
                .SetMaxResults(15)
                .List<SomeEntity>();

Don't know how to do this using the criteria API though.

answered Feb 16 '09 at 23:45

mookid8000

18,258
2
39
63

+1. Huh that's pretty weird. I thought the answer would be part of the HQL "tunneled" string. Interesting. – BuddyJoe Feb 16 '09 at 23:50
3

This answer is actually a hybrid of HQL and the criteria API – Ben Laan Jun 28 '09 at 04:59
yea..it's essentially the same as criteria...just replace with `CreateCriteria()` – dotjoe Mar 04 '10 at 01:19
1

any idea how to order the results before selecting the top 15? – kmehta Apr 06 '10 at 15:03
sure - `session.CreateQuery("from SomeEntity s order by s.SomeProperty").SetFirst(....)` – mookid8000 Mar 18 '11 at 19:03

score 10 · Answer 2 · answered Feb 16 '09 at 23:48

10

Criteria API Method:

ICriteria criteria = DaoSession.CreateCriteria(typeof(T));
criteria.SetFirstResult(StartIndex);
criteria.SetMaxResults(MaximumObjects);
return criteria.List<T>();

answered Feb 16 '09 at 23:48

1

MaximumObjects is just an integer variable to tell SetMaxResults how many objects to return. In your case, you could hard code 15 instead, i.e. criteria.SetMaxResults(15); – Feb 17 '09 at 01:13
Haha... so in other words it is identical to CreateQuery() after the instance is created. – Andrew Burns Feb 19 '09 at 15:47
You can chain all of those ICriteria method calls fluently, would make it more readable. – UpTheCreek Apr 19 '11 at 08:36

score 4 · Answer 3 · answered Apr 09 '15 at 12:29

From NHibernate 3.2 you could use SKIP n / TAKE n in hql at the end of the query. It could be very helpful in subqueries where you can not use SetMaxResults.

For example:

select l, (select u from User u where u.Location = l order by u.Date asc take 1) 
from Location l

score 0 · Answer 4 · answered Dec 05 '12 at 20:53

0

For completeness, here is how to do it with the QueryOver API introduced in NHibernate 3.0:

var top15 = session.QueryOver<SomeEntity>().Take(15).List();

Throw in a .Skip(someInt) if you need to define a start index, e.g. for paging.

answered Dec 05 '12 at 20:53

Jørn Schou-Rode

37,718
15
88
122

score -2 · Answer 5 · answered Jun 14 '13 at 15:17

-2

mookid8000 is giving false information.

there is no way of setting SQL TOP N with HQL :(

it always downloads all of the table to .NET and the takes the TOP, wich is just plain stupid!

answered Jun 14 '13 at 15:17

Andrew Rebane

199
3
12

Worked just like it was advertised. Information is not false. – Pale Ale Mar 05 '19 at 19:11
I was just testing and filtering is not happening on.Net end. – Sudhakar Chavali Apr 30 '19 at 19:35

NHibernate HQL's Equivalent to T-SQL's TOP Keyword

5 Answers5

Linked