편집

다음을 통해 공유

Filter rows using QueryExpression

  • 아티클

To set conditions on the rows of data to return, set the QueryExpression.Criteria or LinkEntity.LinkCriteria properties to an instance of the FilterExpression class.

To set the conditions, add one or more ConditionExpression class instances to the FilterExpression.Conditions collection.

The FilterExpression.FilterOperator property uses the LogicalOperator enum value to specify whether all (And) or any (Or) of the conditions must be met. The default is And.

Use the FilterExpression.Filters property to add additional filter criteria that can be combined using And or Or.

For each condition, set the ConditionExpression.Operator property to one of the ConditionOperator enum members to specify how to evaluate a row column value.

For example, the following query returns account records where address1_city equals 'Redmond'. It uses LogicalOperator.And with ConditionOperator.Equal.

QueryExpression query = new("account")
{
   TopCount = 5,
   ColumnSet = new ColumnSet("name"),
   Criteria = new FilterExpression(LogicalOperator.And)
   {
      Conditions = {
         new ConditionExpression(
            attributeName:"address1_city",
            conditionOperator: ConditionOperator.Equal,
            value:"Redmond")
      }
   }
};

This query returns account records where address1_city equals 'Redmond', 'Seattle', or 'Bellevue'. It uses LogicalOperator.Or with three ConditionExpression instances created using the ConditionExpression(String, String, ConditionOperator, Object) constructor that each use ConditionOperator.Equal.

QueryExpression query = new("account")
{
   TopCount = 5,
   ColumnSet = new ColumnSet("name"),
   Criteria = new FilterExpression(LogicalOperator.Or)
   {
      Conditions = {
         new ConditionExpression(
            attributeName:"address1_city",
            conditionOperator: ConditionOperator.Equal,
            value:"Redmond"),
         new ConditionExpression(
            attributeName:"address1_city",
            conditionOperator: ConditionOperator.Equal,
            value:"Seattle"),
         new ConditionExpression(
            attributeName:"address1_city",
            conditionOperator: ConditionOperator.Equal,
            value:"Bellevue")
      }
   }
};

The previous query can also be represented using ConditionOperator.In, with a single ConditionExpression. This condition is created using the ConditionExpression(String, ConditionOperator, Object[]) constructor that sets multiple values to compare to address1_city.

QueryExpression query = new("account")
{
   TopCount = 5,
   ColumnSet = new ColumnSet("name"),
   Criteria = new FilterExpression(LogicalOperator.Or)
   {
      Conditions = {
         new ConditionExpression(
            attributeName:"address1_city",
            conditionOperator: ConditionOperator.In,
            values: new string[] { "Redmond", "Seattle", "Bellevue" })
      }
   }
};

Operator parameters

Operators can require no parameters, a single parameter, or multiple parameters. The operator determines how you set the value to evaluate.

No parameters

Some operators don't require any parameter values.

For example, you can use the ConditionExpression(String, ConditionOperator) constructor with ConditionOperator.EqualUserId to evaluate any unique identifier to determine if it matches the calling user's ID.

new ConditionExpression(
   attributeName:"ownerid",
   conditionOperator: ConditionOperator.EqualUserId)

Single parameter

When an operator requires a single parameter, use the ConditionExpression(String, ConditionOperator, Object) constructor to specify the value parameter to the value to evaluate.

For example, you can use ConditionOperator.Equal to evaluate the statecode choice column value of a record.

new ConditionExpression(
    attributeName:"statecode",
    conditionOperator: ConditionOperator.Equal,
    value: 0)

Multiple parameters

When an operator requires multiple parameters, use the ConditionExpression(String, ConditionOperator, Object[]) constructor values parameter to specify the values to evaluate.

For example, you can use ConditionOperator.Between to evaluate a number to determine if it is between a set of values.

new ConditionExpression(
    attributeName:"numberofemployees",
    conditionOperator: ConditionOperator.Between,
    values: new object[] {6,20})

Filters on LinkEntity

When you use the LinkEntity.LinkCriteria property to apply a filter, the filter will be applied with the join unless you configure the filter to be applied in the QueryExpression.Criteria.

When the LinkEntity.JoinOperator property uses JoinOperator.LeftOuter, you might want the filter to be applied in the QueryExpression.Criteria by setting the ConditionExpression.EntityName property value. If you're using the LinkEntity.EntityAlias property, use that value to set the ConditionExpression.EntityName property. Otherwise, set the ConditionExpression.EntityName property to the LinkEntity.LinkFromEntityName property value. Learn more about finding records not in a set using

For example, the following query returns contacts without a parent account, or a parent account without a fax.

QueryExpression query = new("contact") { 
     ColumnSet = new ColumnSet("fullname"),
     Criteria = new FilterExpression(LogicalOperator.And) { 
         Conditions = { 
            new ConditionExpression(){ 
                AttributeName = "fax",
                EntityName = "a",
                Operator = ConditionOperator.Null
            }
         }
     },
     LinkEntities = { 
        new LinkEntity(
            linkFromEntityName:"contact",
            linkToEntityName:"account",
            linkFromAttributeName:"parentcustomerid",
            linkToAttributeName:"accountid",
            joinOperator: JoinOperator.LeftOuter){
           EntityAlias = "a"
        }
    }
};

Filter on column values in the same row

You can create filters that compare columns on values in the same row using by setting the ConditionExpression.CompareColumns property using constructors like ConditionExpression(String, String, ConditionOperator, Boolean, Object) that allow you to set this using the compareColumns parameter. When CompareColumns is true, the value represents the name of the other column.

For example, if you want to find any contact records where the firstname column value matches the lastname column value, you can use this query:

QueryExpression query = new("contact")
{
      ColumnSet = new ColumnSet("firstname"),
      Criteria = new FilterExpression(LogicalOperator.And)
      {
         Conditions = {
            new ConditionExpression(
                  attributeName:"firstname",
                  conditionOperator: ConditionOperator.Equal,
                  compareColumns: true, 
                  value: "lastname")
         }
      }
};

Limitations on column comparison filters

There are limitations on these kinds of filters:

  • You can only use these ConditionOperator enum operators:

    Operator Description
    Equal The two values are equal.
    NotEqual The two values are not equal.
    GreaterThan The value is greater than the compared value.
    GreaterEqual The value is greater than or equal to the compared value.
    LessThan The value is less than the compared value.
    LessEqual The value is less than or equal to the compared value.

  • Only two columns can be compared at a time

  • Extended condition operations aren't supported. For example: value: "amount" + 100

  • The columns must be the same type. For example: You can't compare a string value with a number value

To filter on values in related records without returning those values, use the FilterExpression.AnyAllFilterLinkEntity property with a LinkEntity instance where the LinkEntity.JoinOperator uses one of the following JoinOperator enum members:

Name Description
Any Restricts results to parent rows with any matching rows in the linked entity.
NotAny Restricts results to parent rows with no matching rows in the linked entity.
All Restricts results to parent rows where rows with matching from column value exist in the link entity but none of those matching rows satisfy the additional filters defined for this link entity. You need to invert the additional filters to find parent rows where every matching link entity row satisfies some additional criteria.
NotAll Restricts results to parent rows with any matching rows in the linked entity. This link type is equivalent to any despite the name.

When you use these JoinOperator member values with a LinkEntity specified by the FilterExpression.AnyAllFilterLinkEntity, these filters are child conditions following the behavior defined by the FilterExpression.FilterOperator (And/Or).

Filters using these JoinOperator member values return the parent row at most once even if multiple matching rows exist in the LinkEntity. Any columns specified in the LinkEntity.Columns property will be ignored.

The following examples demonstrate filtering on values of related records. These examples include the equivalent SQL statements to help explain the behavior.

Or filter with JoinOperator.Any

This query uses a FilterExpression with the FilterOperator property set to LogicalOperator.Or and a AnyAllFilterLinkEntity property set with a LinkEntity that has the JoinOperator property set to JoinOperator.Any. This query will return contact records that are either:

var query = new QueryExpression("contact")
{
    ColumnSet = new ColumnSet("fullname"),
    Criteria = new FilterExpression(filterOperator: LogicalOperator.Or)
    {
        AnyAllFilterLinkEntity = new LinkEntity(
            linkFromEntityName: "contact",
            linkToEntityName: "account",
            linkFromAttributeName: "contactid",
            linkToAttributeName: "primarycontactid",
            joinOperator: JoinOperator.Any)
        {
            LinkCriteria = new FilterExpression(filterOperator: LogicalOperator.And)
            {
                Conditions = {
                    new ConditionExpression(
                        attributeName: "name",
                        conditionOperator: ConditionOperator.Equal,
                        value: "Contoso")
                }
            }
        },
        Conditions = {
            new ConditionExpression(
                attributeName:"statecode",
                conditionOperator: ConditionOperator.Equal,
                value: 1)
        }
    }
};

JoinOperator.NotAny

This query uses JoinOperator.NotAny to return records from the contact table that are not referenced by the PrimaryContactId lookup column of any account record that has its Name column equal to 'Contoso'. The contact record might still be referenced by account records with other name column values.

var query = new QueryExpression("contact")
{
      ColumnSet = new ColumnSet("fullname"),
      Criteria = new FilterExpression(filterOperator: LogicalOperator.And)
      {
         AnyAllFilterLinkEntity = new LinkEntity(
            linkFromEntityName: "contact",
            linkToEntityName: "account",
            linkFromAttributeName: "contactid",
            linkToAttributeName: "primarycontactid",
            joinOperator: JoinOperator.NotAny)
         {
            LinkCriteria = new FilterExpression(filterOperator: LogicalOperator.And)
            {
                  Conditions = {
                     new ConditionExpression(
                        attributeName: "name",
                        conditionOperator: ConditionOperator.Equal,
                        value: "Contoso")
                  }
            }
         }
      }
};

JoinOperator.NotAll

Note

The meaning of JoinOperator.All and JoinOperator.NotAll is the opposite of what the names might imply, and they are typically used with inverted filters:

  • JoinOperator.NotAll is equivalent to JoinOperator.Any and returns parent records that have related table records matching the filters.
  • JoinOperator.All returns parent records when some related entity records with a matching LinkFromAttributeName column value exist but none of those link entity rows satisfy the additional filters defined inside of the LinkEntity.

This query uses JoinOperator.NotAll to to return records from the contact table that are referenced by the PrimaryContactId lookup column of at least one account record that has its Name column equal to 'Contoso':

var query = new QueryExpression("contact")
{
    ColumnSet = new ColumnSet("fullname"),
    Criteria = new FilterExpression(filterOperator: LogicalOperator.And)
    {
        AnyAllFilterLinkEntity = new LinkEntity(
            linkFromEntityName: "contact",
            linkToEntityName: "account",
            linkFromAttributeName: "contactid",
            linkToAttributeName: "primarycontactid",
            joinOperator: JoinOperator.NotAll)
        {
            LinkCriteria = new FilterExpression(filterOperator: LogicalOperator.And)
            {
                Conditions = {
                    new ConditionExpression(
                        attributeName: "name",
                        conditionOperator: ConditionOperator.Equal,
                        value: "Contoso")
                }
            }
        }
    }
};

JoinOperator.All

This query uses JoinOperator.All to return records from the contact table that are referenced by the PrimaryContactId lookup column of some account record, but none of those account records have their Name column equal to 'Contoso':

var query = new QueryExpression("contact")
{
    ColumnSet = new ColumnSet("fullname"),
    Criteria = new FilterExpression(filterOperator: LogicalOperator.And)
    {
        AnyAllFilterLinkEntity = new LinkEntity(
            linkFromEntityName: "contact",
            linkToEntityName: "account",
            linkFromAttributeName: "contactid",
            linkToAttributeName: "primarycontactid",
            joinOperator: JoinOperator.All)
        {
            LinkCriteria = new FilterExpression(filterOperator: LogicalOperator.And)
            {
                Conditions = {
                    new ConditionExpression(
                        attributeName: "name",
                        conditionOperator: ConditionOperator.Equal,
                        value: "Contoso")
                }
            }
        }
    }
};

Condition limits

You can include no more than 500 total ConditionExpression and LinkEntity instances in a query. Otherwise, you see this error:

Name: TooManyConditionsInQuery
Code: 0x8004430C
Number: -2147204340
Message: Number of conditions in query exceeded maximum limit.

You need to reduce the number of conditions to execute the query. You might be able to reduce the number of conditions by using the ConditionOperator.In that can be used with numbers, unique identifiers, and strings up to 850 characters.

Next steps

Learn how to page results.

Page results