Use the ConditionExpression class

In Microsoft Dataverse, you can use the ConditionExpression class to compare a table column to a value or set of values by using an operator, such as "equal to" or "greater than". The ConditionExpression class lets you pass condition expressions as parameters to other classes, such as QueryExpression and FilterExpression.

The following table lists the properties you can set to create a condition using the ConditionExpression class.

Property	Description
AttributeName	Specifies the logical name of the column in the condition expression.
Operator	Specifies the condition operator. This is set by using the ConditionOperator enumeration.
Values	Specifies the values of the column.

When using the AddCondition(ConditionExpression) method (or the constructor for ConditionExpression), it's important to understand whether the array is being added as multiple values or as an array.

The following code example shows two different outcomes depending on how the array is used.

string[] values = new string[] { "Value1", "Value2" };  
ConditionExpression c = new ConditionExpression("name", ConditionOperator.In, values);  
Console.WriteLine(c.Values.Count); //This will output 2   
string[] values = new string[] { "Value1", "Value2" }object value = values;  
ConditionExpression c = new ConditionExpression("name", ConditionOperator.In, value);  
Console.WriteLine(c.Values.Count); //This will output 1  
  

In some cases, it is necessary to cast to either object[] or object, depending on the desired behavior.

When you create a condition that compares a column value to an enumeration, such as a state code, you must use the ToString method to convert the value to a string.

Example: use the ConditionExpression class

The following code example shows how to use the ConditionExpression class.

  
//  Query using ConditionExpression    
ConditionExpression condition1 = new ConditionExpression();  
condition1.AttributeName = "lastname";    
condition1.Operator = ConditionOperator.Equal;    
condition1.Values.Add("Brown");                    
FilterExpression filter1 = new FilterExpression();    
filter1.Conditions.Add(condition1);    
QueryExpression query = new QueryExpression("contact");    
query.ColumnSet.AddColumns("firstname", "lastname");    
query.Criteria.AddFilter(filter1);    
EntityCollection result1 = _serviceProxy.RetrieveMultiple(query);    
Console.WriteLine();    
Console.WriteLine("Query using Query Expression with ConditionExpression and FilterExpression");    
Console.WriteLine("---------------------------------------");    
foreach (var a in result1.Entities)    
{  
      Console.WriteLine("Name: " + a.Attributes["firstname"] + " " + a.Attributes["lastname"]);    
}    
Console.WriteLine("---------------------------------------");  

Example: test for inactive state

The following code example shows how to use the ConditionExpression class to test for the inactive state.

  
ConditionExpression condition3 = new ConditionExpression();  
condition3.AttributeName = "statecode";  
condition3.Operator = ConditionOperator.Equal;  
condition3.Values.Add(AccountState.Active);  
  

Column comparison

The following example shows how to compare columns with the ConditionExpression class:

public static EntityCollection ColumnComparisonExample(IOrganizationService service)
{
    QueryExpression query = new("contact")
    {
        Criteria = new FilterExpression(LogicalOperator.And)
        {
            Conditions = {
                {
                    new ConditionExpression(){
                        AttributeName = "firstname",
                        Operator = ConditionOperator.Equal,
                        CompareColumns = true,
                        Values = {
                            {"lastname"}
                        }
                    }
                }
            }
        }
    };
    return service.RetrieveMultiple(query);
}

By passing in true as the value for the CompareColumns property, the value is treated as the name of the second column to compare the values in attributeName to. The default value is false.

You can also set this property using the optional compareColumns ConditionExpression constructor. The following example creates a condition to return only records where the first and last names are the same,

var expression = new ConditionExpression(
    attributeName: "firstname",
    conditionOperator: ConditionOperator.Equal,
    compareColumns: true,
    value: "lastname");

Leave out the compareColumns parameter, to create a condition to return only records where the first name is John.

var expression = new ConditionExpression(
    attributeName: "firstname",
    conditionOperator: ConditionOperator.Equal,
    value: "John");

Use Wildcard characters in conditions using string values

You can use wildcard characters when you construct queries using conditions on string values. More information: Use wildcard characters in conditions for string values

See also

Building Queries
Build Queries with QueryExpression
Use the FilterExpression Class
ConditionExpression