SQL in Equivalent in Caml

SQL IN equivalent in CAML

NO, you'll need to deal with nested OR tags; these are supported query instructions on CAML

Maybe CAML.NET can help you in your quest.

TSQL to CAML Where value IN OTHER 2010 SP list

I'm not sure why you object to the original query and I don't know CAML. But this query should be equivalent:

SELECT i.Name, i.SubmitDate
FROM issues i INNER JOIN servers s ON s.Name = ServerName
WHERE s.Active = true

What is the limit of nested OR's in a CAML Query?

For those facing the same problem. I went for a search on SharePoint.StackExchange.com and found the following question:

• Nested CAML Query results in exception “ Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries”

One of the answers points to this website:

• CAML: Nested Too Deep

The person discovered that 500 items was too much but that a batch of 300 worked fine. So I tried this and this also works for me. So for anyone that faces the same problem, this might be the solution. :)

CAML query with nested ANDs and ORs for multiple fields

Since you are not allowed to put more than two conditions in one condition group (And | Or) you have to create an extra nested group (MSDN). The expression A AND B AND C looks like this:

<And>
    A
    <And>
        B
        C
    </And>
</And>

Your SQL like sample translated to CAML (hopefully with matching XML tags ;) ):

<Where>
    <And>
        <Or>
            <Eq>
                <FieldRef Name='FirstName' />
                <Value Type='Text'>John</Value>
            </Eq>
            <Or>
                <Eq>
                    <FieldRef Name='LastName' />
                    <Value Type='Text'>John</Value>
                </Eq>
                <Eq>
                    <FieldRef Name='Profile' />
                    <Value Type='Text'>John</Value>
                </Eq>
            </Or>
        </Or>
        <And>       
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>Doe</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                </Or>
            </Or>
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>123</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                </Or>
            </Or>
        </And>
    </And>
</Where>

Can I still use CAML for this query?

Yes. There is no set limit to OR's. (Due to how the CAML is turned into SQL there is a maximum depth of about 2000 chained operators, but this can be increased by balancing the query tree.)

Just keep nesting the OR's for the desired combination. I recommend using XNode/XElement and recursion to create a function to do this "magically".

Note that the nesting is very simple (I have written in in S-expression form, which is also prefix to show what happens, along with an infix C# conditional equivalent):

1: (OR a b)                => a || b                => a || b
2: (OR (OR a b) c)         => (a || b) || c         => a || b || c
3: (OR (OR (OR a b) c) d)  => ((a || b) || c) || d  => a || b || c || d

Note that each new OR just "wraps" the previous expression.

Performance is based on the performance of the underlying SQL Server database and varies based on which field is being used in the CAML. If the field is part of an index then the operations are very fast for "equals" or "range" conditions. Even if the columns are not part of an index, for "smallish" sized lists it is still a very fast operation.

Best bet is to "just try it" with a tool like the U2U CAML Query Builder ("Windows Version") and see what performance issues there are, if any.

Happy coding.

CAML In Operator Equivalent For SharePoint Services 3 (SharePoint 2007)

I stumbled around a couple approaches before coming to this solution. I'm not sure of its scalability but it suits my needs so I thought I would share it. This recursive method should return the equivalent of

WHERE Title IN ([Title1], [Title2],...[TitleN]) 

for a List of 1-N string titles.

private string _camlTitleEq = "<Eq>" +
                                 "<FieldRef Name=\"Title\" />" +
                                 "<Value Type=\"Text\">{0}</Value>" +
                              "</Eq>";

private XElement BuildOrClause(List<string> listItemTitles, int index)
{
    //If we've reached the last item in the list, return only an Eq clause
    if (index == listItemTitles.Count - 1)
        return XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index]));
    else
    {
        //If there are more items in the list, create a new nested Or, where
        //the first value is an Eq clause and the second is the result of BuildOrClause
        XElement orClause = new XElement("Or");
        orClause.Add(XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index])));
        orClause.Add(BuildOrClause(listItemTitles, index + 1));
        return orClause;
    }
}

And you could use it like so:

SPQuery query = new SPQuery();
string titleIn = BuildOrClause(listItemTitles, 0).ToString(SaveOptions.DisableFormatting);

query.Query = "<Where>" +
                  titleIn +
              "</Where>";

I hope this helps someone still working in SP 2007. Constructive feedback is welcome! If you're in SharePoint 2010, use the already built in In Operator.

---

Related Topics