nhibernate - Finding the row with matching relations using HQL -

i using castle activerecord , nhibernate.

i have instance class has many-to-many relationship component class. find instance related specific set of components. possible in hql (or else in nhibernate)?

the linq version of function be:

public instance find(ienumerable<component> passed_components) {     return instance.queryable.single(i => passed_components.all(x => i.components.contains(x))); } 

of course nhibernate linq implementation can't handle this.

i can write hql 1 of components:

instance.findone(new detachedquery("from instance :comp in elements(i.components)").setparameter("comp", passed_components.first())); 

but looks in compares 1 item set, can't compare set set.

edit:

this best do:

iqueryable<instance> q = queryable; foreach(var c in components) {     q = q.where(i => i.components.contains(c)); } 

but inefficient. adds subselect sql query every clause. unnecissarily long subselect @ that. joins instance table, instance/component join table, , component table. needs instance/component join table.

because of nature of data, going implement hybrid solution. narrow down instances in query, use linq objects correct 1 if necessary. code looks this:

iqueryable<instance> q = queryable; foreach(var c in components.take(2)) {     q = q.where(i => i.components.contains(c)); }  var result = q.toarray(); if(result.length > 1) {     return result.singleordefault(i => !components.except(i.components).any()); } else return result.firstordefault(); 

anyone have better way?

using nhibernate.linq provider, following should work:

var passed_components = new list<component>(); var instance = session.linq<instance>()                       .where(i => !passed_components.except(i.components).any())                       .singleordefault(); 

you can download provider here , read more here , here.