List query column lookup threshold error

SvenSieverding 

Hi Sven,

Apologies it has been so long, I haven't worked on this in quite some time.

Thanks again for your reply.

To answer some of your queries:

Language: C# .NET
I'm not building a PowerApp, no.

So I'm currently using the SharePoint CSOM to return SharePoint list items like so:

var oList = context.Web.Lists.GetByTitle(listName);

ListItemCollection collListItem = oList.GetItems(camlQuery);

context.Load(collListItem);
context.ExecuteQueryRetry();

The camlQuery of a functioning request:

(NOTE: arbitraryFieldNames are not the same field)

<View>
   <ViewFields>
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
   </ViewFields><Query> 
   <Where>
      <And>
        <Eq>
          <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
          Type='Integer'>21</Value>
        </Eq>
        <Eq>
           <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
           Type='Integer'>25</Value>
        </Eq>
      </And>
   </Where></Query><RowLimit>30</RowLimit>
</View>

The camlQuery of a non-functioning request:

(NOTE: arbitraryFieldNames are not the same field)

It's the same camlQuery as the functioning request, just a lot more ViewFields, i.e.:

<View>
   <ViewFields>
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
      <FieldRef Name='arbitraryFieldName' />
   </ViewFields>
   <Query>
      <Where>
      <And>
         <Eq>
            <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
            Type='Integer'>21</Value>
         </Eq>
         <Eq>
            <FieldRef Name='arbitraryFieldName' LookupId='TRUE'/><Value 
            Type='Integer'>25</Value>
         </Eq>
      </And>
      </Where>
   </Query>
<RowLimit>30</RowLimit>
</View>

This query then returns "The query cannot be completed because the number of lookup columns it contains exceeds the column lookup threshold." error.

The solution I'm currently working on is to synchronously execute REST API requests.

This works well, however, the oData JSON looks like so:

{{
  "odata.metadata": "https://<tenant>.sharepoint.com/sites/<site>/_api/$metadata#SP.ListData.<redacted>&$select=Title,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField",
  "value": [
    {
      "odata.type": "SP.Data.<redacted>",
      "odata.id": "<redacted>",
      "odata.etag": "\"6\"",
      "odata.editLink": "Web/Lists(guid'<redacted>')/Items(42)",
      "Title": "<redacted>",
      "arbitraryField": [],
      "arbitraryField": null,
      "arbitraryField": "<redacted>",
      "arbitraryField": [
        9
      ],
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": true
    }
  ]
}}

{{
  "odata.metadata": "https://<tenant>.sharepoint.com/sites/<site>/_api/$metadata#SP.ListData.<redacted>&$select=arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField,arbitraryField",
  "value": [
    {
      "odata.type": "SP.Data.<redacted>",
      "odata.id": "<redacted>",
      "odata.etag": "\"6\"",
      "odata.editLink": "Web/Lists(guid'<redacted>')/Items(42)",
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null,
      "arbitraryField": null
    }
  ]
}}

And the requests continue until all fields have been returned.

Is there any way to merge and then deserialize this JSON back into a SharePoint ListItem object?

The reason for this is my custom API method requires a ListItemCollection of which it then converts each SharePoint ListItem in the ListItemCollection into my own custom object, so it would take some work to rewrite everything or create new methods to handle the parsing of this oData JSON.

Something I thought of that might work, but would potentially be quite messy and slow:

Stick to CSOM, and could I perhaps reduce the camlQuery to say 10-20 fields, execute the request, and once I have the same list item just with different fields, can I somehow merge them all into one list item (as they will all have the same ID)? I'm guessing performance would take a big hit, though, if there are many list items with the same number of fields... There could be anywhere from 1-10 list items to maybe 1000 or so.

Please let me know if you require more info on anything.

Thanks again.