Aggregate values with nested outer joins do not work

[ehclark]

I have a query that aggregates open activities across two levels of hierarchy. In the example here, the query returns one row per account with aggregate values for the number of open account tasks and the number of open child contact tasks. There are 3 problems with the FakeXrmEasy:

1. The aggregate values for nested linked entity are always zero (child contact tasks)
2. The aliased column names do not match what is returned from CRM
3. The alias attribute must be specified on the first level for an aggregate to be returned

Here is code that sets up data and run the query in both FakeXrmEasy and a real CRM instance:

        static void Main(string[] args)
        {
            string aggrFetchXml = $@"<fetch top=""1000"" distinct=""true"" aggregate=""true"" >
              <entity name=""account"" >
                <attribute name=""name"" alias=""acctname"" groupby=""true"" />
                <link-entity name=""task"" from=""regardingobjectid"" to=""accountid"" link-type=""outer"" alias=""task"" >
                    <attribute name=""activityid"" alias=""accttaskcnt"" aggregate=""countcolumn"" distinct=""true"" />
                    <filter>
                        <condition attribute=""statecode"" operator=""eq"" value=""0"" />
                    </filter>
                </link-entity>
                <link-entity name=""contact"" from=""parentcustomerid"" to=""accountid"" link-type=""outer"" alias=""ctapt"">
                    <link-entity name=""task"" from=""regardingobjectid"" to=""contactid"" link-type=""outer"" >
                      <attribute name=""activityid"" alias=""cttaskcnt"" aggregate=""countcolumn"" distinct=""true"" />
                      <filter>
                        <condition attribute=""statecode"" operator=""eq"" value=""0"" />
                      </filter>
                    </link-entity>
                </link-entity>
              </entity>
            </fetch>";

            // run with fake xrm
            XrmFakedContext fakecontext = new XrmFakedContext
            {
                ProxyTypesAssembly = System.Reflection.Assembly.GetAssembly(typeof(Task))
            };
            fakecontext.Initialize(InitEntities());
            IOrganizationService fakeorg = fakecontext.GetOrganizationService();
            EntityCollection results = fakeorg.RetrieveMultiple(new FetchExpression(aggrFetchXml));
            IOrderedEnumerable<string> stringList = results.Entities.Select(e =>
            {
                string s = e.GetAttributeValue<AliasedValue>("acctname")?.Value + ", " + e.GetAttributeValue<AliasedValue>("task.accttaskcnt")?.Value + ", " + e.GetAttributeValue<AliasedValue>("task.ctapt.cttaskcnt")?.Value;
                return s;
            }).OrderBy(e => e);
            Console.WriteLine("==== FakeXrmEasy Output ====");
            foreach (string s in stringList)
            {
                Console.WriteLine(s);
            }

            // run against real crm
            string conn = $@"
                        Url = https://****.crm.dynamics.com;
                        AuthType = Office365;
                        UserName = ****;
                        Password = ****;
                        RequireNewInstance = True";
            CrmServiceClient client = null;
            List<Entity> entities = InitEntities();
            try
            {
                client = new CrmServiceClient(conn);
                foreach (Entity e in entities)
                {
                    client.Create(e);
                }
                foreach (Entity e in entities)
                {
                    if (e.LogicalName == "task" && ((int)e["statecode"]) == 1)
                    {
                        client.Execute(new SetStateRequest
                        {
                            EntityMoniker = e.ToEntityReference(),
                            State = new OptionSetValue(1),
                            Status = new OptionSetValue(5)
                        });
                    }
                }
                results = client.RetrieveMultiple(new FetchExpression(aggrFetchXml));
                stringList = results.Entities.Select(e =>
                {
                    string s = e.GetAttributeValue<AliasedValue>("acctname")?.Value + ", " + e.GetAttributeValue<AliasedValue>("accttaskcnt")?.Value + ", " + e.GetAttributeValue<AliasedValue>("cttaskcnt")?.Value;
                    return s;
                }).OrderBy(e => e);
                Console.WriteLine("==== Real CRM Output ====");
                foreach (string s in stringList)
                {
                    Console.WriteLine(s);
                }
            }
            finally
            {
                Console.WriteLine("Press enter to cleanup and exit...");
                Console.ReadLine();

                foreach (Entity e in entities)
                {
                    try
                    {
                        client.Delete(e.LogicalName, e.Id);
                    }
                    catch (Exception)
                    {
                        // ignore
                    }
                }
                client.Dispose();
            }
        }
        static List<Entity> InitEntities()
        {
            List<Entity> entities = new List<Entity>();
            InitAccount(entities, "A01", 0, 1, 0, new int[] { }, new int[] { });
            InitAccount(entities, "A02", 1, 0, 0, new int[] { }, new int[] { });
            InitAccount(entities, "A03", 1, 1, 0, new int[] { }, new int[] { });
            InitAccount(entities, "A04", 2, 2, 0, new int[] { }, new int[] { });
            InitAccount(entities, "A05", 2, 2, 1, new int[] { 0 }, new int[] { 0 });
            InitAccount(entities, "A06", 2, 2, 1, new int[] { 0 }, new int[] { 1 });
            InitAccount(entities, "A07", 2, 2, 1, new int[] { 1 }, new int[] { 0 });
            InitAccount(entities, "A08", 2, 2, 1, new int[] { 1 }, new int[] { 1 });
            InitAccount(entities, "A09", 2, 2, 2, new int[] { 0, 0 }, new int[] { 0, 0 });
            InitAccount(entities, "A10", 2, 2, 2, new int[] { 0, 1 }, new int[] { 0, 0 });
            InitAccount(entities, "A11", 2, 2, 2, new int[] { 0, 0 }, new int[] { 0, 1 });
            InitAccount(entities, "A12", 2, 2, 2, new int[] { 0, 1 }, new int[] { 0, 1 });
            InitAccount(entities, "A13", 2, 2, 2, new int[] { 1, 1 }, new int[] { 1, 1 });
            InitAccount(entities, "A14", 2, 2, 2, new int[] { 2, 2 }, new int[] { 2, 2 });
            return entities;
        }
        static void InitAccount(List<Entity> entities, string acctName, int completeTasks, int openTasks, int numContacts, int[] completeTasksPerContact, int[] openTasksPerContact)
        {
            Entity acct = new Entity
            {
                Id = Guid.NewGuid(),
                LogicalName = "account",
                Attributes = { { "name", acctName } }
            };
            entities.Add(acct);
            for (int j = 0; j < completeTasks; j++)
            {
                Entity task = new Entity
                {
                    Id = Guid.NewGuid(),
                    LogicalName = "task",
                    Attributes = {
                        { "regardingobjectid", acct.ToEntityReference() },
                        { "subject", $"{acctName}-CT{j + 1}" },
                        { "statecode", 1 }
                    }
                };
                entities.Add(task);
            }
            for (int j = 0; j < openTasks; j++)
            {
                Entity task = new Entity
                {
                    Id = Guid.NewGuid(),
                    LogicalName = "task",
                    Attributes = {
                        { "regardingobjectid", acct.ToEntityReference() },
                        { "subject", $"{acctName}-OT{j + 1}" },
                        { "statecode", 0 }
                    }
                };
                entities.Add(task);
            }
            for (int i = 0; i < numContacts; i++)
            {
                Entity ct = new Entity
                {
                    Id = Guid.NewGuid(),
                    LogicalName = "contact",
                    Attributes = {
                        { "parentcustomerid", acct.ToEntityReference() },
                        { "lastname", $"{acctName}-C{i + 1}" }
                    }
                };
                entities.Add(ct);
                for (int j = 0; j < completeTasksPerContact[i]; j++)
                {
                    Entity task = new Entity
                    {
                        Id = Guid.NewGuid(),
                        LogicalName = "task",
                        Attributes = {
                            { "regardingobjectid", ct.ToEntityReference() },
                            { "subject", $"{acctName}-C{i + 1}-CT{j + 1}" },
                            { "statecode", 1 }
                        }
                    };
                    entities.Add(task);
                }
                for (int j = 0; j < openTasksPerContact[i]; j++)
                {
                    Entity task = new Entity
                    {
                        Id = Guid.NewGuid(),
                        LogicalName = "task",
                        Attributes = {
                            { "regardingobjectid", ct.ToEntityReference() },
                            { "subject", $"{acctName}-C{i + 1}-OT{j + 1}" },
                            { "statecode", 0 }
                        }
                    };
                    entities.Add(task);
                }
            }
        }

And here is the output returned:

==== FakeXrmEasy Output ====
A01, 1, 0
A02, 0, 0
A03, 1, 0
A04, 2, 0
A05, 2, 0
A06, 2, 0
A07, 2, 0
A08, 2, 0
A09, 2, 0
A10, 2, 0
A11, 2, 0
A12, 2, 0
A13, 2, 0
A14, 2, 0
==== Real CRM Output ====
A01, 1, 0
A02, 0, 0
A03, 1, 0
A04, 2, 0
A05, 2, 0
A06, 2, 1
A07, 2, 0
A08, 2, 1
A09, 2, 0
A10, 2, 0
A11, 2, 1
A12, 2, 1
A13, 2, 2
A14, 2, 4

[jordimontana82]

Thanks for raising this, so it's the nested outer joins that cause this behaviour as the results of the first outer join is correct, yep?

[ehclark]

Yes the aggregates on the first level join are correct. I did not do any tests to see if inner joins result in a different behavior since my particular scenario requires outer joins at both levels.

[mikewama]

Yes the aggregates on the first level join are correct. I did not do any tests to see if inner joins result in a different behavior since my particular scenario requires outer joins at both levels.

Same behaviour for inner joins as well.
Linked aggregates are prefixed with the entity name or alias attribute.

[mikewama]

Still an issue as of 1.58.1.
Has this been resolved in 2.0?

[jordimontana82]

@mikewama I don't think it has been fixed yet, will add it to the backlog of v2/v3 versions.