title: SQL query to NHibernate query
body: <p>I have a SQL query</p>
  
  <pre><code>select distinct e.ID, e.FIRST_NAME, e.LAST_NAME, ef.PROJECT, r.NAME
  from EMPLOYEE as e
  inner join [RANK] as r on r.ID = e.[RANK]
  inner join [EMPLOYEE_FUNCTION_IN_PROJECT] as ef on e.ID = ef.EMPLOYEE
  where ef.PROJECT
  in (
      select ef.PROJECT
      from [EMPLOYEE_FUNCTION_IN_PROJECT] as ef
      group by ef.PROJECT
      having COUNT(distinct ef.EMPLOYEE) &gt;= 3
  )
  and ef.[FUNCTION] = 1
  </code></pre>
  
  <p>Now I would like to convert in to NHibernate query. This is what I've done so far</p>
  
  <pre><code>using (ITransaction myTransaction = mySession.BeginTransaction())
  {
      EmployeeDAO empDaoAlias = null;
      Employee empAlias = null;
      Group groupAlias = null;
      Project projectAlias = null;
      Rank rankAlias = null;
      EmployeeFunctionInProject efpAlias = null;
  
      var subquery = QueryOver.Of&lt;EmployeeFunctionInProject&gt;(() =&gt; efpAlias)
                      .Select(Projections.GroupProperty(
                          Projections.Property&lt;EmployeeFunctionInProject&gt;(e =&gt; e.Project)))
                      .Where(Restrictions.Gt(Projections.Count&lt;EmployeeFunctionInProject&gt;(e =&gt; e.Employee), 3));
  
      empList = mySession.QueryOver&lt;Employee&gt;(() =&gt; empAlias)
          .Inner.JoinAlias(() =&gt; empAlias.Rank, () =&gt; rankAlias)
              //.Where(Restrictions.Eq(Projections.Property&lt;Rank&gt;(r =&gt; r.Id), 1)) //indicate this is group leader
          .WithSubquery
              .WhereProperty(() =&gt; empAlias.Id)
              .In(subquery)
          .Select(Projections.ProjectionList()
          .Add(Projections.Property(() =&gt; empAlias.Id).WithAlias(() =&gt; empDaoAlias.Id))
          .Add(Projections.Property(() =&gt; empAlias.FirstName).WithAlias(() =&gt; empDaoAlias.FirstName))
          .Add(Projections.Property(() =&gt; empAlias.LastName).WithAlias(() =&gt; empDaoAlias.LastName))
          .Add(Projections.Property(() =&gt; ??).WithAlias(() =&gt; empDaoAlias.Project))
          .Add(Projections.Property(() =&gt; rankAlias.Name).WithAlias(() =&gt; empDaoAlias.Rank)))
          .TransformUsing(Transformers.AliasToBean&lt;EmployeeDAO&gt;())
          .List&lt;EmployeeDAO&gt;().ToList();
  }
  </code></pre>
  
  <p>First off, I need to modify </p>
  
  <pre><code>.Add(Projections.Property(() =&gt; ??).WithAlias(() =&gt; empDaoAlias.Project)) 
  </code></pre>
  
  <p>to show Project ID</p>
  
  <p>Secondly, the query doesn't seem to work correctly, can anyone help me to correct this?</p>
  
  <p>Thanks in advance!!!</p>