title: NHibernate - Retrieve specific columns and count using criteria query
body: <p>This is my mapping file:</p>
  
  <pre><code>class name="CRMStradCommon.Entities.OportunidadEntity,CRMStradCommon" table="oportunidad"&gt;
  
      &lt;id name="Id" column="id" type="int"&gt;
          &lt;generator class="native" /&gt;
      &lt;/id&gt;
      &lt;property name="Titulo" column="titulo" type="string" not-null="true" /&gt;
      &lt;many-to-one name="Estado" column="estado" class="CRMStradCommon.Entities.EstadoOportunidadEntity,CRMStradCommon"  /&gt;
      &lt;many-to-one name="Dueno" column="dueno" class="CRMStradCommon.Entities.ContactoEntity,CRMStradCommon"  /&gt;
      &lt;property name="FechaCierreEstimado" column="fecha_cierre_estimado" type="DateTime" not-null="false"/&gt;
      &lt;property name="FechaVencimiento" column="fecha_vencimiento" type="DateTime" not-null="false"/&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>This is the other with joined-subclass</p>
  
  <pre><code>class name="CRMStradCommon.Entities.ContactoEntity,CRMStradCommon" table="contacto"  dynamic-update="true"&gt;
      &lt;id name="Id" column="id" type="int"&gt;
          &lt;generator class="native" /&gt;
      &lt;/id&gt;
      &lt;property name="Nombre" column="nombre" type="string" not-null="true" /&gt;
      &lt;property name="Email1" column="email1" type="string" /&gt;
      &lt;property name="Email2" column="email2" type="string" /&gt;
      &lt;property name="Web1" column="web1" type="string" /&gt;
      &lt;property name="Web2" column="web2" type="string" /&gt;
      &lt;bag name="DuenoOportunidadList" lazy="true" inverse="true"&gt;
          &lt;key column="dueno"/&gt;
          &lt;one-to-many class="CRMStradCommon.Entities.OportunidadEntity,CRMStradCommon"/&gt;
      &lt;/bag&gt;
      &lt;joined-subclass name="CRMStradCommon.Entities.EmpresaEntity,CRMStradCommon" table="empresa" lazy="false"&gt;
          &lt;key column="id" /&gt;
          &lt;many-to-one name="Categoria" column="categoria" class="CRMStradCommon.Entities.CategoriaEmpresaEntity,CRMStradCommon"  /&gt;
          &lt;many-to-one name="Calificacion" column="calificacion" class="CRMStradCommon.Entities.CalificacionEmpresaEntity,CRMStradCommon"  /&gt;
      &lt;/joined-subclass&gt;
      &lt;joined-subclass  name="CRMStradCommon.Entities.PersonaEntity,CRMStradCommon" table="persona" lazy="false"&gt;
          &lt;key column="id" /&gt;
          &lt;property name="Saludo" column="saludo" type="string" /&gt;
          &lt;property name="Apellido" column="apellido" type="string" /&gt;
          &lt;property name="SegundoNombre" column="segundo_nombre" type="string" /&gt;
      &lt;/joined-subclass&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>How can I make this query with criteria?</p>
  
  <pre><code>SELECT     
      contacto.id, contacto.nombre, persona.apellido, COUNT(*) AS Cant
  FROM         
      contacto 
  INNER JOIN
      oportunidad ON contacto.id = oportunidad.dueno 
  LEFT OUTER JOIN
      persona ON contacto.id = persona.id 
  LEFT OUTER JOIN
      empresa ON contacto.id = empresa.id
  GROUP BY 
      contacto.id, contacto.nombre, persona.apellido
  ORDER BY 
      contacto.nombre, persona.apellido
  </code></pre>
  
  <p>Thanks a lot!</p>
  
  <p>Thanks a lot! It solved a part of my problem. I did this:</p>
  
  <pre><code>ICriteria criteria = session.CreateCriteria(typeof(ContactoEntity));
  criteria.SetProjection(Projections.ProjectionList()
                      .Add(Projections.GroupProperty("Id"),"Id")
                      .Add(Projections.GroupProperty("Nombre"),"Nombre")
                      .Add(Projections.GroupProperty("Apellido"), "Apellido")
                      .Add(Projections.GroupProperty("TipoContacto"), "TipoContacto")
                      .Add(Projections.RowCount(),"CantOportunidadesDueno"));
  criteria.CreateCriteria("DuenoOportunidadList");
  criteria.AddOrder(Order.Asc("Nombre")).AddOrder(Order.Asc("Apellido"));
  criteria.SetResultTransformer(
                      new NHibernate.Transform.AliasToBeanResultTransformer(typeof(ContactoEntity)));
  
  IList&lt;ContactoEntity&gt; ContLst = (criteria.List&lt;ContactoEntity&gt;());
  </code></pre>
  
  <p>I used Transformer in order to make a collection of <code>Contacto</code> entities, but the problem is with property <code>Apellido</code>. I have it in subclass Persona and the collection is made only with parent class and not with children classes.</p>
  
  <p>do you know if it is possible to solve that? or the only solution I have is to go throw each collection item without using transformer and creating a new collection of Contacto entities creating each new object?</p>
  
  <p>Thanks!!!</p>