CAML query to a SharePoint list, order by a lookup field

Question

I'm attempting to pull a list from SharePoint via CAML and I want the list returned ordered by a specific field. The field is a lookup field. The query comes back unordered when I set the OrderBy to be the lookup field, if I use a text field it's fine.

The U2U CAML query builder will return this query ordered when I build it in the editor.

Here's a code snippet of how I build and execute the query:

String baseQuery = "<Query><Where><Eq><FieldRef Name='paApproved' /><Value Type='Boolean'>1</Value></Eq></Where><OrderBy><FieldRef Name='paState' Ascending='True' LookupValue='TRUE' /></OrderBy></Query>";

qStates.Query = baseQuery;

SPListItemCollection byState = web.Lists["paUpdates"].GetItems(qStates);

The rest is a for loop that parses the collection and displays it. I can post that if necessary.

Here's the SOAP call made by the CAML query tool, I scraped it from the HTTP stream with wireshark.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope 
      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
   <listName>paUpdates</listName>
   <query>
    <Query xmlns="">
     <Where>
      <Eq>
       <FieldRef Name="paApproved" />
       <Value Type="Boolean">1</Value>
      </Eq>
     </Where>
     <OrderBy>
      <FieldRef Name="paState" Ascending="False" />
     </OrderBy>
    </Query>
   </query>
   <viewFields>
    <ViewFields xmlns="" />
   </viewFields>
   <queryOptions>
    <QueryOptions xmlns="" />
   </queryOptions>
  </GetListItems>
 </soap:Body>
</soap:Envelope>

For whatever reason the CAML query tool works, my code doesn't. Anyone know why? Thanks in advance.

Edited to reflect the code I'm actually testing. I had some code that had incorrect values.

Answer 1

The code sample you posted doesn't match the wireshark query:

<Query><Where><Eq><FieldRef Name='paApproved' /><Value Type='Boolean'>1</Value></Eq></Where><OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy></Query>

Should be:

<Where><Eq><FieldRef Name="paApproved" /><Value Type="Boolean">1</Value></Eq></Where><OrderBy><FieldRef Name="paState" Ascending="False" /></OrderBy>

You don't need the <Query></Query> elements (see here for an example).

Answer 2

I tried to reproduce the problem. Your query really doesn't sort correctly. I've made two changes to make it work - removed Query element and removed LookupValue='TRUE' (there is no attribute with such name in element schema). After that all seems fine.