CAML Query on a Lookup Field that points to a Calculated Field throws Exception

Question

I wrote some CAML Queries to get data from a List that has lookups on another List. All is working fine as long as I do not reference a Lookup Field which points to a Calculated Field.

I tried different types in the value field, but it just doesn't work. When I replace the FieldRef with another field from the list, it works as excpected. Why can't I use a lookup to a calculated Field in a Query?

Definition of the to-be-queried Lookup Field in List A

<Field Name="Mitarbeiter" ShowField="Search" ID="{...}" DisplayName="Mitarbeiter" Type="Lookup" List="Lists/Mitarbeiter" />

The referenced calculated Field in List B (basically displays "Lastname, Firstname" in this example)

<Field Name="Search" ID="{...}" Hidden="FALSE" DisplayName="Search" Type="Calculated" ResultType="Text" ReadOnly="TRUE">
   <FieldRefs>
     <FieldRef Name="Title" />
     <FieldRef Name="Vorname" />
   </FieldRefs>
   <Formula>=Title &amp; ", " &amp; Vorname</Formula>
</Field>

and the CAML Query (Field Mitarbeiter Points to a calculated Field in another List)

 <Where>
  <Contains>
     <FieldRef Name="Mitarbeiter" />
     <Value Type="Text">Name of person</Value>
  </Contains>
 </Where>

The Request crashes and I find several entries in the SP Logs

System.Data.SqlClient.SqlException: Argument data type sql_variant is invalid for argument 1 of like function.

Unknown SQL Exception 8116 occurred.  Argument data type sql_variant is invalid for argument 1 of like function.function.'

Answer 1

try this:

 <Where>
  <Contains>
     <FieldRef Name="Mitarbeiter" />
     <Value Type="lookup">ID of person</Value>
  </Contains>
 </Where>

or

     <Where>
      <Contains>
         <FieldRef Name="Mitarbeiter" />
         <Value Type="lookupId">ID of person</Value>
      </Contains>
     </Where>