This is my mapping file:
class name="CRMStradCommon.Entities.OportunidadEntity,CRMStradCommon" table="oportunidad"> <id name="Id" column="id" type="int"> <generator class="native" /> </id> <property name="Titulo" column="titulo" type="string" not-null="true" /> <many-to-one name="Estado" column="estado" class="CRMStradCommon.Entities.EstadoOportunidadEntity,CRMStradCommon" /> <many-to-one name="Dueno" column="dueno" class="CRMStradCommon.Entities.ContactoEntity,CRMStradCommon" /> <property name="FechaCierreEstimado" column="fecha_cierre_estimado" type="DateTime" not-null="false"/> <property name="FechaVencimiento" column="fecha_vencimiento" type="DateTime" not-null="false"/> </class>
This is another with a combined subclass.
class name="CRMStradCommon.Entities.ContactoEntity,CRMStradCommon" table="contacto" dynamic-update="true"> <id name="Id" column="id" type="int"> <generator class="native" /> </id> <property name="Nombre" column="nombre" type="string" not-null="true" /> <property name="Email1" column="email1" type="string" /> <property name="Email2" column="email2" type="string" /> <property name="Web1" column="web1" type="string" /> <property name="Web2" column="web2" type="string" /> <bag name="DuenoOportunidadList" lazy="true" inverse="true"> <key column="dueno"/> <one-to-many class="CRMStradCommon.Entities.OportunidadEntity,CRMStradCommon"/> </bag> <joined-subclass name="CRMStradCommon.Entities.EmpresaEntity,CRMStradCommon" table="empresa" lazy="false"> <key column="id" /> <many-to-one name="Categoria" column="categoria" class="CRMStradCommon.Entities.CategoriaEmpresaEntity,CRMStradCommon" /> <many-to-one name="Calificacion" column="calificacion" class="CRMStradCommon.Entities.CalificacionEmpresaEntity,CRMStradCommon" /> </joined-subclass> <joined-subclass name="CRMStradCommon.Entities.PersonaEntity,CRMStradCommon" table="persona" lazy="false"> <key column="id" /> <property name="Saludo" column="saludo" type="string" /> <property name="Apellido" column="apellido" type="string" /> <property name="SegundoNombre" column="segundo_nombre" type="string" /> </joined-subclass> </class>
How can I make this query with criteria?
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
Thanks a lot!
Thanks a lot! He solved part of my problem. I have done this:
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<ContactoEntity> ContLst = (criteria.List<ContactoEntity>());
I used Transformer to create a collection of Contacto objects, but the problem is with the Apellido property. I have it in a Persona subclass, and the collection is done only with the parent class, not with the child classes.
Do you know if this can be solved? or is the only solution I have to drop every element of the collection without using a transformer and create a new collection of Contacto objects that create each new object?
Thanks!!!