c# - Fetch max records of parent in one to many relationship -

c# - Fetch max records of parent in one to many relationship -

suppose 2 class there parentclass , childclass. parent having handbag have childs. have tried .setresulttransformer(new distinctrootentityresulttransformer()) , distinct() filters out repeatitions , when fetching .setmaxresults() not getting in parentclass level.

is there can used create .setmaxresults() work on parentclass level , not on childclass. need enforce maxresults in parent level.

example parentclass having 6 childs , setmaxresults(6) , distinct() result me single parentclass while looking more 5 parentclass records in query. , criteria includes 3 parameters match parent record , 2 match kid record

one solution utilize subquery. documentation 14.11. subqueries.

it work inner select. subquery contain clause 2 params match child, , projection homecoming parent.id. master query contain 3 params filter parent , phone call subquery match parent id.

the subquery:

var sub = detachedcriteria .for<child>() .add(restrictions.in("firschildproperty", new int[] {1, 2 })) // .add(restrictions.... // sec .setprojection(projections.property("parent.id")); // parent id select clause

the master query:

var criteria = session.createcriteria<parent>() .add(restrictions.in("firsparentproperty", new int[] {1, 2 })) // .add(restrictions.... // sec .add(restrictions.... // 3rd // no filter match children .add(subqueries.propertyin("id", sub)); // parent.id in (select // paging on parent table.... .setfirstresult(100) // skip rows .setmaxresults(20) // take 20 var result = criteria.list<parent>();

c# nhibernate