Search code examples
c#sqlnhibernatequeryover

Is it possible to have a subselect in the join part in Nhibernate QueryOver?

I am trying to duplicate 

Select
    ...
From Mistress Mi
Left Outer Join Dog D On Mi.ID = C.MistressID
    And D.MasterID = 
        ( Select Ma2.ID From Master Ma2 Where 
            D.MasterID = Ma2.ID 
            And Ma2.Status = 42 )
Left Outer Join Master Ma On 
    D.MasterID = Master.ID 
    And Ma.Status = 42

What this means is that:
Give me all Mistresses regardless having a dog or not.
Give me all Masters that have Status
and all Dogs that are connected to said Masters and a Mistress.

This might result in only Mistresses.
But if we have a Dog, we are sure it's Master has Status.

It is the subselect in the join that eludes me.
All my tries with WithSubQuery results in the subselect added to the final Where clause (and not inside the Join part as I want).

Is it even possible with NHibernate/QueryOver?

Solution

  • This should be possible. When we do JoinAlias (JOIN statement), we can pass the 4th parameter, so called with clause. In this Q & A:

    We can see an example

    var list = session
  .QueryOver<Job>()
  .JoinAlias(x => x.tags      // relation
     , () => jobTag,          // alias
     , JoinType.LeftOuterJoin // type
     , Subqueries.WhereProperty(() => jobTag.name).In(subquery) // here
  )
  .List();

    that 4th paramter is added into ON cluase with AND (it could just restrict more the existing relation conditions)