nhibernate Paging query to get the next set of results

Question

This is the problem i am trying to solve and i want to get the nhibernate linq query which will allow me to be able to do this. So I have a table called organization which has Name and an id.Say this has 10.

---

1. id=1 Name="D"
2. id=2 Name="B"
3. id=3 name="C"
4. id=4 name="E"
5. id=5 name="F"
6. id=6 name="G"
7. id=7 name="h"
8. id=8 name="i"
9. id=9 name="j"
10. id=10 name="k"

I want to do paging of the organizations as follows.

First time use I pass in index 0 and pageSize=2 and orderby="name" thus this will return orgs with **[id=2, name=B] and [id=3 name=C]. This is simple. Now second time round i want to get the next set 2 organizations which are after the 2nd element in the alphabet so in this case i expect to get orgs with id=1 and id= 4, so i pass in index=1,pagesize=2 and also LastElementId=3. can you help me in the query that will get me the orgs with id=1 and 4.For performance reasons I want to prevent having to get all 10 elements and doing foreach etc on these.

Answer 1

Not sure what the index variable is for, but it seems like you're looking for SQL that looks like this:

select
    top (2) *
from    
    [MyEntity]
order by
    [MyEntity].[Name] asc

(for the first query)

and this, for subsequent queries:

select
    top (2) *
from    
    [MyEntity]
where
    [MyEntity].[Name] > (
        select [LastEntity].[Name]
        from [MyEntity] as [LastEntity]
        where [LastEntity].[Id] = 3 --LastElementId
    )
order by
    [MyEntity].[Name] asc

With QueryOver, this could be written like this:

public IList<MyEntity> PerformQuery(
    ISession session,
    int pageSize,
    int? lastElementId)
{
    var query = session.QueryOver<MyEntity>();

    if (lastElementId.HasValue)
    {
        query.Where(
            Restrictions.GtProperty(
                Projections.Property<MyEntity>(e => e.Name),
                Projections.SubQuery(
                    QueryOver.Of<MyEntity>()
                        .Where(le => le.Id == lastElementId.Value)
                        .Select(le => le.Name))));

    }

    return query.OrderBy(e => e.Name).Asc
        .Take(pageSize)     
        .List<MyEntity>();
}