CodeFluent related collection sorting

Question

I have two CodeFluentEntities collections that are related in a typical Master/Detail relationship: Invoices and Invoice details.

While I can load the invoice collection in order by the invoice's number, I can't quite figure out how to get the related collection of invoice details to load in order by the invoice's line numbers.

This works in retrieving the invoice's in the desired sequence:

Dim _page As New CodeFluent.Runtime.PageOptions("[Invoice].[InvoiceNumber]", System.ComponentModel.ListSortDirection.Ascending)
Dim _orders as Accounting.AR.OrderCollection.PageLoadAll(0, -1, _page)
OrderCollectionBindingSource.DataSource = _orders

The InvoiceDetail collection is loaded in a random order. What I would like to do is have the related collection sorted by [InvoiceDetail].[LineNumber]

Answer 1

CodeFluent Entities offers several options to handle collections sorting:

• Static sorting: the collection is sorted in SQL using CFQL methods. You can also overwrite the LoadBy...() method if you need to. However, it is probably better to create a CFQL method, called LoadBy...OrderedBySomething().
• Dynamic sorting: the collection is sorted in SQL depending on sorting parameters.
• Client side sorting: with the Sort method that can be overriden for your needs.

All of these options are documented here: http://www.softfluent.com/documentation/?BOM_SP_Sorting.html

In your case, you want to change the method used by the Orders property to load the related collection of orders. So you need to create a new CFQL method (or replace the existing one) with static sorting. Then you can set this method as the one to use using the loadMethodName attribute:

<cf:entity name="Customer">
  <cf:property name="Id" key="true" />
  <cf:property name="FullName" />

  <!-- loadMethodName -->
  <cf:property name="Orders" loadMethodName="LoadByCustomerOrderedByCreationDate" typeName="{0}.OrderCollection" relationPropertyName="Customer" />
</cf:entity>

<cf:entity name="Order">
  <cf:property name="Id" key="true" />
  <cf:property name="CreationDate" sortable="true" typeName="date" />
  <cf:property name="Customer" typeName="{0}.Customer" relationPropertyName="Orders" />

  <!-- Static sorting: `ORDER BY ...` -->
  <cf:method name="LoadByCustomerOrderedByCreationDate" body="LOAD(Customer) WHERE Customer = @Customer ORDER BY CreationDate" />
</cf:entity>

The Orders property is:

public OrderCollection Orders
{
    get
    {
        if (this._orders == null)
        {
            // code omitted for brevity
            this._orders = OrderCollection.LoadByCustomerOrderedByCreationDate(this);
        }
        return this._orders;
    }
}