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

Question

I have used NHibernate for quite some time now but still struggle to do some "simple" stuff. I am trying to work with a many-to-many relationship between my entity ServiceProvider and Features.

Basically every SERVICE_PROVIDERS can have different features which must be present in my table FEATURES.

These are my 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 are the 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
    {
        get { return (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
    {
        get { return (new List<ServiceProvider>(_ServiceProviders).AsReadOnly()); }
    }
}

What I am trying to do is fetch all the services-providers (with all the features) where the description starts with a certain string, and they have at least one feature specified (param).

I reckon I need a subquery but I don't know how to build the QueryOver. It should be something like 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();

Answer 1

I've come up with an extension method with allows me to build expressions:

public static class ServiceProviderSearch
{
    public static IQueryOver<App.Domain.ServiceProvider, App.Domain.ServiceProvider> AttachWhereForSearchText(this IQueryOver<App.Domain.ServiceProvider, App.Domain.ServiceProvider> mainQuery, string searchText)
    {
        if (!string.IsNullOrEmpty(searchText))
        {
        ICriterion filterSearchText = Expression.Disjunction()
            .Add(Restrictions.On<App.Domain.ServiceProvider>(f => f.Description).IsLike(searchText, MatchMode.Anywhere))
            .Add(Restrictions.On<App.Domain.ServiceProvider>(f => f.ExtendedDescription).IsLike(searchText, MatchMode.Anywhere));
        mainQuery.Where(filterSearchText);
        }
        return (mainQuery);
    }

    public static IQueryOver<App.Domain.ServiceProvider, App.Domain.ServiceProvider> AttachWhereForFeatures(this IQueryOver<App.Domain.ServiceProvider, App.Domain.ServiceProvider> mainQuery, App.Domain.ServiceProvider serviceProvider, App.Domain.Feature features, Guid[] listOfFeatures)
    {
        if ((listOfFeatures != null) && (listOfFeatures.Count() > 0))
        {
        mainQuery
            .Inner.JoinAlias(() => serviceProvider.Features, () => features);

        mainQuery.WithSubquery.WhereProperty(() => features.Code)
                .In(
                QueryOver.Of<App.Domain.Feature>()
                    .WhereRestrictionOn(f => f.Code)
                    .IsIn(listOfFeatures)
                    .Select(f => f.Code)
                    );
        }
        return (mainQuery);
    }
}

So now I can write something like this:

App.Domain.ServiceProvider serviceProvider = null;
App.Domain.Feature features = null;
App.Domain.Language languages = null;
App.Domain.Service services = null;

Guid[] selectedFeatures = {};

var serviceProviders = Session.QueryOver(() => serviceProvider);    
serviceProviders
    .AttachWhereForSearchText(<searchText>)
    .AttachWhereForFeatures(serviceProvider, features, selectedFeatures);

Results = serviceProviders
    .TransformUsing(Transformers.DistinctRootEntity)
    .Take(<pageSize>)
    .Skip((<page> - 1) * <pageSize>)
    .ToList<App.Domain.ServiceProvider>();

Inspiration from this answer.

Answer 2

There are couple of things I would change in your snippet. First, you don't need to specify the % in your IsLike method: NHibernate does that automatically. Second, you can construct the subquery in the following manner:

var subquery = 
    session.QueryOver<App.Domain.Feature>()
           .WhereRestrictionOn(f => f.Description).IsLike("test", MatchMode.Anywhere)
           .Select(f => f.Code);

You can plug this in your main query:

var serviceProviders = 
    session.QueryOver<App.Domain.ServiceProvider>()
           .WithSubquery.WhereProperty(s => s.Code).In(subquery)
           .List();

Or you can even 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 your lazy settings, you can then initialize the Features collection with,

serviceProviders.ToList()
                .ForEach(service => NHibernateUtil.Initialize(service.Features));