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

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?

Answer 1

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.

Answer 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!

Answer 3

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

Answer 4

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.

Answer 5

Criteria API Method:

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