Don't Reuse SPQuery objects!

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:

Advertisements
Don't Reuse SPQuery objects!

3 thoughts on “Don't Reuse SPQuery objects!

  1. kevin says:

    This, like many other things, should’ve been documented in the SDK but it isn’t therefore I wouldn’t fault the developer for not figuring this out. I too learned about this in practice.

    Like

  2. Yes, I agree, it’s not obvious at all. I would have created a new object reference, but that’s just preference; I couldn’t see for a fair while that there was anything really wrong with what he’d done.

    Like

  3. Eric L says:

    all you have to do to make it work is clear your variable that you instantiated c

    loop {
    $spQuery = New-Object Microsoft.SharePoint.SPQuery
    DO STUFF HERE WITH OBJECT
    clear-variable spQuery
    }

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s