CAML to Query User field

Sharepoint as a database is more of a Flat structure database. Querying is often times the most time consuming operation in especially when running against millions of List data. Microsoft has done a lot to increase the query time, still there are some limitations in sharepoint. Hence it is necessary to optimize the CAML as much as possible when querying against large Lists or document Libraries.

Often times I get asked by team members about writing CAML against Lookup Field and User Field. I decided to write it down here as a reference for everybody who wants to use CAML to query Sharepoint user field for me as well because every time some one asks me I tend to forget and needs to go back to code and give them the query. So I thought this is a right form so every body can be benefited.


Code:

SPQuery query = new SPQuery();
query.Query = string.Format("<Where><Eq><FieldRef Name='Author' LookupId='TRUE' /><Value Type='Int'>{0}</Value></Eq></Where>", SPContext.Current.Web.CurrentUser.ID);
SPListItemCollection itemCollection = oList.GetItems(query);


I am using the sharepoint user field as sharepoint lookup field and performing the query based on user id. I prefer this method because there is no way this could go wrong as no 2 users can have same ID. This struck me when my team was implementing FBA and we created test user with same name. Interestingly the same query can be used for sharepoint Lookup field as well. If you notice the way Sharepoint stores the Look up field you can the format as

1;#lookupvalue

This same format is used for SPUser field as well. This set up is actually a blessing for us  "Developers"