c# - Restrict QueryOver by child collection using nHibernate -

i trying parent entity entities in child collection in list.

for example:

public class parent {  public virtual int id {get;set;} public virtual list<child> children {get;set;}  }  public class child {  public virtual int id {get;set;} public virtual string name {get;set;}  } 

i've tried various combinations of joins , restrictions can't seem hit spot.

so please suggestions.

current example below:

 public ilist<lead> getallavailable(string[] names)     {          var result =  session.createcriteria<parent>()             .createcriteria("children")             .add(expression.in("name", names)).list<parent>();          return result;     } 

edit:

this sql equivilent:

select  *    dbo.parent         join ( select   p.id                    dbo.parent p                         join dbo.parenttochildren on p.id = dbo.parentstochildren.parent_id                         join dbo.child on dbo.parenttochildren.child_id = dbo.child.id                   name in ( 'foo', 'bar' )                group p.id                having   count(1) > 1              ) foo on dbo.parent.id = foo.id 

here's suggestion:

var parents = session.queryover<child>()   .whererestrictionon(x => x.name).isin(names)   .select(projections.group<child>(x => x.parent))   .where(restrictions.ge(projections.count<child>(x => x.parent), names.length))   .list<parent>(); 

the idea follows: find children have name 1 of names entries. group children parent. child need parent property mapped respective parent this, that's idea anyway. groups size equal (or greater than, shouldn't happen, replace ge eq) names.length, return parent; because if size of group equal names.length, names have been found assuming no 2 children of parent have same name.

the generated query:

select     this_.parent y0_     child this_     this_.name in (         /*  */     ) group     this_.parent having     count(this_.parent) >= /* names.length */; 

i've created test app returned promising results.

if need more parents, paging or fetching children, split problem sub query (note .fetch(x=>x.children).eager line not required, it's example can further query):

var parentsubquery =   queryover.of<child>()     .whererestrictionon(x => x.name).isin(names)     .select(projections.group<child>(x => x.parent))     .where(restrictions.ge(projections.count<child>(x => x.parent), names.length));  var parents = session.queryover<parent>()    .fetch(x=>x.children).eager // not necessary, example   .withsubquery.whereproperty(x => x.id).in(parentsubquery )   .list(); 

sql (without fetch):

select     this_.id id1_0_     parent this_     this_.id in (         select             this_0_.parent y0_                     child this_0_                     this_0_.name in (                 /* names */             )         group             this_0_.parent         having             count(this_0_.parent) >= /* names.length */     ); 

update:

if parent<->child many-to-many, things little bit trickier:

      parent parent = null;       var parentsubquery = queryover.of<child>()         .whererestrictionon(x => x.name).isin(names)         .joinqueryover(x => x.parents, () => parent)         .where(restrictions.ge(projections.count(() => parent.id), names.length))         .select(projections.group(() => parent.id));        var parents = session.queryover<parent>()         .withsubquery.whereproperty(x => x.id).in(parentsubquery)         .list(); 

the main difference instead of grouping direct parent property of child first needed join parents collection. reference each parent there introduce alias parent.

the generated sql pretty close original approach:

select     this_.id id2_0_     parent this_     this_.id in (         select             parent1_.id y0_                     child this_0_         inner join             childtoparent parents3_                 on this_0_.id=parents3_.childid         inner join             parent parent1_                 on parents3_.parentid=parent1_.id                     this_0_.name in (                 /* names */             )         group             parent1_.id         having             count(parent1_.id) >= /* names.length */     ); 

for test scenario works, you, too.