New Posts New Posts RSS Feed: Sorts on Primary entities and related entities
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Sorts on Primary entities and related entities
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
JonF View Drop Down
Newbie
Newbie


Joined: 17-Dec-2008
Posts: 3 		Post Options Post Options   Quote JonF Quote  Post ReplyReply Direct Link To This Post Topic: Sorts on Primary entities and related entities
    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!


Edited by JonF - 26-Jan-2009 at 4:34pm
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post 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.
 
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down