CAML query on lookup columns

Let's imagine two SharePoint lists:

- Products
- Categories

In this scenario, the Products list contains a lookup column called Category pointing on the Categories list.

Now, I want to write a CAML query to get all the products list items related to the Hardware category.

The first thing coming into my mind is the following piece of code:

private SPListItemCollection GetProductsByCategory(SPFieldLookupValue Category)

{

    SPList listProducts = this._web.Lists["Products"];

    SPQuery camlQuery = new SPQuery();

    camlQuery.Query = string.Format("<Where><Eq><FieldRef Name=\"Category\" /><Value Type=\"Lookup\">{0}</Value></Eq></Where>", Category.LookupValue);

    return listProducts.GetItems(camlQuery);

}

The problem with this query appears if some categories have the same name (Hardware). It's due to the fact that the query doesn't use the ID of the category but the Name. So, you will get all the products related to all the categories having Hardware as name.

To solve that and get only the products related to the wanted category, you have to rewrite the query in setting the LookupId property of the FieldRef node to true and in using the LookupId property as value and not the LookupValue property:

private SPListItemCollection GetProductsByCategory(SPFieldLookupValue Category)

{

    SPList listProducts = this._web.Lists["Products"];

    SPQuery camlQuery = new SPQuery();

    camlQuery.Query = string.Format("<Where><Eq><FieldRef Name=\"Category\" LookupId=\"TRUE\" /><Value Type=\"Lookup\">{0}</Value></Eq></Where>", Category.LookupId);

    return listProducts.GetItems(camlQuery);

}