Hi All,

I am having a parent and child relationship as shown below:

Parent Configuration:

<class name="PERSPerson" table="[dbo].[PERS_Person]" optimistic-lock="version" lazy="true">

<bag name="PERSPersonAddress" cascade="all-delete-orphan" inverse="true" lazy="true"
optimistic-lock="false">
<key>
<column name= "`Person_id`" />
</key >
<one-to-many class="PERSPersonAddress" />
</bag >

Child Configuration:

<class name="PERSPersonAddress" table="[dbo].[PERS_PersonAddress]" optimistic-lock="version" lazy="true">
<many-to-one name="PERSPerson" class="PERSPerson" cascade="save-update" outer-join="auto" lazy="false">
<column name="`Person_id`"/>
</many-to-one >


I tried to programmatically sort the sub objects based on the column in the child table as shown below.

ICriteria criteria = GetSession().CreateCriteria(typeof(T));
criteria.CreateAlias("PERSPersonAddress", "a").AddOrder(Order.Asc("a.PersonAddressEnteredDate")) ;

where T is parent type

What I see the SQL Profiler ( I am using Microsoft SQL Server) is a select statament with join statement for Parent and child with the order by clause as expected. When I try to get the child collection property on the Parent, another SQL statement for the child is submitted without the order by clause. I am wondering how to resolve this issue.

Also, I tried to configure the sorting by placing the order-by clause name as part of the parent's mapping file for the person address bag. It worked fine as expected.

Appreciate your help..

Thanks

You marked the bag as lazy="true" which means, the bag isn't initialized with your first query. When you access the property the bag will be lazily loaded, but now there is no order by, because it's not specified in the mapping.



I think that's the correct approach.

Thank you.

I thought about that and ran a test with lazy=false. But I don't see any change in the behaviour. Following are the queries submitted to the database.

exec sp_executesql N'SELECT this_.[Person_id] as Person1_78_1_, ... FROM [dbo].[PERS_Person] this_ inner join [dbo].[PERS_PersonAddress] a1_ on this_.[Person_id]=a1_.[Person_id] WHERE this_.[Person_id] = @p0 ORDER BY a1_.[PersonAddressEnteredDate] asc',N'@p0 bigint',@p0=1258

exec sp_executesql N'SELECT persperson0_.[Person_id] as Person3___1_, persperson0_.[PersonAddress_id] as PersonAd1_1... FROM [dbo].[PERS_PersonAddress] persperson0_ WHERE persperson0_.[Person_id]=@p0',N'@p0 bigint',@p0=1258

I think that the resultset that I get as part of the first query are overwritten by the second query. Any thoughts about that?

Try

Thank you. I tried the code

ICriteria criteria = GetSession().CreateCriteria(typeof(T), "t");
criteria.Add(Expression.IdEq(id));
criteria.SetFetchMode("t.PERSPersonAddress", FetchMode.Join);
criteria.AddOrder(Order.Asc("t.PERSPersonAddress.PersonAddressEnteredDate"));

and get the following exception

NHibernate.QueryException: could not resolve property: PERSPersonAddress.PersonAddressEnteredDate of: BusinessEntities.PERSPerson.

If I replace the last line with
criteria.CreateAlias("PERSPersonAddress", "a").AddOrder(Order.Asc("a.PersonAddressEnteredDate"));

I end up with the behaviour that I stated in my earlier post.

How do we order on a collection field, let it be a set or bag as list is usually obtained by the listIndex that we explicity specify?

How about the HQL? Sorry, it is the same.

from ParentObj parentObj
left join fetch parentObj.children child
order by child.childField

Thanks
Krishna