How to check the total number of contacts in CRM 2011?

Question

The method used right now is to check the size of the array read from DB. As long as there are other operations performed on it, such a piggy-back is fine. However, I'd like to know if there's a more direct method to query for the number of contact existing.

I'm coding in C# but JS would be to get to know too, just in case.

QueryExpression query = new QueryExpression
{
  EntityName = "contact",
  ColumnSet = new ColumnSet(true),
};
EntityCollection response = organizationService.RetrieveMultiple(query);
IEnumerable<Entity> entities = response.Entities;

Answer 1

Here are your options:

1. Use QueryExpression/Linq to CRM - These methods don't support a server side count though. So this is basically what you are doing now, returning every single record to the client and then counting. The negatives for this are as follow:
   1. You get the page size, not the true number in the database.
   2. The page size is limited to 5000 records, so you'd have to implement your own logic to continue to requery until you've returned all the records
   3. Highly inefficient. All of the data from all of the entities has to be returned.
2. oData - Technically you could use odata, but your going to have the same exact issues as QueryExpressions/Linq since it doesn't support a server side count either.
3. Use Fetch XML - This is the only supported method of getting an aggregate count of records within CRM. It also has the advantage doing the minimum amount of work required to return the information needed.

Here is the Fetch XML and C# code required to retrieve the Count:

var xml = @"
<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='contact'> 
       <attribute name='contactid' alias='contact_count' aggregate='count'/> 
    </entity> 
</fetch>";

using (var service = GetOrganizationServiceProxy())
{
  var resultEntity = service.RetrieveMultiple(
  new FetchExpression(xml)).Entities.First();
  var count = resultEntity
    .GetAttributeValue<Microsoft.Xrm.Sdk.AliasedValue>("contact_count").Value;
}

Notice 50K limit!

There is a limit of 50,000 entities that can aggregated. See this SO question

If this limit is too low, you could do as the SO question suggestions, and catch the exception and just return 50,000. If you needed an exact count, probably the simple thing to do is to add a filter to limit the name to starting with a single letter, then doing 26 different queries, one for each letter. Assuming a completely uniform distribution of names (which it isn't) you should be able to get a max count of 1,300,000.

---

Microsoft Site explicitly stating that only Fetch XML supports the Grouping / Aggregate functions --> http://msdn.microsoft.com/en-us/library/gg334607.aspx

For CRM/CDS V9+

There is now a simple request to get the total count: https://learn.microsoft.com/en-us/dotnet/api/microsoft.crm.sdk.messages.retrievetotalrecordcountrequest?view=dynamics-general-ce-9

Answer 2

You can use the TotalRecordCount on response.

Console.WriteLine("Total number of records: " + response.TotalRecordCount);

Answer 3

You could execute FetchXML with a count aggregation.

Answer 4

I presume that you are doing some sort of select statement from your data base and looking at the returned DataTable object to see how many rows it has.

Try doing a

SELECT COUNT(*) FROM [TableName] WHERE [condition]

This will return a single number which will be the same value as the number of rows.