up vote 0 down vote favorite
1

I am running into an issue when using the listdata.svc REST API on large lists. I have a query that looks like: .../_vti_bin/listdata.svc/MyList?$filter=(substringof('<query>',LastName))or(substringof('<query>',FirstName))&$select=Name,Id

Where Name is a calculated column that combines LastName, FirstName (and does a bit more stuff in cases where there are middle names/initials etc.)

The list has over 5000 items and throws a 500 error. Both FirstName and LastName are indexed. When I replace the or operator with an and operator there is no error. So the issue must be related to the list view threshold and SharePoint not wanting to filter on more than 1 field. (And I can't use a Person field for this as many of the entries in this list do not exist in AD). If it matters I am using O365. My goal is to get a list of names that contain the query text

Anyone have any ideas on how to get around this?

share|improve this question
  • Had you indexed the fields before crossing the threshold? – harshal gite Sep 16 at 11:37
  • Yes; both Firstname and Lastname where indexed before hitting 5000 items – Paul Gowman Sep 17 at 12:48
up vote 0 down vote

You can try this then -> .../_api/web/lists/getByTitle('MyList Display Name')?$filter=(substringof('<query>',LastName))or(substringof('<query>',FirstName))&$select=Name,Id

if this throws an error then maybe this should work ->

.../_vti_bin/listdata.svc/MyList?$filter=(substringof('<query>',LastName) eq false)or(substringof('<query>',FirstName) eq false)&$select=Name,Id
share|improve this answer
  • The first one returns the list. Even when I add the /items to the query, it is still throttled. The Second one isn't functionally any different than the original query, which again, is still throttled. – Paul Gowman Sep 18 at 14:25
  • Try with single filter. Most of the time "or" in filters do not work on threshold crossed lists. – harshal gite Sep 19 at 8:16
  • Without the "or" operator it works fine (as they are indexed columns). I get that the "or" operator is the problem as it requires doing a join which results in a large set of data which has no relevant index. – Paul Gowman Sep 19 at 12:58
  • You will have to write 2 separate calls and then join the result sets. – harshal gite Sep 19 at 13:00
up vote 0 down vote

OR is an evil operator.

This will force SP to evaluate both sides of the query and then join them. The 5000 item limit is thrown once the internal engine accesses the 5000th record.

Also substring is not a good operator for indices. Even indexed data will have to work through an index scan...

share|improve this answer
  • I agree with you. However it is still something that needs to be done in my scenario (looking up a person (not necessarily user) so I can assign them a permit). So it is this evil lookup operation (as lookup fields don't work either), or I have to get the user to manually search for the person they want, and trust that they type in an id correctly. It is good to know how the SP engine is evaluating the expression and why it throws an error – Paul Gowman Sep 18 at 14:08

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.