Compare two columns in QueryExpression in Dynamics 365/ CDS SDK

Follow my blog for more interesting topics on Dynamics 365, Portals and Power Platform. For training and consulting, write to us at info@xrmforyou.com

A long awaited feature which has been asked by customers for over a decade has been finally introduced in the platform. And today I referred a blog post by my friend and fellow MVP Nishant Rana on how you can compare two columns in fetchxml.
In this blog article I am going to discuss on how we can compare two columns in QueryExpression.

To use the SDK API in your custom code make sure you install the latest SDK assemblies from Nuget. I have installed latest version 9.0.2.26 of Microsoft.CrmSdk.CoreAssemblies in my project. Prepare a query expression to filter the necessary set of records from any entity. Here I am retrieving data from contact. You can see below is the new construct available to compare two attributes

ConditionExpression(<attribute_name>, <operator>, <flag>, <value>);

<attribute_name> is the attribute name with which you will make a comparison
<operator> – Equal, Not Equal, all conditional operators
<flag> – true/false. True compares attribute values. False means will return all records for one attribute and return values that match the provided value.
<value> – specify the other column attribute name.
Here is my final query expression construct to filter records that matches the lastname column and address1_city column. Absolutely non-sense comparison but I guess you get the idea.

Here you go with code sample:

var query = new QueryExpression("contact");
query.ColumnSet.AddColumns("firstname", "lastname");
query.Criteria.Conditions.Add(new ConditionExpression("lastname", ConditionOperator.Equal, true, "address1_city"));
var results = client.RetrieveMultiple(query);

What this query does here is like it will return all the contacts where lastname matches with address1_city. Nice and simple isn’t.
In this we compare other attribute types as well.
Optionsets query –

query.Criteria.Conditions.Add(new ConditionExpression("gendercode", ConditionOperator.Equal, true, "familystatuscode"));

GenderCode and FamilyStatusCode are two optionset attributes if we compare these two attributes the comparision happens with optionset attribute values not with the text of attributes.
Datetime query –
query.Criteria.Conditions.Add(new ConditionExpression(“createdon”, ConditionOperator.Equal, true, “modifiedon”));
Lookup query
query.Criteria.Conditions.Add(new ConditionExpression(“createdby”, ConditionOperator.Equal, true, “ownerid”));
All the above queries compares columns which contains values. Null attribute values are ignored and they will not be picked up in the result query.

You might also like the below posts

Hope this helps!!!

Debajit Dutta
(Business Solutions MVP)