NHibernate selecting from a many to many collection

Question

I would like NHibernate to generate the following SQL but I'm stuggling with the code below

   SELECT rt.Id 
   FROM ClientContact cc  
     JOIN ClientContact_DefaultRequest d on d.ClientContactID = cc.Id
     JOIN RequestType rt on d.RequestTypeId = rt.Id
   WHERE 
     cc.Id = ContactId



public class GetDefaultRequestsForContact : SimpleQuery<IEnumerable<RequestType>>, IGetDefaultRequestsForContact
{
    public int ContactId { private get; set; }

    public GetDefaultRequestsForContact(ISession session) : base(session) { }

    public override IEnumerable<RequestType> Execute()
    {
        var x = Session.QueryOver<ClientContact>()
            .Where(c => c.Id == ContactId)
            .JoinQueryOver<RequestType>(c => c.DefaultRequests)
            .Select(c => c.DefaultRequests)
            .List();
        return null;
    }
}

The generated SQL is only selecting the parent id (which is the parameter) I want all the child Ids.

SELECT this_.Id as y0_
FROM   ClientContact this_
       inner join ClientContact_DefaultRequest defaultreq3_
         on this_.Id = defaultreq3_.ClientContactID
       inner join RequestType requesttyp1_
         on defaultreq3_.RequestTypeID = requesttyp1_.Id
WHERE  this_.Id = 313706 /* @p0 */

This works but it is not strongly typed (HQL).

        var x = Session.CreateQuery("SELECT R.Id FROM ClientContact cc JOIN cc.DefaultRequests R WHERE cc.Id = :contactId")
            .SetParameter("contactId",ContactId)
            .List<int>();
        return x;

Answer 1

Try using Aliases like:

ClientContact Cont = null;
RequestType Req = null;

var x = session.QueryOver<ClientContact>(() => Cont)
.Where(() => Cont.ID == ContactId)
.JoinAlias(() => Cont.DefaultRequests, ()=> Req, JoinType.LeftOuterJoin)
.Select(ignore => Req.Id) // <-- Select wants a parameter
.List<int>();