Limiting NHibernate Entities Returned

Question

I'm trying to limit the number of entities that an NHibernate Criteria will return. The standard seems to be to use SetMaxResults, but this doesn't work properly if my Criteria involves a LEFT OUTER JOIN on a many-to-many relationship.

An example:

Dogs have owners (a many-to-many relationship), and I need to retrieve up to 10 dogs that belong to some set of owners. Doing

session.CreateCriteria<Dog>()
  .CreateAlias("Owners", "Owners")
  .Add(Restrictions.In("Owners.Id", idCollection)
  .SetMaxResults(10)
  .List<Dog>();

will translate into a SQL query like

SELECT TOP(10) * FROM DOGS d
  LEFT OUTER JOIN OWNERS_DOGS od ON d.id = od.id
  WHERE od.OWNER_ID IN (:valueone, :valuetwo)

My TOP restriction is happening too early, causing a dog with multiple owners that match the criteria to count multiple times towards my limit of 10. Is there a clean way to tell NHibernate that I only want it to hydrate the first X dog objects? I probably won't be able to get out of performing a full SELECT on the database, but it would be nice if I could avoid loading up every dog in the system, since I'm only going to be displaying 10 of them.

But aren't you restricting by the owner_id? Meaning dogs with multiple owners shouldn't be an issue (unless you allow duplicate rows in owners_dogs)? — dotjoe, Feb 05 '13 at 19:32
Thanks for pointing that out. My real-world scenario is a bit more complicated. I fixed up the example to be more applicable. — iddqd, Feb 05 '13 at 20:19

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

Solution in this case is a subquery. What we need is to create the inner SELECT, which will be filtered by dog owners and return the Dog ID. Then we will query Dogs, filter them by that subquery. Finally, our paging will be correct, while executed over the flat structure.

See this answer with more details: https://stackoverflow.com/a/14080092/1679310

Subquery 15.8. Detached queries and subqueries:

DetachedCriteria subQuery = DetachedCriteria.For(typeof(Dog))
    // WHERE conditions go here
    .SetProjection( Projections.Property("ID") )
;

The query with correct paging

session.CreateCriteria(typeof(Dog))
    .Add(Subqueries.PropertyEq("ID", subQuery));
    // PAGING goes here
    .SetMaxResults(10)
    .List();

edited May 23 '17 at 12:06

Community

1
1

answered Feb 06 '13 at 05:13

Radim Köhler

122,561
47
239
335

You're an awesome guy! Thanks for the answer. The only minor change I had to make was to use Subqueries.PropertyIn("ID", subQuery) vs PropertyEq, since the subquery itself returns multiple rows. – iddqd Feb 06 '13 at 14:32
great if this is working ;) Brilliant that you've adjusted it to your needs. good luck – Radim Köhler Feb 06 '13 at 15:04

Limiting NHibernate Entities Returned

1 Answers1