I am using nHibernate and trying to implement some paging. If I run this code
IList list = session.CreateQuery("FROM Author").List();
it returns 8 records. If I run this code though
IList list = session.CreateQuery("FROM Author")
.SetFirstResult(1).SetMaxResults(5).List();
it returns 0 records. When I look at the SQL generated I can't see that there is any paging logic.
What is the most likely errors with this?
You have the syntax correct, so as long as there is data in that table, I'm not sure why it would return 0 results.
As far as the generated SQL, MSSQL Server does not support the LIMIT and OFFSET commands so paging can't be implemented in that fashion on the server. (n.b. My understanding is that SQL Server 2005 has improved methods for paging, but still doesn't support LIMIT or OFFSET) So, with SQL 2000, all rows must be retrieved and then the subset selected from that result set.
The only other thing I can suggest is check that your query is within a separate transaction. I have seen posts that suggest this might help. There may be something to it because my unit tests with SetFirstResult/SetMaxResults are successful.
I'm not sure about NHibernate, but in Java the result index is 0-based. Try calling .SetFirstResult(0) instead of .SetFirstResult(1), otherwise if there is only one row, it will return 0 results. It sounds like you have 8 rows, though, so I don't know why you wouldn't get some results in any case.
Maybe check your Dialect and Connection Provider in web.config or app.config
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>