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());
}
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());
}
Comments
Post a Comment