Understanding QueryExpression “Orders” in Dynamics 365

Recently I am experiencing an issue while setting “Order” of the QueryExpression with attributes name using link entity “Alias” name.

Let me give you a scenario let say you I am retrieving Account information and related primary contact information of the account. Here is my query, I am retrieving account name and primary contact first name, last name here. Here you can see I have used “primarycontact.firstname” in the order expression of the link entity. Below code give me error “primarycontact.firstname” does not exists in contact entity.

QueryExpression qe = new QueryExpression();

qe.EntityName = "account";

qe.ColumnSet = new ColumnSet();

qe.ColumnSet.Columns.Add("name");

qe.LinkEntities.Add(new LinkEntity("account", "contact", "primarycontactid", "contactid", JoinOperator.Natural));

qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");

qe.LinkEntities[0].EntityAlias = "primarycontact";

// Set Order for Child - No need entity Alias, just to add the link entity column name inside link entity order expression

OrderExpression linkentityOrder = new OrderExpression("primarycontact.firstname", OrderType.Ascending);

qe.LinkEntities[0].Orders.Add(linkentityOrder);

EntityCollection ec = organizationService.RetrieveMultiple(qe);

So now what should you use when you are retrieving multiple entity data with order of child entity or parent entity attributes. Now look at above query at first I am adding parent (Account) entity columns like –

qe.ColumnSet.Columns.Add("name");

And then I am adding columns of link entity (Contact) under link entity expression –

qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");

Which means we are defining columns in a query expression in two level, first for the parent entity and then the link entity level.  We don’t need to use any “Alias” name separately while setting ordering of the columns in a query expression. Simply use the name of the attributes which you set the columns under link entity.

Here you can see I have used the same name which I used at the time of adding the columns in the child entity as parent entity and this should work fine.

qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");

qe.LinkEntities[0].EntityAlias = "primarycontact";

// Set Order for Child - No need entity Alias, just to add the link entity column name inside link entity order expression

OrderExpression linkentityOrder = new OrderExpression("firstname", OrderType.Ascending);

Here is the final query which should work for you, make sure you cannot set two order expression at a time so I have commented parent “Order” expression.

QueryExpression qe = new QueryExpression();

qe.EntityName = "account";

qe.ColumnSet = new ColumnSet();

qe.ColumnSet.Columns.Add("name"); // parent entity (Account) Field

//  * Set Order for parent - No Need Alias name, only name of the attributes *

// OrderExpression ParentEntityOrder = new OrderExpression("name", OrderType.Descending);

// qe.Orders.Add(ParentEntityOrder);

qe.LinkEntities.Add(new LinkEntity("account", "contact", "primarycontactid", "contactid", JoinOperator.Natural));

qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname"); //link entity ( Contact) Field

qe.LinkEntities[0].EntityAlias = "primarycontact";

// Set Order for Child - No need to use entity Alias name, just to add attributes name which you add columns inside link entity.

OrderExpression linkentityOrder = new OrderExpression("firstname", OrderType.Ascending);

qe.LinkEntities[0].Orders.Add(linkentityOrder);

EntityCollection ec = organizationService.RetrieveMultiple(qe);

In summary, you cannot use “Alias” name in the “Order” expression  of any query expression, you should set attributes name only.