SPQuery on a User field

Filtering on a user field in a SPQuery can be a bit tricky.

The following query would look at the displayname in the userfield on the list (not very practical):


<Where>
  <Eq><FieldRef Name="UserId" /><Value Type="User" >contoso\sp</Value></Eq>
</Where>


The userfield is actually a string like this "42#;contoso\sp"
You would then believe, that it was easy to filter on the loginname. However... If the displayname of the user is set, the string value og the userfield would then be "42;#Sune Popp".
The consistent thing is the user id. This id is unique within the site collection.

So... A better way (imho) is to query on the user lookup id. Your caml should then look like this:


<Where>
  <Eq><FieldRef Name="UserId" LookupId="TRUE"/><Value Type="User" >42</Value></Eq>
</Where>


Hint! As the user id is only unique within the site collection, you should use EnsureUser on the loginname, if your solution is spread across more site collections.