Fetch XML–Query to compare between columns–Dynamics 365

We have many limitations with Fetch Query compare to SQL Query. One such limitation is compare between columns while retrieving data. CRM Fetch query now supporting comparison between columns with limitations.

Supported Operations:

• Equal
• NotEqual
• GreaterThan
• GreaterEqual
• LessThan
• LessEqual

Limitations:

• You can only compare columns within a single entity
• Only two columns may be compared at a time.
• Multi-value condition like “IN” operators are not supported
• Extended condition operators are not supported (i.e., “creditlimit > spends+1000”).
• Incompatible attribute comparison is not supported. For example, “int vs. int” attributes is a valid comparison but “int vs. string” attributes is not a valid comparison.

Example Fetch XML:

<fetch>
   <entity name=’contact’ >
     <attribute name=’firstname’ />
     <filter>
       <condition attribute=’firstname’ operator=’eq’ valueof=’lastname’/>
     </filter>
   </entity>
</fetch>

Web API:

https://<CRMServer>/contacts?$select=firstname&$filter=firstname eq lastname

SDK:

public ConditionExpression
(
   string attributeName,
   ConditionOperator conditionOperator,
   bool compareColumns,
   object value
)

public ConditionExpression
(
   string attributeName,
   ConditionOperator conditionOperator,
   bool compareColumns,
   object[] values
)

If compareColumns is passed in as true, it will compare the two attributes and return all records that match.

If a false value is passed, it will return all records for one attribute and return values that match the provided value.