queryover - NHibernate filter collection by subcollection items -

health record may have symptom, consists of words. (er diagram.)

what need: given set of words return health records corresponding symptoms.

i have code:

public ienumerable<healthrecord> getbywords(ienumerable<word> words) {     var wordsids = words.select(w => w.id).tolist();     word word = null;     healthrecord hr = null;     isession session = nhibernatehelper.getsession();     {         return session.queryover<healthrecord>(() => hr)             .whererestrictionon(() => hr.symptom).isnotnull()             .inner.joinalias(() => hr.symptom.words, () => word)             .whererestrictionon(() => word.id).isin(wordsids)             .list();     } } 

what should use here is: inner select, i.e. subquery. can many-to-many maping, performance suffer.

the (easier, prefered) way not use many-to-many mapping. because explicitly mapped pairing object symptomword, querying more easier.

word word = null; symptom symptom = null;  // sub select returning column symptom.id  var subq = queryover.of<symptom>(() => symptom)    // symptoms refering searched words    .inner.joinalias(() => symptom.words, () => word)    .whererestrictionon(() => word.id).isin(wordsids)    // result of inner select    .select(s => symptom.id); 

and in next step can use filtering:

var list = session     // query on healthrecord here     .queryover<healthrecord>()     .withsubquery       // id of referenced symptom in table       .whereproperty(hr => hr.symptom.id)        // , filtered our subquery       .in(subq)     .list<helthrecord>();  return list; 

that should work, check similar issue here:

• query on hasmany reference
• nhibernate lazy loading limited earlier criteria

some hint how re-map many-to-many (because pairing table mapped object, can construct similar , simplified construct, resulting in better sql statement)

• nhibernate: how represent many-to-many relationships one-to-many relationships?