We're trying to use an Order By clause on our database, on a child object. The SQL trace shows that NHibernate is sending the correct SQL to the database (i.e. the Order By clause is correct). But for some strange reason, the objects in the object graph are in the wrong order. this is the code we're using:
Code:
ICriteria criteria = CurrentSession.CreateCriteria(typeof(PurchaseOrder));
criteria.Add(Restrictions.Eq("PurchaseOrderId", id)).UniqueResult<PurchaseOrder>();
criteria.Add(Restrictions.Eq("ActiveFlag", true)).UniqueResult<PurchaseOrder>();
criteria = criteria.CreateCriteria("PurchaseOrderLineItems");
criteria = criteria.CreateCriteria("Disposition");
criteria.AddOrder(Order.Asc("SortOrder"));
PurchaseOrder x = criteria.UniqueResult<PurchaseOrder>();
//initialise other collections here
NHibernateUtil.Initialize(x.Vendors);
NHibernateUtil.Initialize(x.Operators);
NHibernateUtil.Initialize(x.PurchaseOrderLineItems);
So in a nutshell, our main object is PurchaseOrder, this contains a PurchaseOrderLineItems collection, each PurchaseOrderLineItem has a Disposition object with a SortOrder property, which is what we're trying to sort by. If we examine variable x, the PurchaseOrderLineItems collection is never returned in the correct order, although the SQL executed is right (we're checking the log file for the SQL).
The OrderBy seems to work on the properties of the parent (PurchaseOrder) object, when drilling into children this seems to fail. Are we doing anything daft here?