Aggregate counting of a related object in an XML sample does not return 0

Question

Problem

I have an XML Fetch request that has a cumulative count for a related object that does not behave as expected. Basically, I return the list of courses and get a count of the number of contacts that are currently registered for the course, but even if no one is registered for the course, I get a score of 1 registered contact. If I register 1 contact, I get a score of 1. If I register 5 contacts, I get a score of 5, so the problem seems to fix it, not being able to get a score of 0 when there are no related entries.

Here is the XML file fetch

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true"> <entity name="new_coursesection"> <attribute name="new_termscheduleid" groupby="true" alias="new_termscheduleid" /> <attribute name="new_termid" groupby="true" alias="new_termid" /> <attribute name="new_sectionname" groupby="true" alias="new_sectionname" /> <attribute name="new_name" groupby="true" alias="new_name" /> <filter type="and"> <condition attribute="new_courseid" operator="eq" value="{some guid}" /> <condition attribute="statecode" operator="eq" value="0" /> </filter> <link-entity name="new_contactcoursesection" from="new_coursesectionid" to="new_coursesectionid" alias="new_contactcoursesection1" link-type="outer"> <attribute name="new_contactcoursesectionid" aggregate="count" alias="coursecount" /> <filter> <condition attribute="statecode" operator="eq" value="0"/> </filter> <order alias="coursecount" descending="true" /> </link-entity> </entity> </fetch> 

Has anyone encountered this problem and is aware of the job?

Answer 1

I found out the problem by looking at the generated SQL with the trace protocol enabled. It was necessary to change count to countcolumn:

Broken

 <attribute name="new_contactcoursesectionid" aggregate="count" alias="coursecount" /> 

Fix

 <attribute name="new_contactcoursesectionid" aggregate="countcolumn" alias="coursecount" /> 

Although I believe that in CRM 2011 there is an error related to cumulative values ​​of a related object in FetchXML. Even if it is marked with an alias, AliasedValue in the Attribute collection will use the actual CRM name, not the alias, although the AttributeCollection key will correctly use the aliases. Weird ...