CODEWITHSUNDEEP
c#nhibernate
Vinay
Vinay

Reputation: 471

Fetch max records of parent in one to many relationship

Suppose two class are there a ParentClass and a ChildClass. Parent is having a bag to have childs.
I have tried .SetResultTransformer(new DistinctRootEntityResultTransformer()) and distinct() which filters out the repeatitions and when fetching .SetMaxResults() I am not getting it in ParentClass level.

Is there anything which can be used to get make the .SetMaxResults() to work on ParentClass level and not on ChildClass. I need to enforce the maxresults in Parent level.

Example ParentClass having 6 childs and setmaxresults(6) and distinct() would result me to a single ParentClass while I am looking for more 5 ParentClass records in my query. And my criteria includes 3 parameters to match with Parent record and 2 to match with Child record

Upvotes: 1

Views: 183

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123901

One solution could be to use the subquery. The documentation 14.11. Subqueries.

It will work like an inner select. The subquery will contain WHERE clause with 2 params to match Child, and a projection to return Parent.ID. The master query will then contain 3 params to filter Parent and also a call to subquery to match the Parent ID.

The subquery:

var sub = DetachedCriteria
 .For<Child>()
 .Add(Restrictions.In("FirsChildProperty", new int[] {1, 2 })) // WHERE
 .Add(Restrictions.... // Second
 .SetProjection(Projections.Property("Parent.ID")); // Parent ID as a SELECT clause

The master query:

var criteria = session.CreateCriteria<Parent>()
 .Add(Restrictions.In("FirsParentProperty", new int[] {1, 2 })) // WHERE
 .Add(Restrictions.... // the second
 .Add(Restrictions.... // the third
 // no filter to match children
 .Add(Subqueries.PropertyIn("ID", sub)); // Parent.ID in (select
 // now paging just over Parent table....
 .SetFirstResult(100) // skip some rows
 .SetMaxResults(20)   // take 20

var result = criteria.List<Parent>();

Upvotes: 0

Related Questions