Tuesday, June 16, 2009

Combining OR and AND Conditions in Query Filters

Consider a scenario where you have to run a query to retrieve records from MS CRM with the following conditions:
Select Attribute1, Attribute2, Attribute3 from Entity_A where
(Attribute1=value1 OR Attribute1=value2)
AND
Attribute 2=value3
AND
Attribute3=value4

Since, FilterOperators permit only AND or OR Conditionoperators, you can use the FilterExpression.filters property to use a nested subquery for the OR clause.
The code for this will be as follows:

try
{
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { Attribute1, Attribute2, Attribute3 };

ConditionExpression attr1Val1= new ConditionExpression();
attr1Val1.AttributeName =Attribute1;
attr1Val1.Operator = ConditionOperator.Equal;
attr1Val1.Values = new object[] { value1};

ConditionExpression attr1Val2= new ConditionExpression();
attr1Val2.AttributeName = Attribute1;
attr1Val2.Operator = ConditionOperator.Equal;
attr1Val1.Values = new object[] { value2};

FilterExpression attr1Conditions= new FilterExpression();
attr1Conditions.Conditions = new ConditionExpression[] { attr1Val1, attr1Val2};
attr1Conditions.FilterOperator = LogicalOperator.Or;


ConditionExpression attr2= new ConditionExpression();
attr2.AttributeName = Attribute2;
attr2.Operator = ConditionOperator.Equal;
attr2.Values = new object[] { value3};

ConditionExpression attr3= new ConditionExpression();
attr3.AttributeName =Attribute3;
attr3.Operator = ConditionOperator.Equal;
attr3.Values = new object[] { value4};


FilterExpression filter = new FilterExpression();
filter.Conditions = new ConditionExpression[] { attr2, attr3 };
filter.Filters = new FilterExpression[] { attr1Conditions };
filter.FilterOperator = LogicalOperator.And;


QueryExpression query = new QueryExpression();
query.ColumnSet = cols;
query.EntityName = Entity_A;
query.Criteria = filter;

BusinessEntityCollection objBusinessEntities = service.RetrieveMultiple(query);

return objBusinessEntities;

}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}

No comments:

Post a Comment