nhibernate - How to fetch entities where children match a certain condition in a many-to-many relationship? -

nhibernate - How to fetch entities where children match a certain condition in a many-to-many relationship? -

i have used nhibernate quite time still struggle "simple" stuff. trying work many-to-many relationship between entity serviceprovider , features.

basically every service_providers can have different features must nowadays in table features.

these mapping files:

serviceproviders.hbm.xml,

<class name="app.domain.serviceprovider, app.domain" table="serviceproviders"> <id name="code" type="system.guid" unsaved-value="00000000-0000-0000-0000-000000000000"> <column name="serviceprovidercode" /> <generator class="guid.comb" /> </id> <property name="description" type="ansistring"> <column name="description" length="150" not-null="true" /> </property> <set name="features" table="serviceprovidersfeatures" access="field.pascalcase-underscore" cascade="save-update" optimistic-lock="false"> <key column="serviceprovidercode"></key> <many-to-many class="app.domain.feature" column="featurecode" not-found="exception" /> </set> </class>

features,

<class name="app.domain.feature, app.domain" table="features"> <id name="code" type="system.guid" unsaved-value="00000000-0000-0000-0000-000000000000"> <column name="featurecode" /> <generator class="guid.comb" /> </id> <property name="description" type="ansistring"> <column name="description" length="150" not-null="true" /> </property> <set name="serviceproviders" table="serviceprovidersfeatures" cascade="none" inverse="true" lazy="true" access="field.pascalcase-underscore" optimistic-lock="false" mutable="false"> <key column="featurecode"></key> <many-to-many class="app.domain.serviceprovider" column="serviceprovidercode" not-found="ignore" /> </set> </class>

and these 2 main classes:

serviceprovider.cs

public class serviceprovider { public serviceprovider() { this._features = new hashset<feature>(); } public virtual guid code { get; protected set; } public virtual string description { get; set; } private icollection<feature> _features = null; public virtual readonlycollection<feature> features { { homecoming (new list<feature>(_features).asreadonly()); } } }

feature.cs

public class feature { public feature() { this._serviceproviders = new hashset<serviceprovider>(); } public virtual guid code { get; protected set; } public virtual string description { get; set; } private icollection<serviceprovider> _serviceproviders = null; public virtual readonlycollection<serviceprovider> serviceproviders { { homecoming (new list<serviceprovider>(_serviceproviders).asreadonly()); } } }

what trying fetch services-providers (with features) description starts string, , have @ to the lowest degree 1 feature specified (param).

i reckon need subquery don't know how build queryover. should this:

var serviceproviders = session.queryover<app.domain.serviceprovider>() .inner.joinalias(x => x.features, () => features) .whererestrictionon(f => f.description).islike("%" + "test" + "%") .andrestrictionon(() => features.code).isin(<subquery>) .list();

there couple of things alter in snippet. first, don't need specify % in islike method: nhibernate automatically. second, can build subquery in next manner:

var subquery = session.queryover<app.domain.feature>() .whererestrictionon(f => f.description).islike("test", matchmode.anywhere) .select(f => f.code);

you can plug in main query:

var serviceproviders = session.queryover<app.domain.serviceprovider>() .withsubquery.whereproperty(s => s.code).in(subquery) .list();

or can try:

var serviceproviders = session.queryover<app.domain.serviceprovider>() .joinqueryover<app.domain.feature>(s => s.features) .whererestrictionon(f => f.description).islike("test", matchmode.anywhere) .list<app.domain.serviceprovider>();

depending on lazy settings, can initialize features collection with,

serviceproviders.tolist() .foreach(service => nhibernateutil.initialize(service.features));

nhibernate queryover