How to query a many-to-many collection with NHibernate?

Question

I've been trying to interpret the answers to similar questions but haven't been able to make it work.

I have a list of activities, and each activity as a list of participants. Here are the mappings:

  <class name="Activity" lazy="false">
    <id name="ID">
      <generator class="guid" />
    </id>
    <list name="Participants">
      <key column="Activity" />
      <index column="Ord" />
      <many-to-many column="Contact" class="Model.Contact" />
    </list>
    <property name="Timestamp" />
  </class>

  <class name="Contact" lazy="false">
    <id name="ID">
      <generator class="guid" />
    </id>
    <property name="Name" />
  </class>

I am currently retrieving the activities ten at a time with this criteria:

var crit = ModelSession.Current.CreateCriteria<Activity>()
   .AddOrder(Order.Desc("Timestamp"))
   .SetFirstResult(start)
   .SetMaxResults(count);

Now I need to retrieve only those activities in which a particular person participated, in other words: where Contact.Name like '%some_name%'. In raw SQL I'd probably write something like this:

select * from Activity where ID in (select p.Activity from Participants p, 
Contact c where p.Contact=c.ID and c.Name like '%some_name%')

Any idea how to do this, with HQL or ICriteria, in a way that lets me keep the paged results and ordering? Many thanks!

Answer 1

You just need to add an appropriate condition to your criteria:

crit.CreateAlias("Participants", "participant")
.Add( Expression.Like("participant.Name", "%some_name%") );

See Criteria associations chapter of NHibernate documentation for more details

Update You can use DetachedCriteria to specify your Participant conditions as a subquery:

DetachedCriteria subquery = DetachedCriteria.For(typeof(Participant))
 .SetProjection(Projections.Property("activity"))
 .Add( Expression.Like("name", "%some_name%") );
crit.add( Subqueries.GeAll("ID", subquery) ).List();

Answer 2

ChssPly76 response above wasn't quite correct — this a unidirectional relationship, so there is no "activity" property on the contacts to project — but it got me on the right track. The correct answer is actually much simpler (in its own answer so I can format it properly):

crit.CreateCriteria("Participants")
   .Add(Expression.Like("Name", like));

Added to the criteria from the original question, this says "return a page of activities, ordered by timestamp, where a participant has this name".

Taken a step further, I really wanted any activity where the search term appeared in the list of participants OR in the description of the activity. In this case you really do need the subquery.

var subquery = DetachedCriteria.For<Activity>()
   .SetProjection(Projections.Property("ID"))
   .CreateCriteria("Participants")
   .Add(Expression.Like("Name", search_term));

crit.Add(Expression.Or(
   Subqueries.PropertyIn("ID", subquery),
   Expression.Like("Description", search_term));