1

I use the following REST query:

http://portal/_api/Web/Lists/getByTitle('MyForms')/Items?$filter=startswith(ContentTypeId,%270x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF%27)

This query works fine and we use it for quit some time. Our list has now reached 500 items. And now our query doesn't work anymore. When I delete 1 item and we have 499 items in the list the query works again.

I have tried adding $skiptoken=Paged=TRUE&$top=100 But no luck.

Is there a 500 item limit for REST?

Update during testing

I found an issue with my list. I have an expiration policy on my contenttypes. The expiration policy timer job has set an index on my contenttype field. When I remove this index my query works again with 500 or more items. sadly the index is recreated on contenttype when the expiration policy timer job runs.

Update after analyzing the ULS

When I do a trace I see that my REST query is translated to the following CAML query:

<Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='Text'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query>

This query fails when I execute this from CSOM.

    using (ClientContext ctx = new ClientContext("http://d-ccp-portal"))
    {
        Web web = ctx.Web;
        List list = web.Lists.GetByTitle("Beheerde documenten Forms 2");

        var q = new CamlQuery() { ViewXml = "<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='Text'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query></View>" };
        var r = list.GetItems(q);                ctx.Load(r);                ctx.ExecuteQuery();
    }

r = 0

The problem seems to be the value type. When I replace my CAML query like so:

<Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query>

I do get all my items

   using (ClientContext ctx = new ClientContext("http://d-ccp-portal"))
    {
        Web web = ctx.Web;
        List list = web.Lists.GetByTitle("Beheerde documenten Forms 2");

        var q2 = new CamlQuery() { ViewXml = "<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query></View>" };
        var r2 = list.GetItems(q2);                ctx.Load(r2);                ctx.ExecuteQuery();
    }

So the problem seems to be that REST doesn't set the correct "value type" when you do a $filter on startswith(ContenTypeId)

Why the index on my list also has influence I don't really know. Because when there is no index on contenttype the first query also works fine.

5
  • check the list threshold on central admin
    – MSallal
    Jan 30 '20 at 23:56
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
    – Gaurravs
    Jan 31 '20 at 2:18
  • The threshold is set to 5000 items. I only have around 505 items and the query fails when 500 or more
    – Danny
    Jan 31 '20 at 7:27
  • 1
    Do you have any entries in the recycle bin? Those count with threshold too.
    – M.P.
    Feb 4 '20 at 11:00
  • Empty recycle bin. See my ULS analysis. It looks like wrong CAML is generated and index is causing issue too
    – Danny
    Feb 5 '20 at 10:26
0

You can try to use the following REST API to achieve it.

/_api/Web/Lists/getByTitle('MyForms')/Items?$filter=substringof('0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF',ContentTypeId)&$top=5000
7
  • When I run this query I get a status 500 error (Exception from HRESULT: 0x80131904) and a SQL exception in the ULS log when there are more then 500 items. When I remove some items ad have 499 this query also works. SQL Error in ULS: Unknown SQL Exception 8116 occurred. Additional error information from SQL Server is included below. Argument data type sql_variant is invalid for argument 1 of like function.
    – Danny
    Jan 31 '20 at 7:26
  • I can confirm that running with less than 500 items I get an empty result. With more than 500 I get the SQL exception. When I remove the index on contentype I always get a empty result. Also with more than 500 items
    – Danny
    Feb 3 '20 at 10:09
  • Try to add $orderby=ContentTypeId desc in REST API. And remove the $filter to check if it works.
    – LZ_MSFT
    Feb 4 '20 at 7:06
  • I can do a orderby. But I need to do a $filter. Check my ULS log finding. I think there is a bug when you use contenttypeid. ValueType should be set to ContentTypeId then it would work
    – Danny
    Feb 4 '20 at 7:24
  • We can also use CAML query with REST API to achieve it. c-sharpcorner.com/blogs/sharepoint-rest-api-caml-query
    – LZ_MSFT
    Feb 4 '20 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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