Aggregate Count of linked entity in fetch XML won't return 0

Daryl picture Daryl · Aug 28, 2012 · Viewed 8.7k times · Source

Problem

I have a Fetch XML query that has an aggregate count on a linked entity that isn't behaving as expected. Basically I'm returning a list of courses, and getting a count of how many contacts are currently registered for the course, but even if no one is registered for the course, I'm getting a count of 1 contacts registered. If I register 1 contact, I get a count of 1. If I register 5 contacts, I get a count of 5, so the issue seems to be resolving around not being able to get a count of 0, when there are no related records.

Here is the Fetch XML

<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 issue and know a work around?

Answer

Daryl picture Daryl · Aug 28, 2012

Figured out the issue after looking at the generated SQL with the trace log enabled. Had 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 there is a bug in CRM 2011 when dealing with aggregate values of a linked entity in FetchXML. Even though it is marked with an alias, the AliasedValue in the Attribute collection will use the actual CRM name, rather than the aliased one, even though the AttributeCollection's key will correctly use the aliased name. Weird...