Dynamically adding And to CAML query

Question

I have the following "base" query:

   <Where>
      <And>
         <Eq>
            <FieldRef Name='Field1' />
            <Value Type='Boolean'>1</Value>
         </Eq>
            <Eq>
               <FieldRef Name='Field 2' />
               <Value Type='Text'>TextQuery</Value>
         </Eq>        
      </And>
   </Where>
   <OrderBy>
      <FieldRef Name='OrderField' Ascending='True' />
   </OrderBy>

I would like to be able to dynamically add And blocks based on whether drop down boxes have a selected value. Example:

 strQuery = "";

 if (ddlNewField.SelectedIndex != 0)
 {
   strQuery += "<And>...</And>";
 }

The strQuery value would then be concatenated somewhere in the base query.

It obviously doesn't work. After a google search it looks like blocks need to be paired with only one other value or And block. I'm currently having coders-block.

How can I accomplish this?

Thanks in advance.

edit: I can change the base query if necessary

Answer 1

I've done similar like

string baseQry = "<and><eq>...</eq><eq>...</eq></and>"
string finalQry = baseQry;

and then if selected index is not 0, or wherever you want to add it

finalQry = "<and>" + finalQry + "<eq>new one here</eq></and>"

And running the above code will continually build out the statement. When you're ready to use it, add the where and orderby

finalQry = "<where>" + finalQry + "</where><orderby>...</orderby>"

HTH

Answer 2

There's a couple of of posts here and here but I've added support for the <In> query if more than one value for the same field is specified (hence why I don't use a Dictionary pair). My code currently only supports Text fields and doesn't support <Or>. Others have mentioned the REST API as an alternative approach.

Code - you can run this in LINQPad:

void Main()
{   
    var fieldValues = new List<FieldValue>();
    fieldValues.Add(new FieldValue("Completed", "No"));
    fieldValues.Add(new FieldValue("Completed", "Yes"));
    fieldValues.Add(new FieldValue("Alice", "1"));
    fieldValues.Add(new FieldValue("Bob", "2"));
    fieldValues.Add(new FieldValue("Charlie", "3"));

    ToCaml(fieldValues).Dump();
}

private const string CamlWhereEq = "<Eq><FieldRef Name='{0}' />{1}</Eq>";
private const string CamlWhereIn = "<In><FieldRef Name='{0}' /><Values>{1}</Values></In>";
private const string CamlValueText = "<Value Type='Text'>{0}</Value>";
private const string CamlAndOpen = "<And>{0}";
private const string CamlAndClose = "</And>";

public string ToCaml(List<FieldValue> fieldValues)
{
    string result = string.Empty;
    StringBuilder sbResult = new StringBuilder();   
    if (fieldValues.Count > 0)
    {
        if (fieldValues.Count == 1)
        {
            sbResult.AppendFormat(CamlWhereEq, fieldValues[0].Field, string.Format(CamlValueText, fieldValues[0].Value));
        }
        else        
        {
            // Check for duplicates 
            var groupedByField = from a in fieldValues
            group a by a.Field into g
            where g.Count() > 1
            select new { Key = g.Key, Item = g };

            var whereFilters = new List<string>();
            var otherFields = new List<FieldValue>();
            if (groupedByField.Count() > 0)
            {
                // Build "IN" query where multiple values specified             
                var groupedByFieldNames = groupedByField.Select(g => g.Key).ToList();                                                   
                otherFields = fieldValues.Where(a => !groupedByFieldNames.Contains(a.Field)).ToList();

                foreach (var g in groupedByField)
                {
                    StringBuilder sbFieldValues = new StringBuilder();
                    foreach (var i in g.Item)
                    {
                        sbFieldValues.AppendFormat(CamlValueText, i.Value);
                    }

                    whereFilters.Add(string.Format(CamlWhereIn, g.Key, sbFieldValues.ToString()));
                }
            }
            else            
            {
                otherFields = fieldValues;
            }

            foreach (var otherField in otherFields)
            {
                whereFilters.Add(string.Format(CamlWhereEq, otherField.Field, string.Format(CamlValueText, otherField.Value)));
            }

            if (whereFilters.Count == 1)
            {
                sbResult.AppendFormat(CamlAndOpen + CamlAndClose, whereFilters[0]);
            }
            else 
            {               
                sbResult.AppendFormat(CamlAndOpen + CamlAndClose, whereFilters[0] + whereFilters[1]);               
                for (int i = 2;i < whereFilters.Count;i++)
                {               
                    sbResult.Insert(0, string.Format(CamlAndOpen, whereFilters[i]));
                    sbResult.Append(CamlAndClose);                  
                }
            }
        }

        result = sbResult.ToString();
    }


    return result;
}

public class FieldValue
{
  public string Field { get; set; }
  public string Value { get; set; } 

  public FieldValue()
  {
  }

  public FieldValue(string field, string value)
  {
    Field = field;
    Value = value;
  }
}

Result:

<And><Eq><FieldRef Name='Charlie' /><Value Type='Text'>3</Value></Eq><And><Eq><FieldRef Name='Bob' /><Value Type='Text'>2</Value></Eq><And><In><FieldRef Name='Completed' /><Values><Value Type='Text'>No</Value><Value Type='Text'>Yes</Value></Values></In><Eq><FieldRef Name='Alice' /><Value Type='Text'>1</Value></Eq></And></And></And>