Querying CRM with conditions and filters

07Jul10

On more than one occasion I’ve had to assemble a complex query for retrieving CRM entities. Unfortunately, the QueryExpression API can be pretty confusing. I’ve actually written several query APIs for other projects myself, and this one still trips me up sometimes.

The basis for my confusion, and probably the confusion of many others, is that the naming is slightly confusing and it is not obvious at first blush why there is a need for both FilterExpression and ConditionExpression.

To clear up the naming convention, there are two types that are used in the construction of the selection condition: FilterExpression and ConditionExpression. FilterExpression is used to express boolean conjunctions and disjunctions (and and or), and ConditionExpression is used to represent (in)equality, and set membership. There is an inconsistency in naming of the field that holds the filters at the top level however, there being a QueryExpression.Criteria property rather than QueryExpression.Filters.

One way to look at a complex query is that the criteria consist of a tree of FilterExpressions with ConditionExpressions as the terminals. That is, FilterExpression has a collection of other FilterExpressions, whereas ConditionExpressions do not contain any further expressions.

As an example, if we wanted to express the clause:

( A = 1 and ( B = 2 or C = 3 ) ) 

we would need two FilterExpressions and three ConditionExpressions. It is easy to see how to construct the ConditionExpressions – we simply create three instances using ConditionOperator.Equal as the operator. For example:

ConditionExpression conditionA = new ConditionExpression();
conditionA.AttributeName = "A";
conditionA.Operator = ConditionOperator.Equal;
conditionA.Values = new int[] { 1 }; 

Notice that the value is placed in an array of integers. ConditionExpression.Values is a collection since we support the possibility of testing for set membership, thus necessitating the use of a collection for the possible values.

Next we create two filters, one each for the ‘and’ and ‘or’ expressions:

FilterExpression andFilter = new FilterExpression();
andFilter.FilterOperator = LogicalOperator.And;

FilterExpression orFilter = new FilterExpression();
childFilter.FilterOperator = LogicalOperator.Or;

// Now establish the filter hierarchy
andFilter.Filters = new FilterExpression[] { orFilter };

Once the filters are set up, we can apply the conditionals:

andFilter.Conditions = new ConditionExpression[] { conditionA };
orFilter.Conditions = new ConditionExpression[] { conditionB, conditionC };

I have organized the code slightly differently than most of the code samples available from Microsoft. I think that looking at the filters as a tree structure makes things clearer. Notice that a ConditionExpression is given the same consideration that a FilterExpression is given when evaluating the entire expression — the boolean operator is applied to conditionA and the result of the evaluation of orFilter.

Somehow, I think that this API could have been simpler. I might explore wrapping this API in something a little less confusing, perhaps at the expense of supporting less frequently used operations. Hopefully this gives you a slightly different perspective on constructing Criteria expressions in CRM. I’ll cover (the also confusing) LinkEntity in an upcoming post.

Advertisements


3 Responses to “Querying CRM with conditions and filters”

  1. 1 Xitij Thool

    Nice Post , can u tell us in what manner we will add these FilterCriteria in the QueryExpression?

    • 2 Fraser

      When creating the QueryExpression, the Criteria only accepts one filter. How do you plan on getting both your FilterExpression into one Query?

      • 3 newcome

        @fraser – yes, you may only supply one FilterExpression to the QueryExpression, but what we have done in the code sample above is create a hierarchy of filters, the topmost “root” being the “and” expression. Check the line:

        andFilter.Filters = new FilterExpression[] { orFilter };
        

        where we set up the hierarchy. Once that is set up all we need is the root expression to give to QueryExpression.Critera

        Hope this makes sense.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: