How to query data across hierarchy using Dynamics 365/ DataVerse SDK

Hello everyone and welcome to my blog. In today’s blog, we will discuss about how you can query hierarchical data using SDK in Dynamics 365/ DataVerse

Let’s get started. I am going to use Account hierarchy (account -> parent account) relationship for this blog demo. But you can utilize the same learning for any hierarchical relationship like User and manager.

I have the following account hierarchy.

I want to find all the accounts which are under Fabrikam’s hierarchy. And below is the code to do that.

QueryExpression query = new QueryExpression("account");
            query.ColumnSet.AddColumn("name");
            query.Criteria.AddCondition("accountid", ConditionOperator.Under, fabrikamId); //fabrikamId is the GUID of the Fabrikam account.

            EntityCollection results = service.RetrieveMultiple(query);

            foreach(var ent in results.Entities)
            {
               Console.WriteLine(ent.GetAttributeValue<string>("name"));
            }

Observe the code highlighted in bold. The ConditionOperator – Under.

Unknown to many there are multiple operators in SDK which allow you to query records across hierarchy. Below is the compiled list taken from Microsoft documentation.

Below is the same query in FetchXML.

<fetch distinct="false" useraworderby="false" no-lock="false" options="MaxRecursion" mapping="logical">
   <entity name="account">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="accountid" operator="under" value="<fabrikam account id>" />
      </filter>
   </entity>
</fetch>

So if you have any requirement to query for records across hierarchy, you know the operators you need to use. Now coming to the final question. How do I accomplish the same from client side API’s?

You can use these same operators to construct your desired FetchXML and use Xrm.WebApi.retrieveMultipleRecords function to get the results.

Hope this helped. You will also like the below posts.

Debajit Dutta