Sharepoint Camlex Caml Join error

Question

Hi I'm new to the world of sharepoint and caml, so any hints to point me in the right direction helpful.

I've picked up the Camlex https://camlex.codeplex.com/ library to help me make caml queries in a nice way.

All was working well, until I added in a InnerJoin

var caml =
                Camlex.Query().
                InnerJoin(x => x[CesaDocument.DocType_field].PrimaryList(CESAContext.Documents).ForeignList(CESAContext.DocumentType)).
                Where(x => (string)x[CesaDocument.RiOID_field] == id).
                Scope(ViewScope.RecursiveAll).ToCamlQuery();

At first the code came out with the Join at the end - believing that was the problem I got the source code and I managed to get the Join at the start.

So that gave me this code.

<View Scope="RecursiveAll">
 <Joins>    <Join Type="INNER" ListAlias="Document Types">    
 <Eq>        <FieldRef List="Documents" Name="Doc_x0020_Type" RefType="Id" />       
 <FieldRef List="Document Types" Name="Id" />      
 </Eq>    </Join>  </Joins> <Query> <Where>     <Eq>
 <FieldRef Name="RiO_x0020_ID" />
 <Value Type="Text">1</Value>
 </Eq>    </Where>  </Query></View>

However that still errored -giving me this code.

<nativehr>0x80070057</nativehr><nativestack></nativestack>

When I called the execute function.

List list = null;
            ListItemCollection ListCollection = null;

            list = this.Web.Lists.GetByTitle(List);

            ListCollection = list.GetItems(query);


            this.Load(ListCollection);
            this.ExecuteQuery();

return ListCollection;

Googling seems to bring up to turn allowunsafeUpdates=true but I'm using the Micrsoft.Sharepoint.Client object and I can't see that property nor am I updating. I've pasted the Caml query into SP CAML Query Helper Online and it runs as expected.

Am I doing this right? I've tried a few ways but to no avail. Should I be using the SPQuery object, can that work with CSOM - if so what library is that in.

Should I be using Document_x0020_Types in my join? Edit - tried that but didn't work.

Edit2 Just thought this would work but no.

<Joins>
  <Join Type="INNER" ListAlias="Document_x0020_Types">
    <Eq>
      <FieldRef List="Documents" Name="Doc_x0020_Type" RefType="Id" />
      <FieldRef List="Document_x0020_Types" Name="Id" />
    </Eq>
  </Join>
</Joins>
<View Scope="RecursiveAll">
  <Query>
    <Where>
      <Eq>
        <FieldRef Name="RiO_x0020_ID" />
        <Value Type="Text">2</Value>
      </Eq>
    </Where>
  </Query>
</View>

Edit 3 So now I knew a little more about the format of Caml I could fix my camlex generater.

var caml =
                Camlex.Query()
                .InnerJoin(x => x[CesaDocument.DocType_field].ForeignList(CESAContext.DocumentType))
                .Where(x => (string)x[CesaDocument.RiOID_field] == id)
                .Scope(ViewScope.RecursiveAll)
                .ToCamlQuery();

Just dropping the primary bit did the trick!

Answer 1

Assuming this is what the builder generated for you...

<View Scope="RecursiveAll">
    <Joins>
        <Join Type="INNER" ListAlias="Document Types">
            <Eq>
                <FieldRef List="Documents" Name="Doc_x0020_Type" RefType="Id" />
                <FieldRef List="Document Types" Name="Id" />
            </Eq>
        </Join>
    </Joins>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name="RiO_x0020_ID" />
                <Value Type="Text">1</Value>
            </Eq>
        </Where>
    </Query>
</View>

Problem: You shouldn't need the attribute List="Documents" in the first <FieldRef> element of your join.

Explanation: The List attribute is supposed to only take an alias which is defined in a preceding ListAlias attribute of a Join element. The primary list in a join only needs a List attribute if you are joining additional lists off a foreign list, in which case the alias would be defined by a preceding join. If the List attribute is omitted, the primary list of the join will be the list against which the query is being executed.

The generated CAML query above is otherwise valid if your lists take the following shape:

1. The list that you are querying with this CAML (and which I'm guessing is named Documents) has a lookup field with an internal name of Doc_x0020_Type.
2. That same Documents list has a text field with an internal name of RiO_x0020_ID

If either of those are false (such as if your RiO_x0020_ID field is actually on the Document Types list, not on Documents), then the query will fail. Note that you don't need to specify the actual name of the foreign list to which you're joining, since the mapping parallels an existing lookup field relation.

Further reading: For more information on how to use Joins in CAML (and the related concept of Projections/Projected Fields, which is necessary if you want to do any filtering based on the values in the joined foreign list) you can refer to Microsoft's documentation here: https://msdn.microsoft.com/en-us/library/office/ee539975(v=office.14).aspx