I am facing issue while querrying the data in fetchxml. Both querries are similar but one not providing the result

[ravifulani]

I have two similar queries, my first one is working fine and result is coming in tabular format in fetchxml builder but for second I am getting error useridname does not exist and querry is not running, then it opens xml format result which is not correct. below are two querries, with audit entity result is not poping up. Any ideas, why is that?

FIRST Query

<fetch top="50" aggregate="true" >
<entity name="msdyn_workorder" >
<attribute name="createdby" alias="CB" groupby="true" />
<attribute name="msdyn_workorderid" alias="UI" aggregate="count" />
<filter>
<condition attribute="createdon" operator="last-seven-days" valueof="cr4cb_typeofwork" />
</filter>
</entity>
</fetch>

SECOND Query

<fetch aggregate="true" >
<entity name="audit" >
<attribute name="auditid" alias="AIUpdate" aggregate="count" />
<attribute name="userid" alias="UIUpdate" groupby="true" />
<filter type="and" >
<condition attribute="operation" operator="eq" value="2" />
<condition attribute="createdon" operator="on-or-before" value="2/28/2022" />
<condition attribute="createdon" operator="on-or-after" value="2/2/2022" />
</filter>
</entity>
</fetch>

fetchxml issue.txt

[rappen]

I don't know why it's wrong, but I have some ideas... If you run this at "Raw fetch result" it works.

Then it does a raw:

   var result = Service.Execute(new ExecuteFetchRequest() { FetchXml = fetch })

The code just sends the string fetchxml as is.

As usual, where it creates a new FetchExpression, so it is handled all in the SDK.

   var result = Service.RetrieveMultiple(new FetchExpression(fetch));

The converting looks ok to accept the query, but executing the query fails. Something when it is back on the server, and it can't convert it to SQL, it seems... So I'm sorry, but it's "not in my code"... 😕

If you find more info - I'm interested!

Created an issue from the error message in the tool, looks like this (if you have 1.2022.3.1 or later you will have this error handling, giving more info to the issues!) :

---

System.ServiceModel.FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>:
Encountered unsupported attribute name: 'useridname' when converting entity expression to SQL.

Server stack trace: 
- System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
- System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
- System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
- System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]: 
- Microsoft.Xrm.Tooling.Connector.CrmServiceClient.RetrieveMultiple(QueryBase query)
- Cinteros.Xrm.FetchXmlBuilder.FetchXmlBuilder.<>c__DisplayClass134_0.<RetrieveMultiple>b__0(BackgroundWorker worker, DoWorkEventArgs eventargs)
- System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
- System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

---

<fetch aggregate="true">
  <entity name="audit">
    <attribute name="auditid" alias="AIUpdate" aggregate="count" />
    <attribute name="userid" alias="UIUpdate" groupby="true" />
    <filter type="and">
      <condition attribute="operation" operator="eq" value="2" />
      <condition attribute="createdon" operator="on-or-before" value="2/28/2022" />
      <condition attribute="createdon" operator="on-or-after" value="2/2/2022" />
    </filter>
  </entity>
</fetch>

---

• FetchXML Builder Version: 1.2022.3.1
• DB Version: 9.2.22015.128
• Deployment: Online