locked
CRM 2011 - Need a QueryExpression expert to modify my query RRS feed

  • Question

  • I am new to queryexpression and need help with modifying my query expression below:

    newCondition1 = new ConditionExpression()
    {

           AttributeName = "record1id",
           Operator = ConditionOperator.Equal,
           Values = { contactid }
    };

    newCondition2 = new ConditionExpression("record1id", ConditionOperator.In, list.Cast<object>().ToArray());
                            
    FilterExpression newFilter = new FilterExpression()
    {
           FilterOperator = LogicalOperator.Or,
           Conditions = { newCondition1, newCondition2 }
    ;
    query.Criteria.Filters.Add(newFilter);

    The above query works. What I need to do here is to add a couple of And conditions to the newCondition 1 and newCondition2 which is to check for the statecode to make sure it is 0 (retrieve active records only).

    How do I add the And without causing issue with the Or specified above.

    This site is great and I have received wonderful helps before. I hope someone (a query expression expert) look at this and provide the help/answer.

    Thank you very much for your time.

    Monday, August 5, 2013 7:58 PM

Answers

  • Hi,

        You can perform this by using additional ConditionExpression and FilterExpression. For something like following:

    Select Attribute1, Attribute2, Attribute3 from Entity_A where
    (Attribute1=value1 OR Attribute1=value2)
    AND
    Attribute 2=value3
    AND
    Attribute3=value4

    Code would look like this:

    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());
    } 

    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"
    Monday, August 5, 2013 10:44 PM
  • Minal is on the right track though using a filter within a filter.

    The filter you're after should look something like this (excuse any typos/errors - I wrote this in notepad on my surface because VS is taking too long to install):

    FilterExpression newFilter = new FilterExpression()
    {
        FilterOperator = LogicalOperator.Or,
        Filters =
        {
            new FilterExpression()
            {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    newCondition1a,
                    newCondition1b
                }
            },
            new FilterExpression()
            {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    newCondition2a,
                    newCondition2b
                }
            }
        }
    };
    
    query.Criteria.AddFilter(newFilter);

    P.S. I spaced it all out as I think it's easier to read/understand this way.

    Hope that helps (and compiles)

    Paul


    If my response helped you find your answer please show your thanks by taking the time to "Mark As Answer" and "Vote As Helpful".

    Twitter LinkedIn Facebook Blog Magnetism


    • Edited by Paul Nieuwelaar Tuesday, August 6, 2013 9:43 AM vs installed, compiled code
    • Marked as answer by Homayoun Niazi Tuesday, August 6, 2013 3:52 PM
    Tuesday, August 6, 2013 8:20 AM

All replies

  • Hi,

        You can perform this by using additional ConditionExpression and FilterExpression. For something like following:

    Select Attribute1, Attribute2, Attribute3 from Entity_A where
    (Attribute1=value1 OR Attribute1=value2)
    AND
    Attribute 2=value3
    AND
    Attribute3=value4

    Code would look like this:

    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());
    } 

    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"
    Monday, August 5, 2013 10:44 PM
  • Hi Minal,

    Thank you very much for your response.

    It seems like what I was looking for (got it resolved though in another way) would look like this:

    (Attribute1=value1 AND Attribute2=value2)
    OR
    (Attribute 1=value3 AND Attribute2 = value2)

    How would you provide the query expression condition for this?

    Tuesday, August 6, 2013 12:11 AM
  • Minal is on the right track though using a filter within a filter.

    The filter you're after should look something like this (excuse any typos/errors - I wrote this in notepad on my surface because VS is taking too long to install):

    FilterExpression newFilter = new FilterExpression()
    {
        FilterOperator = LogicalOperator.Or,
        Filters =
        {
            new FilterExpression()
            {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    newCondition1a,
                    newCondition1b
                }
            },
            new FilterExpression()
            {
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    newCondition2a,
                    newCondition2b
                }
            }
        }
    };
    
    query.Criteria.AddFilter(newFilter);

    P.S. I spaced it all out as I think it's easier to read/understand this way.

    Hope that helps (and compiles)

    Paul


    If my response helped you find your answer please show your thanks by taking the time to "Mark As Answer" and "Vote As Helpful".

    Twitter LinkedIn Facebook Blog Magnetism


    • Edited by Paul Nieuwelaar Tuesday, August 6, 2013 9:43 AM vs installed, compiled code
    • Marked as answer by Homayoun Niazi Tuesday, August 6, 2013 3:52 PM
    Tuesday, August 6, 2013 8:20 AM
  • Hi,

    You can use the below code to achieve your requirement.

    ConditionExpression cond1 =new ConditionExpression();

    cond1.AttributeName = "new_attribute1";

    cond1.Operator = ConditionOperator.Equal;

    cond1.Values.Add("Value1");

    ConditionExpression cond2 = new ConditionExpression();

    cond2.AttributeName = "new_attribute2";

    cond2.Operator = ConditionOperator.Equal;

    cond2.Values.Add("Value2");

    ConditionExpression cond3 = new ConditionExpression();

    cond3.AttributeName = "new_attribute3";

    cond3.Operator = ConditionOperator.Equal;

    cond3.Values.Add("Value3");

    ConditionExpression cond4 = new ConditionExpression();

    cond4.AttributeName = "new_attribute4";

    cond4.Operator = ConditionOperator.Equal;

    cond4.Values.Add("Value4");

    FilterExpression filter1=new FilterExpression();

    filter1.AddCondition(cond1);

    filter1.AddCondition(cond2);

    filter1.FilterOperator = LogicalOperator.And;

    FilterExpression filter2 = new FilterExpression();

    filter2.AddCondition(cond3);

    filter2.AddCondition(cond4);

    filter2.FilterOperator = LogicalOperator.And;

    FilterExpression filterMain = new FilterExpression();

    filterMain.AddFilter(filter1);

    filterMain.AddFilter(filter2);

    filterMain.FilterOperator=LogicalOperator.Or;

    Thanks!

    Kalim Khan

    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Tuesday, August 6, 2013 10:07 AM
  • Thank you all very much for your responses.

    Like Paul said, I liked the sub filter use of Minal's queryexpression but overall, to me, Paul's solution was easier to read/understand and pretty much what I was looking for.

    Thank you very much, Paul.

    Tuesday, August 6, 2013 3:58 PM