title: Using NHibernate Criteria API to select specific set of data together with a count
body: <p>I have the following domain set up for persistence with NHibernate:
  <img src="https://lh6.ggpht.com/_aAiY0W-Lt88/SsDf_aB4xZI/AAAAAAAAAmE/py6GgcAMrrU/AccreditationDomainSection2.png" alt="Domain"></p>
  
  <p>I am using the PaperConfiguration as the root aggregate.</p>
  
  <p>I want to select all PaperConfiguration objects for a given Tier and AcademicYearConfiguration. This works really well as per the following example:</p>
  
  <pre><code>ICriteria criteria =
  session.CreateCriteria&lt;PaperConfiguration&gt;()
      .Add(Restrictions.Eq("AcademicYearConfiguration", configuration))
      .CreateCriteria("Paper")
      .CreateCriteria("Unit")
      .CreateCriteria("Tier")
          .Add(Restrictions.Eq("Id", tier.Id))
  
  return criteria.List&lt;PaperConfiguration&gt;();
  </code></pre>
  
  <p>(Perhaps there is a better way of doing this though).</p>
  
  <p>Yet also need to know how many ReferenceMaterials there are for each PaperConfiguration and I would like to get it in the same call. Avoid  HQL - I already have an HQL solution for it.</p>
  
  <p>I know this is what projections are for and <a href="https://stackoverflow.com/questions/532483/nhibernate-and-collection-counts">this question</a> suggests an idea but I can't get it to work. </p>
  
  <p>I have a PaperConfigurationView that has, instead of <code>IList&lt;ReferenceMaterial&gt; ReferenceMaterials</code> the ReferenceMaterialCount and was thinking along the lines of</p>
  
  <pre><code>ICriteria criteria =
  session.CreateCriteria&lt;PaperConfiguration&gt;()
      .Add(Restrictions.Eq("AcademicYearConfiguration", configuration))
      .CreateCriteria("Paper")
      .CreateCriteria("Unit")
      .CreateCriteria("Tier")
          .Add(Restrictions.Eq("Id", tier.Id))
      .SetProjection(
          Projections.ProjectionList()
             .Add(Projections.Property("IsSelected"), "IsSelected")
             .Add(Projections.Property("Paper"), "Paper")
              // and so on for all relevant properties
             .Add(Projections.Count("ReferenceMaterials"), "ReferenceMaterialCount")
      .SetResultTransformer(Transformers.AliasToBean&lt;PaperConfigurationView&gt;());
  
  return criteria.List&lt; PaperConfigurationView &gt;();
  </code></pre>
  
  <p>unfortunately this does not work. What am I doing wrong?</p>
  
  <p>The following simplified query:</p>
  
  <pre><code>ICriteria criteria =
  session.CreateCriteria&lt;PaperConfiguration&gt;()
  .CreateCriteria("ReferenceMaterials")
  .SetProjection(
  Projections.ProjectionList()
  .Add(Projections.Property("Id"), "Id")
  .Add(Projections.Count("ReferenceMaterials"), "ReferenceMaterialCount")
  ).SetResultTransformer(Transformers.AliasToBean&lt;PaperConfigurationView&gt;());
   return criteria.List&lt; PaperConfigurationView &gt;();
  </code></pre>
  
  <p>creates this rather unexpected SQL:</p>
  
  <pre><code>SELECT 
    this_.Id as y0_, 
    count(this_.Id) as y1_ 
  FROM Domain.PaperConfiguration this_ 
    inner join Domain.ReferenceMaterial referencem1_ 
      on this_.Id=referencem1_.PaperConfigurationId
  </code></pre>
  
  <p>The above query fails with ADO.NET error as it obviously is not a correct SQL since it is missing a group by or the count being count(referencem1_.Id) rather than (this_.Id).</p>
  
  <p>NHibernate mappings:</p>
  
  <pre><code>  &lt;class name="PaperConfiguration" table="PaperConfiguration"&gt;
      &lt;id name="Id" type="Int32"&gt;
        &lt;column name="Id" sql-type="int" not-null="true" unique="true" index="PK_PaperConfiguration"/&gt;
        &lt;generator class="native" /&gt;
      &lt;/id&gt;
      &lt;!-- IPersistent --&gt;
      &lt;version name="VersionLock" /&gt;
      &lt;!-- IAuditable --&gt;
      &lt;property name="WhenCreated" type="DateTime" /&gt;
      &lt;property name="CreatedBy" type="String" length="50" /&gt;
      &lt;property name="WhenChanged" type="DateTime" /&gt;
      &lt;property name="ChangedBy" type="String" length="50" /&gt;
  
      &lt;property name="IsEmeEnabled" type="boolean" not-null="true" /&gt;
  
      &lt;property name="IsSelected" type="boolean" not-null="true" /&gt;
  
      &lt;many-to-one name="Paper" column="PaperId" class="Paper" not-null="true" access="field.camelcase"/&gt;
  
      &lt;many-to-one name="AcademicYearConfiguration" column="AcademicYearConfigurationId" class="AcademicYearConfiguration" not-null="true" access="field.camelcase"/&gt;
  
      &lt;bag name="ReferenceMaterials" generic="true" cascade="delete" lazy="true" inverse="true"&gt;
        &lt;key column="PaperConfigurationId" not-null="true" /&gt;
        &lt;one-to-many class="ReferenceMaterial" /&gt;
      &lt;/bag&gt;
    &lt;/class&gt;
  
    &lt;class name="ReferenceMaterial" table="ReferenceMaterial"&gt;
      &lt;id name="Id" type="Int32"&gt;
        &lt;column name="Id" sql-type="int" not-null="true" unique="true" index="PK_ReferenceMaterial"/&gt;
        &lt;generator class="native" /&gt;
      &lt;/id&gt;
      &lt;!-- IPersistent --&gt;
      &lt;version name="VersionLock" /&gt;
      &lt;!-- IAuditable --&gt;
      &lt;property name="WhenCreated" type="DateTime" /&gt;
      &lt;property name="CreatedBy" type="String" length="50" /&gt;
      &lt;property name="WhenChanged" type="DateTime" /&gt;
      &lt;property name="ChangedBy" type="String" length="50" /&gt;
  
      &lt;property name="Name" type="String" not-null="true" /&gt;
      &lt;property name="ContentFile" type="String" not-null="false" /&gt;
      &lt;property name="Position" type="int" not-null="false" /&gt;
      &lt;property name="CommentaryName" type="String" not-null="false" /&gt;
      &lt;property name="CommentarySubjectTask" type="String" not-null="false" /&gt;
      &lt;property name="CommentaryPointScore" type="String" not-null="false" /&gt;
      &lt;property name="CommentaryContentFile" type="String" not-null="false" /&gt;
  
      &lt;many-to-one name="PaperConfiguration" column="PaperConfigurationId" class="PaperConfiguration" not-null="true"/&gt;
    &lt;/class&gt;
  </code></pre>