CAML Query not returning all results

Question

I've got a CAML Query which does a query on a list which exceeds the List View Threshold.  The code is shown below:

SPWeb web = SPContext.Current.Web;
            SPList usersList = web.Lists["User Management"];
            
            SPQuery qry = new SPQuery();
            qry.Query =
            @" <Where>
                  <And>
                     <Eq>
                        <FieldRef Name='User_Org' />
                        <Value Type='Text'>" + orgSelect.SelectedValue + @"</Value>
                     </Eq>
                     <Contains>
                        <FieldRef Name='User_LogonAccount' />
                        <Value Type='Text'>" + searchText.Text + @"</Value>
                     </Contains>
                  </And>
               </Where>
                <OrderBy>
                    <FieldRef Name='User_DisplayName' />
                </OrderBy>";
            qry.ViewFields = "<FieldRef Name='User_DisplayName' /><FieldRef Name='ID' /><FieldRef Name='User_Interviewer' /><FieldRef Name='User_PhoneNumber' />";
            SPListItemCollection users = usersList.GetItems(qry);

Both User_Org and User_LogonAccount are indexed. The issue is that the query is not returning all of the records that it should. For example, if I provide ORG A and the search text 'sa' the query will return Sawyer, David but not Sawvel, Julie (who are both in ORG A).

Anybody have any ideas as to what could be happening?

Answer 1

Hi

If list items in separate folders then add following on your code

qry.ViewAttributes = "Scope=\"RecursiveAll\"";

---

Please mark the replies as answers if they help or unmark if not.

Answer 2

The list does not implement any folders, but I will try looking through some of the other query settings to see what might be happening.

Answer 3

Have you tried including User_LogonAccount in your view fields?

---

Blog | SharePoint Field Notes Dev Tool | ClassMaster

Answer 4

May be your current users doesn't enough permissions?

---

My contributions: SharePoint 2010 Solution Installer

Answer 5

Hello ,

as mention by _Awiw, it could be a lack of permission error

if your number of items exceed the treshold, you needs to be administrator to have all the result

Cand you use the runwithelevated to do your query?

---

Best regards, Christopher.
Blog | Mail
Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

Answer 6

Unfortunately the webpart is a sandbox solution, so I'm not sure that runwithelevated can be utilized.

I don't think that lack of administrator privileges is the problem.  I've tested administrator vs. normal user with the same results.  Additionally, we pull from a different list with one million records and never have a problem with records being dropped from the results.  The only difference between the two queries is that the query listed above uses the 'contains' search where as the one running on the million record list uses only 'equals'.

Maybe the flaw is somehow in the 'contains' logic.

Answer 7

Hi

Have you tried making all fields mentioned in your Query (each field that was within FieldRef tag) indexed ?

---

LinkedIn Profile
SharePoint Advanced Visibility Options project
SharePoint Managed Metadata Claims Provider project

Answer 8

You are right , the sandboxed doesn't  allow the runwithelevated.

if your "equal" query i working parfectly, maybe the "contains" is the problem.. 

the only suggestion I have in mind is to check your data to be sure of your results :s sorry

---

Best regards, Christopher.
Blog | Mail
Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

Answer 9

Ah..

I've made some tests recently and the result is...

"Contains" makes the query to fire that exception because sharepoint engine enumerates all the items if "contains" clause is in the query.

You need to remove it from the query and filter returned items using Linq for example.

According to the MSDN article there is a way to execute queries with "contains" and similar keywords avoiding SPQueryThrottleException firing you can use ContentIterator .

static int exceptions = 0;
static int items = 0;

protected void OnTestContentIterator(object sender, EventArgs args)
{
    items = 0;
    exceptions = 0;
    string query1 = @"<View>
        <Query>
            <Where>
                <And>
                    <BeginsWith>
                        <FieldRef Name='SKU' />
                        <Value Type='Text'>S</Value>
                    </BeginsWith>
                </And>
            </Where>
        </Query>
    </View>";

    ContentIterator iterator = new ContentIterator();
    SPQuery listQuery = new SPQuery();
    listQuery.Query = query1;
    SPList list = SPContext.Current.Web.Lists["Parts"];
    iterator.ProcessListItems(list,
        listQuery,
        ProcessItem,
        ProcessError
    );
}

public    bool ProcessError(SPListItem item, Exception e) 
{ 
    // process the error
    exceptions++; 
    return true; 
}
public void ProcessItem(SPListItem item)
{
    items++;
    //process the item.
}

---

LinkedIn Profile
SharePoint Advanced Visibility Options project
SharePoint Managed Metadata Claims Provider project