Request URL Too Long for fetchXml query types

Question

I'm using CRM 2016 web api with fetchxml query parameter, but my query is too long it's simulate the IN operator were the number of parameters passed in the IN list are 300 elements and some times will be more than that.

So my problem is the query are too big for a GET HTTP Request. I have tried to send the query in the http message body but that didn't work so what is the solution for this problem?

Here's a code snippet of the fetchxml query that I used:

<fetch mapping="logical" distinct="true">
   <entity name="entity">
      <attribute name="new_classopportunityid" />
      <attribute name="new_trainingproduct" />
      <attribute name="new_gtgstatus" />
      <attribute name="new_scheduledstartdate" />
      <attribute name="new_scheduledenddate" />
      <attribute name="new_remainingnumberofseats" />
      <attribute name="new_liveclassroom" />
      <attribute name="new_maxlive" />
      <attribute name="new_xavieruniversity" />
      <attribute name="new_partnerlive" />
      <attribute name="new_blended" />
      <filter>
         <condition attribute="new_classopportunityid" operator="in">
            <value>001943ea-e263-e611-8158-00155d002810</value>
            <value>0071e4ea-bd9b-e611-8163-00155d002810</value>
            <value>00c32774-1c8f-e611-8161-00155d002810</value>
            <value>00d513fa-f0bb-e611-8169-00155d002810</value>
            <value>....</value>
            <value>....</value>
            <value>....</value>
         </condition>
      </filter>
   </entity>
</fetch>

The CRM web api endpoint that I request is :

 GET http://<org>/api/data/v8.0/<entity>?fetchXml=<fetch mapping="logical" distinct="true">
       <entity name="entity">
          <attribute name="new_classopportunityid" />
          <attribute name="new_trainingproduct" />
          <attribute name="new_gtgstatus" />
          <attribute name="new_scheduledstartdate" />
          <attribute name="new_scheduledenddate" />
          <attribute name="new_remainingnumberofseats" />
          <attribute name="new_liveclassroom" />
          <attribute name="new_maxlive" />
          <attribute name="new_xavieruniversity" />
          <attribute name="new_partnerlive" />
          <attribute name="new_blended" />
          <filter>
             <condition attribute="new_classopportunityid" operator="in">
                <value>001943ea-e263-e611-8158-00155d002810</value>
                <value>0071e4ea-bd9b-e611-8163-00155d002810</value>
                <value>00c32774-1c8f-e611-8161-00155d002810</value>
                <value>00d513fa-f0bb-e611-8169-00155d002810</value>
                <value>....</value>
                <value>....</value>
                <value>....</value>
             </condition>
          </filter>
       </entity>
    </fetch>

This is the response I got from the api.

Error code: 414: HTTP/1.1 414 Request-URI Too Long  Response :  "<!DOCTYPE HTML PUBLIC \"-\/\/W3C\/\/DTD HTML 4.01\/\/EN\"\"http:\/\/www.w3.org\/TR\/html4\/strict.dtd\">\r\n<HTML><HEAD><TITLE>Request URL Too Long<\/TITLE>\r\n<META HTTP-EQUIV=\"Content-Type\" Content=\"text\/html; charset=us-ascii\"><\/HEAD>\r\n<BODY><h2>Request URL Too Long<\/h2>\r\n<hr><p>HTTP Error 414. The request URL is too long.<\/p>\r\n<\/BODY><\/HTML>\r\n" [] []

score 3 · Accepted Answer · answered Jan 16 '17 at 12:35

3

You'll need to use a Post, not a Get:

https://dreamingincrm.com/2017/01/15/executing-large-fetchxml-with-webapi/

answered Jan 16 '17 at 12:35

Daryl

18,592
9
78
145

I have tryied to use post on the entity endpoint, but this is a REST and this means create entity not get an entity, I will check the url you sent. Thanks – M.Abulsoud Jan 16 '17 at 13:01
Yes! I just saw this yesterday too and meant to post it as a follow-up to my answer. Good find. – Josh Painter Jan 16 '17 at 23:50

score 1 · Answer 2 · answered Jan 14 '17 at 23:18

1

Using the "in" operator with a big list of ids is probably not the best way to query, as you've seen. It would be better if you could filter on an attribute of the new_classopportunity entity by using a link-entity, like this:

<fetch mapping="logical" distinct="true" >
  <entity name="entity" >
    <!-- ... all your attributes ... -->
    <link-entity name="new_classopportunity" from="new_classopportunityid" to="new_classopportunityid" >
      <attribute name="new_name" />
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Notice you can pull in attributes from the link-entity as well. In this case I'm pulling in name and using a filter to only get new_classopportunity records that are active.

If you don't have a field on new_classopportunity that you can filter on to get the same list, add one! :)

answered Jan 14 '17 at 23:18

Josh Painter

4,071
21
26

I don't know what is the link-entity, but it seems you are suggesting to use a range query (i.e between x1 and x2 but x1, and x2 is not Integers they are GUID string). – M.Abulsoud Jan 15 '17 at 07:16
It is similar to a INNER JOIN in SQL. You are pulling in all of your main entity records and joining them on the related entity. You can filter that link-entity, so the end result will be a list of your main entity records that have matching new_classopportunity records. This way you don't pass a big list of ids - you pass a filter that will return those same ids in the entity-link. – Josh Painter Jan 16 '17 at 23:53
Unfortunately, there's no way I can get rid of the long list of ids. – M.Abulsoud Jan 17 '17 at 08:22

Request URL Too Long for fetchXml query types

2 Answers2