9

I'm querying MS Dynamics CRM Online from my console app:

public EntityCollection GetEntities(string entityName)
{
    IOrganizationService proxy = ServerConnection.GetOrganizationProxy();

    string request = string.Format("<fetch mapping ='logical'><entity name = '{0}'></entity></fetch>", entityName);
    FetchExpression expression = new FetchExpression(request);
    var mult = proxy.RetrieveMultiple(expression);

    return mult;
}

This code only returns maximum of 5000 elements in mult.Entities. I know there are more entities in CRM. How to retrieve all entites?

Tschareck
  • 3,532
  • 8
  • 38
  • 70

3 Answers3

9

You can only get back 5000 records at a time using fetch XML.

To get more records, you have to use a paging cookie, see here:

Sample: Use FetchXML with a paging cookie

The relevant bits of code:

// Define the fetch attributes.
// Set the number of records per page to retrieve.
int fetchCount = 3;
// Initialize the page number.
int pageNumber = 1;
// Specify the current paging cookie. For retrieving the first page, 
// pagingCookie should be null.
string pagingCookie = null;

The main loop modified, since the sample doesn't seem to do update the paging cookie:

while (true)
{
    // Build fetchXml string with the placeholders.
    string xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);

     FetchExpression expression = new FetchExpression(xml);
     var results = proxy.RetrieveMultiple(expression);

    // * Build up results here *

    // Check for morerecords, if it returns 1.
    if (results.MoreRecords)
    {
        // Increment the page number to retrieve the next page.
        pageNumber++;
        pagingCookie = results.PagingCookie;
    }
    else
    {
        // If no more records in the result nodes, exit the loop.
         break;
    }
}

I personally tend to use LINQ rather than FetchXML, but it's worth noting what Lasse V. Karlsen said, if you are presenting this information to the user, you probably want to be doing some kind of paging (either in FetchXML or LINQ)

Joe
  • 366
  • 3
  • 8
  • Code in this example has pagingCookie = null. At second page of results, this code will throw OrganizationServiceFault exception "Paging cookie is required when trying to retrieve a set of records on any high pages." – Tschareck Nov 05 '14 at 11:17
  • 1
    The MSDN sample doesn't seem to update the paging cookie. It should be available as a property on the result returned from RetrieveMultiple – Joe Nov 05 '14 at 11:52
  • This code will work IF you have a resultset with more than one page. The if(result.MoreRecords) will be true if there are more records available from the result than the first page and then you will set the pagingcookie. If you get an error from this code, try to step debug to see why you don't get the paging cookie. I used this example myself last week and it worked for me, of course you will need a reasonable resultset though. Good luck! – Rickard N Nov 07 '14 at 08:13
  • while (true) { ... } is generally considered a bad practice. And yes, I know that's the official example from the sdk. – Wedge Feb 02 '15 at 22:04
  • 1
    The PagingCookie property comes set in NULL because have to include the ID attribute of the entity of the query – Marcelo Acosta Díaz Sep 09 '16 at 15:28
7

You can use LINQ as shown below. The CRM LINQ provider will automatically page the query and run as many requests as is needed to get the full result, and return the complete set in a single object. It's really convenient for us. However, be careful with that. If the result set is very large it will be noticeably slow, and it could even throw an OutOfMemoryException in extreme cases. 

 public List<Entity> GetEntities(string entityName)
    {
        OrganizationServiceContext DataContext = new OrganizationServiceContext(ServerConnection.GetOrganizationProxy());

        return DataContext.CreateQuery(entityName).toList();
    }

Here's an alternative implementation for paging with FetchXML, which I like much better than the official examples:

int page = 1;
EntityCollection entityList = new EntityCollection();

do
{
    entityList = Context.RetrieveMultiple(new FetchExpression(String.Format("<fetch version='1.0' page='{1}' paging-cookie='{0}' count='1000' output-format='xml-platform' mapping='logical' distinct='false'> ... </fetch>", SecurityElement.Escape(entityList.PagingCookie), page++)));

    // Do something with the results here
}
while (entityList.MoreRecords);
Wedge
  • 817
  • 9
  • 12
1

I had the same issue. I solved it by including the id of the entity in fetch xml or Query Expression. If you include pass in "lead" in the query then also add "leadid". Then the paging cookie is auto generated for you.

Wai Ha Lee
  • 7,664
  • 52
  • 54
  • 80
Neo
  • 11
  • 1