There can be many reasons for SharePoint CAML queries not working, but one of the most frustrating ones is “The underlying connection was closed: The connection was closed unexpectedly.” The connection from the client (CSOM/REST API) is just dropped. Debugging the issue can seem, at first, almost impossible as there is nearly no additional data to work with, but sometimes the solution can be fairly straightforward.
SharePoint has ‘features’ built in to help maintain performance. One of these is the ‘List View Threshold’ which controls the maximum many items can be displayed in any given list view – it is maintained at Web Application level and configured via Central Administration.
The first threshold you may reach that will cause your CAML queries to start failing is 5,000 list items. This is the default setting and means that list views in the default web UI won’t be displayed, with a warning/error message about the number of items in the list being displayed instead.
You can easily get around this limit by increasing the List View Threshold, but be warned that this affects all lists across the entire Web Application and could affect performance so only modify it if you absolutely need to. (A much better way to manage list views is by using filtering.)
If you do need to edit the threshold then launch Central Administration, and go to Application Management and then choose “Manage web applications” under the Web Applications heading. Next, select the application you want to edit, and then choose the Resource Throttling option from the General Settings ribbon menu button.
On the Resource Throttling modal that is displayed, find the List View Threshold and set it to new limit you need (e.g. in the screen-shot below it has been set to 30,000).
In most cases the above technique can fix your problems but there is another problem with queries performed via the UI.
10,000 Items Is The LIMIT
SharePoint APIs usually fail queries that are going to return more than 10,000 results for performance reasons, and yes, you guessed it, they normally do so simply by dropping the connection instead of returning a helpful, user friendly, error like ‘too many results’.
I have never been able to find a setting to override this, and maybe for good reason as running lots of queries returning big datasets would indeed adversely affect SharePoint performance.
The best thing to do is to implement retention policies on the list that manage its size. If that isn’t possible, consider indexing columns for faster querying, and then customising your CAML queries to filter based on those indexed columns to optimise the data retrieval activities and reduce the number of items likely to be returned.