Edit

Use the FilterExpression class

  • Article

In Microsoft Dataverse, you can use the FilterExpression class to build a query that expresses multiple conditions. For example, you can create a query expression that is the equivalent of a SQL statement such as ([FirstName] = 'Joe' OR [FirstName] = 'John') AND [City] = 'Redmond'.

The following table lists the properties for the FilterExpression class.

Property Description
Conditions Gets or sets condition expressions that include table columns, condition operators, and column values.
FilterOperator Gets or sets logical AND/OR filter operators. This is set by using the LogicalOperator enumeration.
Filters Gets or sets a hierarchy of condition and logical filter expressions that filter the results of the query.
IsQuickFindFilter Gets or sets a value that indicates whether the expression is part of a quick find query.

The FilterExpression class also includes several helper methods that make it easier to create queries. The FilterExpression.ConditionExpression method adds a ConditionExpression to the Conditions property for the FilterExpression, reducing the amount of code needed to construct the condition expression. The AddFilter.LogicalOperator method adds a new filter to the Filters property of the FilterExpression class.

Filter expression example

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

QueryExpression query = new QueryExpression("contact");   
query.ColumnSet.AddColumns("firstname", "lastname", "address1_city");   
  
query.Criteria = new FilterExpression();   
query.Criteria.AddCondition("address1_city", ConditionOperator.Equal, "Redmond");   
  
FilterExpression childFilter = query.Criteria.AddFilter(LogicalOperator.Or);   
childFilter.AddCondition("lastname", ConditionOperator.Equal, "Tharpe");   
childFilter.AddCondition("lastname", ConditionOperator.Equal, "Brown");   
  
// Pass query to service proxy   
EntityCollection results = _serviceProxy.RetrieveMultiple(query);   
Console.WriteLine();   
Console.WriteLine("Query using QE with multiple conditions and filters");   
Console.WriteLine("---------------------------------------");   
  
// Print results   
foreach (var a in results.Entities)   
{   
Console.WriteLine("Name: {0} {1}", a.GetAttributeValue<string>("firstname"), a.GetAttributeValue<string>("lastname"));   
Console.WriteLine("City: {0}", a.GetAttributeValue<string>("address1_city"));   
}   
Console.WriteLine("---------------------------------------");

About the IsQuickFindFilter property

You can use the FilterExpression.IsQuickFindFilter property, that is analogous to the isquickfindfields column that exists on the filter node in Fetch XML. When a Fetch query is saved, this is stored in the SavedQuery and UserQuery tables' IsQuickFind properties. The IsQuickFindFilter property was added to provide consistency between Query Expression and Fetch XML queries.

The following rules apply to the IsQuickFindFilter property:

  • This field can only be set to true for filter expressions with a logical operator of type LogicalOperator.Or. If it is set for expressions with a logical operator of type LogicalOperator.And, the IsQuickFindFilter property is ignored.

  • Only one filter expression in a filter expression hierarchy can be set with IsQuickFindFilter = true. If more than one is found, an exception is thrown.

  • If a filter expression has IsQuickFindFilter set to true, it cannot have any child filter expression properties, it can only have ConditionExpression properties. If you add a child filter expression, an exception is thrown.

  • All condition expressions related to a filter expression with IsQuickFindFilter set to true must be single non-null value conditions. In other words, given that a condition is made up of column, operator, and value, only conditions where the value property is a single value that is not null are supported. In addition, the only condition operators supported on these condition expressions are ones that work with a single value that is not null. If a null value or multiple values are detected, an exception is thrown.

See also

Building Queries with QueryExpression
Use a left outer join in QueryExpression to query for records "not in"
Using the ConditionExpression Class
FilterExpression