2

I'm having an issue currently where I have to apply sorting twice in a query.

Since I want paginated results, I'm using a DetachedCriteria approach and using it as a Subquery for the main Criteria.

Issue is, that if I apply the sorting only on the DetachedCriteria, I don't get back the results sorted.

This is because the sql "IN" clause does not guarantee that the results will come back sorted.

So I need to apply the same sorting again on the main Criteria.

var detachedCriteria = DetachedCriteria.For<User>()
     .SetProjection(Projections.Id())
     .setMaxResults(20).setFirstResult(0)
     .AddOrder(Order.Asc("Name"));

 session.CreateCriteria<User>
    .Add(Subqueries.PropertyIn("Id", detachedCriteria))
    .AddOrder(Order.Asc("Name"))
    .List();

This would work, if could instead apply the DetachedCriteria as a JOIN clause, but as far as I know this is not supported by NHibernate.

Does anybody know of a different approach?

José Carvalho
  • 41
  • 2
  • No, it needs to be applied on the sub query. Reason is, imagine you join root query with another table, you get more rows per result (on a 1-many relation). If you apply pagination on this, you will lose results. – José Carvalho Oct 07 '15 at 09:58
  • What prevents you from paginating on the main criteria itself? – Bredstik Oct 07 '15 at 14:21
  • @Bredstik Because if I apply any joins on the main criteria, I will lose results with pagination. That's why the pagination needs to be applied on the detached criteria, so I get only the unique id's paginated. – José Carvalho Oct 09 '15 at 05:54
  • I get it. In that case, I am not aware of a different approach. – Bredstik Oct 09 '15 at 13:23
  • I'm guessing your user entity participates in some one-to-many relationships like books or courses etc. Just make absolutely certain you're applying a total order over all possible results before you paginate. For example, get page 1 size 20 of 150 users in the catalog sorted by name ascending; when the user requests page 2, all should be unseen and later in the alphabet, right? Some (most?) databases will not let you sort by another field after you project down to just the ID. – chrisinmtown Apr 02 '18 at 21:05

0 Answers0