Dynamics CRM: Query Hierarchical Data

Just browsing the Microsoft Documentation and am interested to know more about this post. Usually, as a Developer, I always got a requirement to take related data (parent-child) then do something with it. To fulfill that requirement, what I did was create a method that receives the parentId to take child records, and loop the data, inside the loop I will call again the same method until no child’s records exist. With these new operators, meaning that logic can be simpler!

Before we go to the code, I want to show you the test data that I prepared:

Account’s Hierarchical Data

Then here is the code for the demonstration purpose:

using System;
using System.Linq;
using System.Web.Configuration;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Tooling.Connector;

namespace CrmCheck
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = WebConfigurationManager.AppSettings["connectionString"];
            var client = new CrmServiceClient(connectionString);

            var account1 = GetAccount(client, "Account 1");
            PrintMethod(client, "Get All Child Records Under Account 1", ConditionOperator.Under, account1.Id);
            PrintMethod(client, "Get All Child Records Under Or Equal Account 1", ConditionOperator.UnderOrEqual, account1.Id);

            var account4 = GetAccount(client, "Account 4");
            PrintMethod(client, "Get All Child Records Above Account 4", ConditionOperator.Above, account4.Id);
            PrintMethod(client, "Get All Child Records Above Or Equal Account 4", ConditionOperator.AboveOrEqual, account4.Id);

            // Not a scenario that I will use 🙂
            PrintMethod(client, "Get All Child Records Not Under Account 1", ConditionOperator.NotUnder, account1.Id);


            Console.ReadKey();
        }

        private static void PrintMethod(CrmServiceClient client, string scenario, ConditionOperator conditionOperator, Guid accountId)
        {
            var query = new QueryExpression(Account.EntityLogicalName)
            {
                ColumnSet = new ColumnSet(true),
                NoLock = true,
            };
            query.Criteria.AddCondition("accountid", conditionOperator, accountId);
            var result = client.RetrieveMultiple(query);
            var data = result.Entities.Select(x => x.ToEntity<Account>()).ToArray();
            Console.WriteLine("Start " + scenario);
            foreach (var account in data)
            {
                Console.WriteLine(account.Name);
            }
            Console.WriteLine("End " + scenario);
            Console.WriteLine("");
            Console.WriteLine("");
        }

        private static Account GetAccount(CrmServiceClient client, string name)
        {
            var query = new QueryExpression(Account.EntityLogicalName)
            {
                ColumnSet = new ColumnSet(true),
                NoLock = true,
            };
            query.Criteria.AddCondition("name", ConditionOperator.Equal, name);
            var result = client.RetrieveMultiple(query);

            return result.Entities.FirstOrDefault()?.ToEntity<Account>();
        }
    }
}

And the below list is the result:

Start Get All Child Records Under Account 1
Account 2
Account 5
Account 3
Account 4
End Get All Child Records Under Account 1


Start Get All Child Records Under Or Equal Account 1
Account 1
Account 2
Account 5
Account 3
Account 4
End Get All Child Records Under Or Equal Account 1


Start Get All Child Records Above Account 4
Account 3
Account 2
Account 1
End Get All Child Records Above Account 4


Start Get All Child Records Above Or Equal Account 4
Account 4
Account 3
Account 2
Account 1
End Get All Child Records Above Or Equal Account 4


Start Get All Child Records Not Under Account 1
Fourth Coffee (sample)
Litware, Inc. (sample)
Adventure Works (sample)
Fabrikam, Inc. (sample)
Blue Yonder Airlines (sample)
City Power & Light (sample)
Contoso Pharmaceuticals (sample)
Alpine Ski House (sample)
A. Datum Corporation (sample)
Coho Winery (sample)
Account 1
test12381
End Get All Child Records Not Under Account 1

Happy CRM-ing!