How can I make a query that returns objects who contain all ids from list

Question

I have two objects : Profile and Tags. Each profile can contain multiple tags. On my search page I can select multiple tags to search on. Now I want a query that get all profiles that have all the selected tags.

So if I use WhereRestrictionOn().IsIn() I get profiles which contains at least 1 of the tags but I need to return profiles which contains all the tags in the list.

I also tried multiple Where conditions for each selected tag but then I get no results at all.

I have no clue how to do this any help is much appreciated!

Structure:

Profile : Id

ProfileTag : ProfileId, TagId

Tag: Id

Mapping Profile

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
  <class name="Agrolink.Application.Models.Profile" lazy="false" table="Profiles" >

    <id name="Id" column="Id"  >
      <generator class="identity" />
    </id>

    <bag name="Tags" table="ProfileTags" cascade="all-delete-orphan" inverse="true">
      <key column="IdProfile" not-null="true"/>
      <one-to-many class="Agrolink.Application.Models.ProfileTag"  />
    </bag>

  </class>
</hibernate-mapping>

Mapping ProfileTag

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
  <class name="Agrolink.Application.Models.ProfileTag" lazy="false" table="ProfileTags" >

    <id name="Id" column="Id"  >
      <generator class="identity" />
    </id>

    <many-to-one name="Profile" class="Agrolink.Application.Models.Profile" column="IdProfile" cascade="save-update" />
    <many-to-one name="Tag" class="Agrolink.Application.Models.Tag" column="IdTag" cascade="none" />

  </class>
</hibernate-mapping>

Mapping Tag

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
  <class name="Agrolink.Application.Models.Tag" lazy="false" table="Tags" >

    <id name="Id" column="Id"  >
      <generator class="identity" />
    </id>

    <property name="Name" column="Name" />
    <property name="Type"  type="Agrolink.Application.Models.TagType, Agrolink.Application"  column="IdType" />

    <many-to-one name="Parent" class="Agrolink.Application.Models.Tag" column="IdParent" cascade="none" />

    <bag name="Children" table="Tags" cascade="all" inverse="true">
      <key column="IdParent" not-null="true"/>
      <one-to-many class="Agrolink.Application.Models.Tag"  />
    </bag>

  </class>
</hibernate-mapping>

SubQuery to achieve this (Solution):

        Profile p = null;
        Account a = null;
        Institute i = null;

        var q = Session.QueryOver(() => p)
            .JoinAlias(x => x.Account, () => a)
            .JoinAlias(x => x.Institute, () => i)
            .Where(x => x.Type == ProfileType.Expert && x.Status == ProfileStatus.Active);

        if(_keywordIds.Any())
            foreach (var keywordId in _keywordIds)
            {
                Tag t = null;

                var subQ = QueryOver.Of<ProfileTag>()
                    .JoinAlias(pt => pt.Tag, () => t)
                    .Where(() => t.Id == keywordId)
                    .Select(pt => pt.Profile.Id);           

                q.WithSubquery.WhereProperty(() => p.Id).In(subQ);
            }

        if (_institute != null) q.Where(() => i.Id == _institute);

        if (!string.IsNullOrEmpty(_name)) q.Where(Restrictions.Disjunction()
                    .Add(Restrictions.Like("a.FirstName", _name + "%"))
                    .Add(Restrictions.Like("a.LastName", _name + "%"))
                );

        return (PagedList<Profile>) q.List<Profile>().ToPagedList(_page, _itemsPerPage);

Answer 1

It is almost it, but we need so called Detached QueryOver, which we will get with construction QueryOver.Of

foreach (var keywordId in _keywordIds)
{
    //Tag t = null;

    var subQ = QueryOver.Of<ProfileTag>()
        //.JoinAlias(pt => pt.Tag, () => t)
        //.Where(() => t.Id == keywordId)
        .Where(x => x.Tag.Id == keywordId)
        //.Select(pt => t.Id);           
        .Select(pt => pt.Profile.Id);          

    q.WithSubquery.WhereProperty(() => p.Id).In(subQ);
}