How to Query Many to Many Objects in NHibernate

Question

Hi,

I have two database tables that have a many to many relationship: People and Organization. And of course there is a table that connects these two called OrganizationPeople.

In NHibernate mapping, they each have a Bag of the other. In People's mapping, the Bag is mapped like this:

  <bag name="Organizations" table="OrganizationPeople" cascade="none" inverse="false" lazy="true">
      <key column="PersonId"/>
      <many-to-many column="OrgId" class="DataTransfer.Organization, DataTransfer" fetch="select"/>
    </bag>

And Organization's mapping the Bag is like

  <bag name="Persons" table="OrganizationPeople" cascade="none" inverse="false" lazy="true">
      <key column="OrgId"/>
      <many-to-many column="PersonId" class="DataTransfer.People, DataTransfer" fetch="select"/>
    </bag>

What I am trying to do now is query the Organization by OrgId to find out all the assocated People using this HQL:

 public IList<Organization> GetByOrgIdAndUserId(System.Guid orgId)
        {
            return NHibernateSession.CreateQuery("from Organization o join o.Persons as p where o.OrgId = :orgId")
                .SetGuid("orgId", orgId)
                .List<Organization>();


        }

This is what my unit test returned:

NHibernate: select organizati0_.OrgId as OrgId21_0_, people2_.PersonId as PersonId12_1_, organizati0_.EmployerIdentificationNo as Employer2_21_0_, organizati0_.InactiveDate as Inactive3_21_0_, organizati0_.OrgName as OrgName21_0_, organizati0_.UDSId as UDSId21_0_, organizati0_.ExternalOrgTypeCode as External6_21_0_, organizati0_.OrgInstance as OrgInsta7_21_0_, organizati0_.DUNS as DUNS21_0_, organizati0_.ProfileFileNumber as ProfileF9_21_0_, organizati0_.CentralRegistryEntityIdentificationNo as Central10_21_0_, organizati0_.PMSOrganizationFlag as PMSOrga11_21_0_, organizati0_.PIN as PIN21_0_, organizati0_.CreatedDate as Created13_21_0_, organizati0_.LastUpdateDate as LastUpd14_21_0_, organizati0_.InstitutionCode as Institu15_21_0_, organizati0_.WebURL as WebURL21_0_, people2_.FirstName as FirstName12_1_, people2_.LastName as LastName12_1_, people2_.Title as Title12_1_, people2_.UserId as UserId12_1_, people2_.GenderCode as GenderCode12_1_, people2_.EmployeeTypeCode as Employee7_12_1_, people2_.EmployeeStatusCode as Employee8_12_1_, people2_.PreferredContactMethodCode as Preferre9_12_1_ from IKNSubmissions.dbo.ExternalOrganizations organizati0_ inner join IKNSubmissions.dbo.OrganizationPeople persons1_ on organizati0_.OrgId=persons1_.OrgId inner join IKNSubmissions.dbo.People people2_ on persons1_.PersonId=people2_.PersonId where organizati0_.OrgId=@p0;@p0 = b7a8fda8-a65a-4607-81ca-e374fe75d685

System.ArgumentException: The value "System.Object[]" is not of type "IKN.DataTransfer.Organization" and cannot be used in this generic collection.
Parameter name: value
at System.ThrowHelper.ThrowWrongValueTypeArgumentException(Object value, Type targetType)
at System.Collections.Generic.List`1.VerifyValueType(Object value)
at System.Collections.Generic.List`1.System.Collections.IList.Add(Object item)
at NHibernate.Util.ArrayHelper.AddAll(IList to, IList from)
at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
at NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results) 
NHibernate.Exceptions.GenericADOException: Could not execute query[SQL: SQL not available]
at NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List<T>(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List<T>()
at IKN.DataAccess.OrganizationDao.GetByOrgIdAndUserId(Guid orgId) in OrganizationDao.cs: line 16
at IKNDataAccessTest.OrganizationTest.CanGetByOrgId() in OrganizationTest.cs: line 42 

It seems that it generated the query corrected based on the mapping but something happened later when creating return result. But I am not sure what is causing this.

Any help is greatly appreciated.

John

Answer 1

Try this:

 return NHibernateSession.CreateQuery("select o from Organization o join o.Persons as p where o.OrgId = :orgId")
   .SetGuid("orgId", orgId)
   .SetResultTransformer(new DistinctRootEntityResultTransformer())
   .List<Organization>();

Answer 2

I think if you remove the join, the collection of associated People objects will be lazy loaded when you access them. If you want the collection to be loaded in the same query then this should work:

select o from Organization o join fetch o.Persons as p where o.OrgId = :orgId