How to sequence the column sort order while fetching data from Dataverse using client sdk?

[spmanjunath]

I have parent table called Location and a child table LocationSortOrder, i have used linked entities with left outer join and trying to sort the entities first by LocationSortOrder.SortOrderValue column and then the Location.LocationCode column. I have below code which does not give the desired result.

(Table: Columns)
Location: LocationCode, Description
LocationSortOrder: LocationCode, SortOrderValue

var parentQueryExpression = new QueryExpression(locationEntityName)
        {
            ColumnSet = new ColumnSet(columns),
            NoLock = true
        };

var linkedEntity = new LinkEntity
{
    LinkFromEntityName = locationEntityName,
    LinkToEntityName = locationSortOrderEntityName,
    LinkFromAttributeName = locationIDName,
    LinkToAttributeName = locationCodeName,
    JoinOperator = JoinOperator.LeftOuter,
    EntityAlias = "locationSortOrder",
    Columns = new ColumnSet(columnNames)
};

//(1)//linkedEntity.Orders.Add(new OrderExpression("sortOrderValue", OrderType.Ascending, "Location1SortOrder", locationSortOrderEntityName));

parentQueryExpression.LinkEntities.Add(linkedEntity);

var orderExpressionCollection = new Collection<OrderExpression>
    {
        //(2)//new OrderExpression("sortOrderValue", OrderType.Ascending, "Location1SortOrder", locationSortOrderEntityName),
        new OrderExpression(locationCodeName, OrderType.Ascending, "LocationCode", locationEntityName)
    };

parentQueryExpression.Orders.AddRange(orderExpressionCollection);

Uncommenting //(2)// gives binding error for column 'sortOrderValue'. Uncommenting //(1)// does not sort the column first by 'sortOrderValue' and then by 'locationCode'.

Note: Sorting on locationCode in LocationSortOrder table is not an option because not every record from Location is present in LocationSortOrder table.

[MattB-msft]

Im not totally clear on what your trying to do there.. that said,
Ordering is calculated from the top down; Outer entity order is applied before inner order.

What I would suggest you do here is use a tool like XrmToolbox's FetchXML builder to work out your query to get the structure of the flow you want. then use the Convert feature to turn it into Query expression ( or fetchxml ) and use it here.