DevForce Classic: Sorts on Primary entities and related entities

Sorts on Primary entities and related entities

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1069
Printed Date: 10-Jun-2026 at 5:28pm

Topic: Sorts on Primary entities and related entities

Posted By: JonF
Subject: Sorts on Primary entities and related entities
Date Posted: 26-Jan-2009 at 4:30pm

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!

Replies:

Posted By: GregD
Date Posted: 28-Jan-2009 at 12:44pm

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.