Working on a customer project I came across the requirement to query lists for selectable filter criteria. The query result had to be taken to query a central list. The central list and the other lists had a one to many relation and shared a primary key in the central list.
So lets say there is a customer list with columns „Customer Key“, „Customer Name“, „Customer Adress“ and different other lists wich hold relational data, like a list Contacts with „Customer Key“, „Contact Name“, „Contact Role“, „Contact Department“.
Now the requirement is to first select all contacts with role „Manager“ and department „Marketing“ and then switch to the central list and get all the cutomers that match the query „Has a contact with role -Manager- in department -Marketing-„.
The part to query the contacts list isn’t too hard using CAML. But come to selecting the customers it will quickly end in an error message saying that the CAML query ist nested too deep. Why?
Assuming we have about 500 customers with aproximately 800 contacts the query to get the contacts will look like:
————————–
SPWeb cbweb = SPContext.Current.Web;
SPList cblist = cbweb.Lists[DroplistListsInWeb.Text];
SPQuery cbquery = new SPQuery();
cbquery.Query = „<Where>“ + BuildQuery() + „</Where>“;
SPListItemCollection cblistitems = cblist.GetItems(cbquery);SPView cbview = cbweb.Lists[DroplistListsInWeb.Text].Views[ViewToUse];
cbview.Query = cbquery.Query;
cbview.Update();
————————–Where DroplistListsInWeb represents the control from wich the „contacts“ lists was selected for query. The function BuildQuery() will return a string value like:
————————–
<And><Eq><FieldRef Name=’Role‘ /><Value Type=’Text‘>Manager</Value></Eq></And><Eq><FieldRef Name=’Department‘ /><Value Type=’Text‘>Marketing</Value></Eq>
————————–
In XML style the query will look like:
————————–
<Where>
<And>
<Eq>
<FieldRef Name=’Role‘ /><Value Type=’Text‘>Manager</Value>
</Eq>
</And>
<Eq>
<FieldRef Name=’Department‘ /><Value Type=’Text‘>Marketing</Value>
</Eq>
</Where>————————–
Lets say this result has 150 list itemsthat represent all the managers in marketing departments. But that doesn’t mean necessarily that there are 150 customers (companies) with a manager in their marketing department. Because at least one customer can have more than one manager in the marketing department. Hence the query for customers must contain all the „Customer Number“s from the result and patch them into a large query with many ORs. Worst case this will end in a query with 149 OR nodes.
This can be reduced by pushing all the dupes in „Customer Number“ out. That could be done using LINQ:
—————————-
List<string> Identifiers = new List<string>(); //using a list and LINQ to get distinct identifiers
foreach (SPListItem cbitem in Trefferitems)
{
Identifiers.Add(cbitem[„Title“].ToString());
}
IEnumerable<string> distinctIdentifiers = Identifiers.Distinct();
—————————-
But even then the query could contain to many ORs and sending that query to a list view raises the „CAML nested too deep“ error. Remember a query would look like this:
—————- <Where>
<Or>
<Eq>
<FieldRef Name=’CustNumber‘ /><Value Type=’Text‘>A10089</Value>
</Eq>
<OR>
<Eq>
<FieldRef Name=’CustNumber‘ /><Value Type=’Text‘>A10099</Value>
</Eq>
<OR>
<Eq>
<FieldRef Name=’CustNumber‘ /><Value Type=’Text‘>A10120</Value>
</Eq>
-continue for N more OR-nodes
<Eq>
<FieldRef Name=’CustNumber‘ /><Value Type=’Text‘>A10120</Value>
</Eq>
</Or>
</Or>
</Or>
– close all N OR nodes
</Where>
—————-
Most probably this will raise trouble when the query becomes too long.
To get around this trouble I used an array to group the resulting queries into batches of 50 ORs. I was using an array for storing the batches and the parameter „Treffergroup“ to set the group that was to be presented in the resultview. The whole thing was packed into a function that was called every time the user clicked the „next“ or „previous“ button.
I had to take care of the query structure, because the last CAML query entry would have to be without <OR></Or> tags.
—————————-
foreach (string strIdent in distinctIdentifiers)
{
ItemsCount++;
if (i < 50 && ItemsCount < count)
{
groups[index] = groups[index] + „<Or><Eq><FieldRef Name=’Title‘ /><Value Type=’Text‘>“ + strIdent + „</Value></Eq>“;
cbtemp = cbtemp + „</Or>“;
}
i=i+1;
if (i >= 50 && ItemsCount < count)
{
groups[index] = groups[index] + „<Eq><FieldRef Name=’Title‘ /><Value Type=’Text‘>“ + strIdent + „</Value></Eq>“;
groups[index] = groups[index] + cbtemp;
i = 1;
index=index+1;
cbtemp = „“;
}
if (ItemsCount == count)
{
groups[index] = groups[index] + „<Eq><FieldRef Name=’Title‘ /><Value Type=’Text‘>“ + strIdent + „</Value></Eq>“;
groups[index] = groups[index] + cbtemp;
}
}
resultquery = „<Where>“ + groups[cbTreffergroup] + „</Where>“;
—————————-
I don’t really know how many nodes a CAML query can have without being nested too deep but for me 100 where too many and 50 worked well. So it is somewhere in between. If you find out the number of acceptable nodes please let me know. Just out of curiosity. Happy coding.