It’s a common task to search SharePoint lists and libraries using an SPQuery mechanism. The rules are pretty simple, you write a query involving columns and values you would like to filter and get the results back. Things are not that straighforward when you`re dealing with lookup column.
When one of your lists is referencing another using a lookup column in it`s relationship – the format the data gets stored in the column involves the referenced ID delimited by a semicolon and followed by the value. You`d think that by supplying exact same format and values you will get your query working but not so.
In order to filter byb a lookup column – you need to supply LookupId=’True’ attribute and value and then provide just an ID of the looked up item.
In the below example, I use a list called comments to search comments that have been associated with a particular post.
Here is how my query is going to look like:
SPListItemCollection items = commentsList.GetItems(
new SPQuery {
Query = "<Where><Eq><FieldRef Name='PostId' LookupId='True'/>"+
"<Value Type='Integer'>" +
properties.ListItemId + "</Value></Eq></Where>",
RowLimit = 0,
}
);
That’s all there is to it, enjoy!
