setfirstresult & setmaxresult in child collection

Question

I have and entity lets call it Entity, and a Child collection Children. I have a screen where the user has the Entity information, and a list with the Children collection, but that collection can be get very big, so i was thinking about using paging: get the first 20 elements, and lazy load the next only if the user explicitly presses the next button.

So i created in the Entity Repository a function with this signature:

IEnumerable<Child> GetChildren(Entity entity, int actualPage, int numberOfRecordsPerPage)

I need to use the setfirstresult and setmaxresult, not in the Agregate root Entity, but in the child collection. But when i use those two configurations, they allways refer to the entity type of the HQL/Criteria query.

Other alternative would be to create a HQL/Criteria query for the Child type, set the max and first result, then filter the ones who are in the Entity Children collection (by using subquery). But i wasn't able to do this filter. If it was a bidirectional association (Child refering the parent Entity) it would be easier.

Any suggestions?

Any

Answer 1

One approach would be to create a query that returns results from both tables by doing a group by. This approach would allow you to apply paging on data that will come from the children collection and have a common factor (the entity's ID in each row) while you keep your starting point (the Entity object). What I mean is something like that:

public IList<object> GetData(int entityID, int actualPage, int numberOfRecordsPerPage)
        {
            ICriteria criteria = _repository.Session.CreateCriteria<FlowWhatIfProfile>("entity")
                .CreateCriteria("Children", "children", NHibernate.SqlCommand.JoinType.InnerJoin)
                .Add(Restrictions.Eq("children.EntityID", entityID));           

            ProjectionList pl = Projections.ProjectionList();
            pl.Add(Projections.GroupProperty("children.Id"));
            pl.Add(Projections.GroupProperty("children.Property1"));
            pl.Add(Projections.GroupProperty("children.Property2"));
            pl.Add(Projections.GroupProperty("children.Property2"));
            pl.Add(Projections.GroupProperty("entity.Id"));

            return criteria.SetProjection(pl)
                .SetFirstResult(actualPage * numberOfRecordsPerPage)
                .SetFetchSize(numberOfRecordsPerPage)
                .List<object>();
        }

The drawback would be that your returned data are a list of arrays (you will have to cast object to object[]) but you can overcome that by using the AliasToBean functionality that lest NHibernate project these arrays to strongly typed objects that you define.

Answer 2

it's simple with CreateFilter

session.CreateFilter(entity.children, "")
    .SetFirstResult(0)
    .SetMaxResults(20)
    .List();

http://knol.google.com/k/fabio-maulo/nhibernate-chapter-16/1nr4enxv3dpeq/19#