Sorts on Primary entities and related entities

Originally posted by JonF

Originally posted by JonF

I've got what I'm sure is a basic question regarding sorts on queries.
I have two tables:  Orders (OrderId, OrderDate, CustomerId) and Customer (CustomerId, CustomerName) which are joined by CustomerId.

I would like to get a list of orders sorted by CustomerName and then OrderDate.  In sql this would be trivial...

SELECT * FROM Orders 
WHERE Orders.CustomerId = Customer.CustomerId 
ORDER BY Customer.CustomerName, Orders.OrderDate

However it doesnt seem to be as obvious in OQL.  I have 2 entities defined, Orders and Customers.  There is a relation defined between the two on CustomerId with the Customer table as the parent.

RdbQuery query = new RdbQuery(typeof(Orders))
EntitySubquery subQuery = query.AddSubQuery(EntityRelations.CustomerToOrders)

subquery.AddOrderBy(Customer.CustomerName)
query.AddOrderBy(Orders.OrderDate)

pm.GetEntities<Orders>(query)

The above gives me a list of Order entities, however they are sorted by OrderDate and then CustomerName.  I would like them sorted by CustomerName and *then* OrderDate.  Where am i going wrong?  :)

Thanks in advance! 

You'll have to sort the list returned by the GetEntities() call.  Your sort will look like the following:

   BindableList<SortItem<Order>> sortItems = new BindableList<SortItem<Order>>();
   sortItems.Add(new SortItem<Order> ("Customer.CompanyName", ListSortDirection.Ascending));
   sortItems.Add(new SortItem<Order>("OrderDate", ListSortDirection.Ascending));
   mOrders.ApplySort(new MultiPropertyComparer<Order>(sortItems), true);

The above depends upon two classes, the MultiPropertyComparer<T> and the SortItem<T>, that you can find in a file named MultiPropertyComparer.cs.  You can find this file in the 200 series instructional unit "BindableList", in the code solution named "ReusableMultiPropertySorter".  (Be sure to adjust the namespace declared in the MultiPropertyComparer.cs file to match the namespace where you want to use it.)

The topic of sorting BindableLists is covered in the Developers Guide in a section entitled "Sorting a BindableList(Of T)".  The MultiPropertyComparer<T> and the SortItem<T> are also documented there.  But you should be able to get what you want simply by including the MultiPropertyComparer.cs file in your solution and using the code above.