Limiting number of results when using NHibernate

Question

I was wondering how people usually go about limiting the number of entities returned from a query. I know about SetMaxResults but this seems to break down as soon as joins are involved - 3 entities with each 2 children will produce 6 rows as a result. If I use SetMaxResults(3) to get only 3 entities I will only get 3 resulting rows which will probably give me an incomplete object graph.

I am aware this is due to the DistinctRootEntityResultTransformer, but I'm wondering what other people do to avoid getting 1000 rows just to get the 6 rows necessary to build my 3 entities.

Answer 1

My answer may not be 100% accurate as I'm a Java Hibernate type of guy, but I'm guessing that NHibernate has the exact same design as it's java cousin.

Hibernate promises that the number of items in the result of a query will be exactly equal to the number of rows returned from the query. This is all fine as normally the query returns one row for each entity, the problem comes in when the query joins to another table and produces the cartesian product.

Couple of options:

1. Remove any 'join' criteria/clauses from your query. This maybe easy if they where just included for performance, note they can be replaced with 'fetch' clauses that won't create a join
2. Add sql restrictions to the criteria that create a sub query in the where clause of the final sql

Note this isn't particalar to hibernate or nhibernate, pagign a joined result set is just plain impossiable (with out brute force and ignorance)

Hope this helps

Answer 2

Could you post your mapping files and the query you're using? I'm not sure I understand the question exactly, or how the DistinctRootEntityResultTransformer comes into play, but if your entity includes a collection of other entities, or is part of a joined-subclass relationship, (or other relationships?) NHibernate will do joins for you in order to populate the entity (even if you don't have explicit joins in your query).

You can stop hibernate from doing various joins by setting the lazy property on the specific mapping. It will only fetch the data if you end up using it.