For God’s sake, don’t reuse SPQuery objects!

Now, with that out of the way, let me explain. I’ve inherited some code. In that code, it’s performing a couple of queries on a List, using an SPQuery object. It’s querying a boolean column, and only one item in the list should be true. It’s a long story why, but that’s the way it is. The list is something like this:

The code needs to query this list for true items (which should only be one) and false items (which you can see above, is more than one). However, these queries didn’t seem to be working.

For some reason, the code for this was reassigning the Query string of the SPQuery object, and reusing it – something like this:

string trueQuery = "<Where><Eq><FieldRef Name='Final_x0020_State'/><Value Type='Boolean'>1</Value></Eq></Where>";
string falseQuery = "<Where><Eq><FieldRef Name='Final_x0020_State'/><Value Type='Boolean'>0</Value></Eq></Where>";

using (SPSite site = new SPSite(listUrl))
{
 using (SPWeb web = site.OpenWeb())
 {
 SPList list = web.GetList(listUrl);

SPQuery q = new SPQuery();
 q.Query = trueQuery;

SPListItemCollection items = list.GetItems(q);

Console.WriteLine("Count: {0}", items.Count);

q.Query = falseQuery;
 items = list.GetItems(q);

Console.WriteLine("Count: {0}", items.Count);
 }
}

The result of this is, however, surprising:

That’s right, you get the same result for both queries. If we change the code so that we’re creating a new SPQuery object for the second query:

string trueQuery = "<Where><Eq><FieldRef Name='Final_x0020_State'/><Value Type='Boolean'>1</Value></Eq></Where>";
string falseQuery = "<Where><Eq><FieldRef Name='Final_x0020_State'/><Value Type='Boolean'>0</Value></Eq></Where>";

using (SPSite site = new SPSite(listUrl))
{
 using (SPWeb web = site.OpenWeb())
 {
 SPList list = web.GetList(listUrl);

SPQuery q = new SPQuery();
 q.Query = trueQuery;

SPListItemCollection items = list.GetItems(q);

Console.WriteLine("Count: {0}", items.Count);

SPQuery q2 = new SPQuery();
 q2.Query = falseQuery;
 items = list.GetItems(q2);

Console.WriteLine("Count: {0}", items.Count);
 }
}
}

This code gives the correct result:

So what gives? Well, I used Reflector to try and track through how the GetItems() method was, well, getting items, but I soon got lost in what SharePoint was doing. However, my own thought is that it isn’t suprising. If the same SPQuery is run twice, I’d probably not want to actually query the database twice; I’d consider implementing some sort of cache. In amongst the list query process, I did find a flag that could be sent as part of the query about the objects that was called SAFEARRAYFLAG_CACHEROWSETANDID, so I’m sure there is some form of caching – and my theory is that it is keyed by SPQuery object, not by the query string itself. Thus, after we reset the SPQuery.Query property and try and get the new set of results, the query mechanism sees this as the same query as the first one rather than a new and different query.

Quite why you wouldn’t just instantiate another SPQuery object mystifies me a little – it isn’t an expensive operation, and in 5 years of SharePoint Development, I don’t think I’ve ever needed to reuse one. But it isn’t obvious that you can’t.

I’d really prefer that the Query string property was ‘write once’ one way or another – such as being a constructor parameter, and the Query parameter only having a Get method, but no set one.

Edit: After writing, I thought to try Google – I’m not the first person to stumble upon this one, though other people have seen it present itself in different ways:

• Do Not Reuse SPQuery by Leon Zandman, who found that it is okay if you’re only paging through the results of the same query
• Watchout!!! Reinstantiate SPQuery by Shannon Bray
• SPQuery – A Few Important things by Praveen