Ordering a Child Table Column In NHibernate
January 24, 2011 1 Comment
The following post is from our development engineer Jorge Cortillo.
Recently I ran into some issues when I was trying to order a column from a child table. The relationship between Parent table and Child table is going to be represented as a Foreign Key (also this relationship could be referenced without using a Foreign Key constraint, this scenario most of the time occurs in legacy databases) and in our mapping file we are going to have something like this:
ParentTable.hbm.xml
<bag name="ChildTable" inverse="true" lazy="false" cascade="all"> <key column="ParentID" /> <one-to-many /> </bag>
ChildTable.hbm.xml
<many-to-one name="ParentTable" column="ParentID" />
Our Properties are going to have the same reference:
ParentTable.cs
public IList<ChildTable> ChildTableList { get; set; }
ChildTable.cs
public ParentTable ParentTable { get; set; }
If you notice I’m not using “virtual” after public that’s because I declared my mapping files as default-lazy = false, so any proxy is going to be created.
Now let’s say that we have, besides this relationship, a couple of properties at each POCO:
ParentTable
Int ParentID String Description IList<ChildTable> ChildTableList
ChildTable
Int ChildID String FirstName String LastName ParentTable ParentTable
Well, now that we have a clear scenario, let’s go with our problem and how I solved it. Let’s say that the Business User requests that you have to order the results of ParentTable ordered by FirstName and LastName (from ChildTable). At first, if you think about a sql statement it should be pretty simple, because you just need to add an Inner Join then add an Order By ChildTable.FirstName ASC, ChildTable.LastName ASC. But how can we translate that into NHibernate? The answer is really simple. First, you need to explicitly declare the join. Then proceed to add the order statement, like this:
Session.CreateCriteria<ParentTable>("pt").CreateCriteria("ChildTable", "ct", JoinType.LeftOuterJoin);
Notice that the JoinType can be LeftOuterJoin, RightOuterJoin or InnerJoin. And finally proceed to add the order statement like this:
criteria.AddOrder(Order.Asc("ct.FirstName"));
criteria.AddOrder(Order.Asc("ct.LastName"));
That’s all the code you need if you want to order a list from a child table. If you want to return a list from a ParentTable ordered by any of its columns, you don’t have to explicitly declare the JoinType. NHibernate is going to do that for you. In this case, you’ll only need add AddOrder(Order.Asc(“columnName”)) and you can use Order.Desc also.
You can add more references if you want to order your result query by any child table. It’s not limited to only ParentTable – One Child Table.
Enjoy!
