title: NHibernate - Union three QueryOvers
body: <p>Hi StackOverflow users,</p>
  
  <p>I ran into this problem
  I have three QueryOvers and each of them returns a list of candidate ids that I then use to bring those candidates. 
  For this I wrote the following code.</p>
  
  <pre><code>        private IQueryOver&lt;CandidateEntity, CandidateEntity&gt; UnionPublicWithPrivateCandidates(
          IQueryOver&lt;CandidateEntity, CandidateEntity&gt; publicCandidates, 
          IQueryOver&lt;CandidateEntity, CandidateEntity&gt; privateCandidate, 
          IQueryOver&lt;CandidateEntity, CandidateEntity&gt; candidatesByUserRole)
      {
          return ActiveCandidatesQueryOver.Where(Restrictions.Disjunction()
                                          .Add(Subqueries
                                              .WhereProperty&lt;CandidateEntity&gt;(c =&gt; c.Id)
                                              .In((QueryOver&lt;CandidateEntity&gt;)publicCandidates.Select(c =&gt; c.Id)))
                                          .Add(Subqueries
                                              .WhereProperty&lt;CandidateEntity&gt;(c =&gt; c.Id)
                                              .In((QueryOver&lt;CandidateEntity&gt;)privateCandidate.Select(c =&gt; c.Id)))
                                          .Add(Subqueries
                                              .WhereProperty&lt;CandidateEntity&gt;(c =&gt; c.Id)
                                              .In((QueryOver&lt;CandidateEntity&gt;)candidatesByUserRole.Select(c =&gt; c.Id))));
      }
  </code></pre>
  
  <p>This returns the correct results and the generated query looks like this</p>
  
  <pre><code>SELECT *
  FROM   Applicants 
  WHERE  IsActive = 1
     and (Id in (SELECT Id from **FirstQueryOver**)
           **or** Id in (SELECT Id from **SecondQueryOver**)
           **or** Id in (SELECT Id from **ThirdQueryOver**))
  </code></pre>
  
  <p>The problem is that it uses 'or'. Because of this the query is painfully slow.</p>
  
  <p>If instead I write this:</p>
  
  <pre><code>SELECT *
  FROM   Applicants 
  WHERE  IsActive = 1
     and (Id in (SELECT Id from **FirstQueryOver**
                       union SELECT Id from **SecondQueryOver**
                       union SELECT Id from **ThirdQueryOver**))
  </code></pre>
  
  <p>It finishes almost instantly.</p>
  
  <p>Do you have any idea on how should I refactor the code for better performance?</p>
  
  <p>Thank you,
  Adrian.</p>