Microsoft's FetchXML

A Microsoft Gold Partner recommended, today (28 May 2021), that we should use Microsoft's FetchXML as the method of accessing our data in Microsoft Dynamics 365.

Here's a sample of FetchXML from Microsoft's own website

<fetch version="1.0" output-format="xml-platform" latematerialize="true"
       mapping="logical" distinct="true">

  <entity name="account">
    <attribute name="accountnumber" />
    <attribute name="createdby" />
    <attribute name="ownerid" />

    <link-entity name="account" from="accountid" to="parentaccountid"
                 link-type="outer" alias="oaccount">
      <attribute name="createdby" />

      <link-entity name="account" from="accountid" to="accountid" link-type="outer"
                 alias="oaccount1">
        <attribute name="createdby" />
        <attribute name="accountid" />
        <attribute name="name" />
      </link-entity>
    </link-entity>

    <link-entity name="account" from="accountid" to="accountid" link-type="outer"
                 alias="oaccount2"/>

    <filter type='and'>
      <condition attribute="statecode" operator="eq" value="2"/> 
    </filter>
  </entity>

</fetch>

The loyal Microsoft Gold Partner insisted that this is better than SQL, Here's the equivalent SQL for comparison.

select distinct
    a.account_number,
    a.created_by      as account_created_by,
    a.owner_id,
    b.created_by      as parent_account_created_by,
    b.account_id
    b.name
from 
    account as a
    left outer join
    account as b
    on a.parent_account = b.account_id
where 
    statecode = 2;

Since FetchXML is so good, I guess I'll do all my writing like this:

<communicationunit level="sentence">
    <communicationunit level="word" partofspeech="article" subtype="indefinite" value="A" />
    <communicationunit level="word" partofspeech="noun" subtype="proper" qualifier="adjectival" value="Microsoft" />
    <communicationunit level="word" partofspeech="adjective" value="Gold" />
    <communicationunit level="word" partofspeech="noun" value="Partner" />
    <communicationunit level="word" partofspeech="verb" tense="past" subtype="perfect" value="recommended" />
    <communicationunit level="word" partofspeech="noun" usage="temporalqualifier" value="today" />
    <communicationunit level="word" partofspeech="conjunction" value="that" />
    <communicationunit level="subordinateclause">
        <communicationunit level="word" partofspeech="pronoun" value="we" />
        <communicationunit level="word" partofspeech="verb" subtype="modal" value="should" />
        <communicationunit level="word" partofspeech="verb" value="use" />
        <communicationunit level="word" partofspeech="noun" subtype="proper" case="genitive" value="Microsoft's" /> 
        <communicationunit level="word" partofspeech="noun" subtype="proper" value="FetchXML" />
        <communicationunit level="word" partofspeech="preposition" value="as" /> 
        <communicationunit level="word" partofspeech="article" subtype="definite" value="the" />
        <communicationunit level="word" partofspeech="noun" value="method" />
        <communicationunit level="word" partofspeech="preposition" value="of" />  
        <communicationunit level="word" partofspeech="noun" subtype="verbal" value="accessing" />
        <communicationunit level="word" partofspeech="determiner" subtype="possessive" value="our" />
        <communicationunit level="word" partofspeech="noun" value="data" />
        <communicationunit level="word" partofspeech="preposition" value="in" />
        <communicationunit level="word" partofspeech="noun" subtype="proper" qualifier="adjectival" value="Microsoft" />
        <communicationunit level="word" partofspeech="noun" subtype="proper" value="Dynamics" />
        <communicationunit level="word" partofspeech="noun" subtype="proper" value="365" />
    </communicationunit>
</communicationunit>

And isn't Einstein's famous equation is so much easier like this?

<equation attribute="E" operator="eq">
    <expression attribute="m" operator="mult">
        <expression attribute="c" operator="pow" value="2" />
    </expression>
</equation>

But seriously...

The example of FetchXML above makes no sense to me. Why would you join a table to itself on the same key? I cannot see any purpose in the link entities oaccount1 and oaccount2. And if you are joining a table to itself on the same key you certainly would not need an outer join. created_by appears twice in the results so needs to be renamed as I did in the SQL version. Maybe a FetchXML expert can explain?

The explanation of performance tuning on the Microsoft website suggests that Microsoft Dataverse does not have a query optimiser. Surely that cannot be right?

In the sparse FetchXML documentation, I cannot find any reference to creating even a simple formula, so my FetchXML-like rendition of E=mc² may not work at all.

All the XML on this page is "well formed", which does not mean what you might expect. In fact, "well formed" is a very low bar.

In XML, information can be recorded in elements and in attributes. There are no rules about when to use elements or when to use attributes in XML. However, the sound advice from W3Schools is "metadata (data about data) should be stored as attributes, and the data itself should be stored as elements." Microsoft ignores this advice in FetchXML. But, this is nit-picking; the entire idea of using XML to write code is ridiculous.

And, finally, I would never use the naming convention used here - sticking all the words together. I use underscores between words (best), or CamelCase (when I need to be consistent with existing use of CamelCase). And why on earth abbreviate "equals" to "eq"? If you want a short form use "=".