A terrible catch when ordering data using FetchXML

At the moment we are finishing up a product for CRM 2015 we have been developing over the last 7 months. As with most large software development projects, the last period is spent on stabilizing the product. In our product we are using batch functionality. In this batch functionality we do calculations based on the data stored in CRM.

In order to do the calculations we have to retrieve a large amounts of records from various entities. The records we receive are sorted on the Id of the entity. That way we assemble temporary object collections, that we use for generating new calculated records. A complex process.

While testing our batch functionality we found some exceptions regarding duplicate keys in our trace log, which seemed impossible to us.

Some investigation tought us that CRM has a non consistent behaviour of sorting records using FetchXML. In this article I’ll show you the catch. It might save you some valuable time.

Nicodewet-com_exceptions

Scenario

I created two entities: Entity One and Entity Two and filled them with data.

Entity One contains a name and a description (for identification purposes while editing)

Entity1

Entity Two contains a name and a lookup field to Entity One

Entity2

Now I create two FetchXML queries (QueryExpressions) to show the difference in behaviour. I run the queries in the FetchXML tester of the XRM toolbox.

Query for Entity One:
In this query I retrieve the name and the Id of the records ordered by the Id. I expect the list to be ordered by the Id of the entity.

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” >
<entity name=”new_entityone” >
<attribute name=”new_name” />
<attribute name=”new_entityoneid” />
<order attribute=”new_entityoneid” />
    </entity>
</fetch>

Results for Query for Entity One:

<resultset>
  <result>
    <new_name>A</new_name>
    <new_entityoneid>{001DA86E-7531-E511-80C8-00155D014C22}</new_entityoneid>
  </result>
  <result>
    <new_name>B</new_name>
    <new_entityoneid>{5C149775-7531-E511-80C8-00155D014C22}</new_entityoneid>
  </result>
  <result>
    <new_name>C</new_name>
    <new_entityoneid>{5D149775-7531-E511-80C8-00155D014C22}</new_entityoneid>
  </result>
  ...
  <result>
    <new_name>I</new_name>
    <new_entityoneid>{00006EA1-7531-E511-80C8-00155D014C22}</new_entityoneid>
  </result>
  <result>
    <new_name>A</new_name>
    <new_entityoneid>{87EB4ED9-7531-E511-80C8-00155D014C22}</new_entityoneid>
  </result>
</resultset>

 

Query for Entity Two:
In this query I retrieve the lookup to Entity One and the name of Entity Two, ordered by the lookup. I expect the list to be ordered the same way Entity one is ordered.

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” >
<entity name=”new_entitytwo” >
<attribute name=”new_entityone” />
<attribute name=”new_name” />
<order attribute=”new_entityone” />
    </entity>
</fetch>

Results for Query for Entity Two:

<resultset>
  <result>
    <new_entityone name="A" type="10068">{001DA86E-7531-E511-80C8-00155D014C22}</new_entityone>
    <new_name>record 01</new_name>
    <new_entitytwoid>{7D9E738B-7731-E511-80C8-00155D014C22}</new_entitytwoid>
  </result>
  <result>
    <new_entityone name="A" type="10068">{87EB4ED9-7531-E511-80C8-00155D014C22}</new_entityone>
    <new_name>Record 06</new_name>
    <new_entitytwoid>{75585ED1-7731-E511-80C8-00155D014C22}</new_entitytwoid>
  </result>
  <result>
    <new_entityone name="B" type="10068">{5C149775-7531-E511-80C8-00155D014C22}</new_entityone>
    <new_name>record 02</new_name>
    <new_entitytwoid>{C4F63A97-7731-E511-80C8-00155D014C22}</new_entitytwoid>
  </result>
  ...
  <result>
    <new_entityone name="E" type="10068">{35845B8C-7531-E511-80C8-00155D014C22}</new_entityone>
    <new_name>record 05</new_name>
    <new_entitytwoid>{5C2E79BD-7731-E511-80C8-00155D014C22}</new_entitytwoid>
  </result>
</resultset>

*BAM* I didn’t expect this behaviour!

It turns out that CRM orders data in a related entity on the name field rather than on the Id field. I would expect that CRM would order the data on the Id of the related entity.

Usually this is not a problem, but it will cause unpredicatable behaviour if the name of the related entity is not unique. You have the chance of ending up with a mixed up set of records with the same name but not with the same Id.

Leave a Reply

Your email address will not be published. Required fields are marked *