Making an dynamic <And><Or> Caml Query

Question

I am Trying to write an Dynamic Caml Query.

I did it like This:

CamlQuery qry = new CamlQuery();
        string qryStatus = "";
        string qryCategory = "";

        foreach (RiskStatus sta in filter.Status)
        {
            if (sta == null)
                continue;

            qryStatus = "<Eq><FieldRef Name='Status' /><Value Type='Text'>" + sta.Name + "</Value></Eq>" + qryStatus;
        }

        if (filter.Status.Length >= 2)
        {
            qryStatus = "<Or>" + qryStatus + "</Or>";
        }

        foreach (RiskCategory ctg in filter.Categories)
        {
            if (ctg == null)
                continue;

            qryCategory = "<Eq><FieldRef Name='Category' /><Value Type='Text'>" + ctg.Name + "</Value></Eq>" + qryCategory;
        }

        if (filter.Categories.Length >= 2)
        {
            qryCategory = "<Or>" + qryCategory + "</Or>";
        }

        qry.ViewXml = "<View Scope='RecursiveAll'>"
                    + "<Query>"
                    + "   <Where>"
                    + "     <And>"
                    + qryStatus
                    + qryCategory
                    + "     </And>"
                    + "   </Where>"
                    + "</Query>"
                    + "</View>";
        return qry;

I use this Test values:

CategoryArray[0] = new TestCategory { Name = "(2) TestCategory2" };
StatusArray[0] = new TestStatus { Name = "(1) TestStatus1" };

CategoryArray[1] = new TestCategory { Name = "(3) TestCategory3" };
StatusArray[1] = new TestStatus { Name = "(2) TestStatus2" };

This is the Result I get:

<View Scope='RecursiveAll'>
<Query>   
<Where>     
    <And>
        <Or>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>
        </Or>
        <Or>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(3) TestCategory3</Value></Eq>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(2) TestCategory2</Value></Eq>
        </Or>     
    </And>   
</Where>
</Query>
</View>

It works fine But if I add a third value I get the exception "Cannot complete this action.\n\nPlease try again."

Added Value:

  StatusArray[2] = new TestStatus { Name = "(3) Status3" };

Result:

<View Scope='RecursiveAll'>
<Query>   
<Where>     
    <And>
        <Or>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>
            <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus3</Value></Eq>
        </Or>
        <Or>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(3) TestCategory3</Value></Eq>
            <Eq><FieldRef Name='Category' /><Value Type='Text'>(2) TestCategory2</Value></Eq>
        </Or>     
    </And>

What am I doing wrong? (This is my first time working with Caml Query)

There is one problem in your last caml query. You have to write</Eq> instead of </eq>. And you can not add 3 fieldref in OR tag. — Dikesh Gandhi
– Dikesh Gandhi, Commented Jun 9, 2016 at 11:02
So how is it suppose to look like if I want to do it with 3 or more? — user53548
– user53548, Commented Jun 9, 2016 at 11:13
You can get some reference from here -stackoverflow.com/questions/6203821/… — Dikesh Gandhi
– Dikesh Gandhi, Commented Jun 9, 2016 at 11:22
doing it like that will make it a lot complicated... I will try. Thanks. — user53548
– user53548, Commented Jun 9, 2016 at 11:48

Gaurav Vijh · Accepted Answer · 2016-06-15 07:51:07Z

I had similar kind of requirement, below is the code which I have used previously:

enum MergeType { Or, And };

private static string MergeCAMLConditions(List<string> conditions, MergeType type)
{
    if (conditions.Count == 0) return "";

    string typeStart = (type == MergeType.And ? "<And>" : "<Or>");
    string typeEnd = (type == MergeType.And ? "</And>" : "</Or>");

    // Build hierarchical structure
    while (conditions.Count >= 2)
    {
        List<string> complexConditions = new List<string>();

        for (int i = 0; i < conditions.Count; i+=2)
        {
            if (conditions.Count == i + 1) // Only one condition left
                complexConditions.Add(conditions[i]);
            else // Two condotions - merge
                complexConditions.Add(typeStart + conditions[i] + conditions[i + 1] + typeEnd);
            }

            conditions = complexConditions;
        }

        return conditions[0];
    }

You can call it using:

List<string> conditions = new List<string>();
conditions.Add("<Eq><FieldRef Name='Col1' /><Value Type='Text'>value1</Value></Eq>");
conditions.Add("<Eq><FieldRef Name='Col2' /><Value Type='Text'>value2</Value></Eq>");;

string merged = MergeCAMLConditions(conditions, MergeType.And);

Charles · Accepted Answer · 2016-06-09 11:14:32Z

0

CAML queries only accept two conditions for logical operators. So you can do

<And>
   <Eq>...</Eq>
   <Eq>...</Eq>
</And>

but not

<And>
   <Eq>...</Eq>
   <Eq>...</Eq>
   <Eq>...</Eq>
</And>

Solution is using two s:

<And>
  <And>
   <Eq>...</Eq>
   <Eq>...</Eq>
  </And>
   <Eq>...</Eq>
</And>

answered Jun 9, 2016 at 11:14

Charles

4753 silver badges11 bronze badges

Add a comment |

Gaurravs · Accepted Answer · 2016-06-09 11:48:10Z

While writing CAML query, you must understand the architecture how it works.

It is pretty simple. You just have to do logical AND/OR operators.

Any operator must have 2 conditions to combine and test.

SCENARIO 1 : This is 1 Operator use with 2 conditions

 <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
 </Or>

SCENARIO 2 : This is 2 Operators use with 2 conditions From scenario 1 we got operator now along with that we will add another operator.

<And>
    <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
    </Or>
    <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>                  
</And>

Here if you notice, <OR> is having two conditions inside it. and that makes 1 condition for outer <And>, so we added another condition block parallel to <OR> operator. Hence 2 conditions are available for <AND>.

Likewise you can build n number of nested conditions.

PS: If you have only 1 condition, it will not require any operator.

Stack Exchange Network

Making an dynamic <And><Or> Caml Query

3 Answers 3

Your Answer

Hot Network Questions

Making an dynamic <And><Or> Caml Query

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions