Multi lookup fields in CAML queries. <Eq> vs <Contains>.

Correct query with <Eq>:

	<Where><Eq><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + id + "</Value></Eq></Where>

Problematic query with <Contains>:

"<Where><Contains><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + id + "</Value></Contains></Where>"

Below is a short explanation and comparison between the two queries above.

When using CAML query for querying a list with multi-lookup field it may seem logical to use <CONTAINS> to get all the items that contain a specific value in the multi lookup field.
That query would be:

"<Where><Contains><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + id + "</Value></Contains></Where>"

The problem with this query is that it will return wrong results as explained below with one example.
For demonstration purposes let’s say we have two lists, List1 and List2, and List1 has a multi lookup column that points to List2.

These two lists contain the following items:

Items in List1:
  • Item1:
        Title: Item1
        MultiLookupField: i1
  • Item2:
        Title: Item2
        MultiLookupField: i1,i21
  • Item3:
        Title: Item3
        MultiLookupField: i2,i21
    • Items in List2:
      • Item1:
            Title: i1
            ID: 1
      • Item2:
            Title i2
            ID: 2
      • Item3:
            Title: i21
            ID: 21

      A CAML query with <CONTAINS> to get all items in List1 that have i1 in their multi lookup field would look like below:

      	<Where><Contains><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + 1 + "</Value></Contains></Where>
      

      This will return Item1, Item2 and Item3. It should only return Item1 and Item2 but because Item3 has i21 which has ID=21 it is included because 21 contains “1”.

      To avoid this problem <EQ> should be used instead of <CONTAINS>.

      	<Where><Eq><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + id + "</Value></Eq></Where>
      

      By reading the query you may think that it will return only the items that have ONLY one value in the multi lookup field equal to value specified in the query but the true behaviour is that it will return all the items that CONTAIN the specified item even if they have other items selected in the multi lookup field.
      For the lists in the example above if for example the query is as below

      	<Where><Eq><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">1</Value></Eq></Where>
      

      it will return not just Item1 that has only i1 in the multi lookup field but it will also return Item2 because it contains i1 even although i1 is not the only item Item2 contains in the multi lookup field (it also contains i21).

      Conclusion:

      Use <EQ> instead of <CONTAINS> when writing a query that checks a multi lookup field.
      Be aware that when checking a multi lookup field by Id (with LookupId=TRUE) a query with <EQ> returns results that you would normally expect from a query with <CONTAINS> while a query with <CONTAINS> returns wrong results and is not very usable in this case.

      Code example with <EQ>:

      	string query = "<Where><Eq><FieldRef Name=\"MultiLookupFieldName\" LookupId=\"TRUE\"/><Value Type=\"LookupMulti\">" + id + "</Value></Eq></Where>";
      	SPQuery spQuery = new SPQuery();
      	spQuery.Query = query;
      	SPListItemCollection items = list.GetItems(spQuery);
      


      Advertisements

3 Responses to Multi lookup fields in CAML queries. <Eq> vs <Contains>.

  1. Hi! Thank you for the information!

  2. Bhaskar Dhone says:

    Really helpful. Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: