3

I've been struggling with writing Linq queries against the Dynamics CRM 2015 SDK OrganizationServiceContext. I'm finding that often the Linq methods I want to use are not supported by the CRM Linq Provider.

The latest is Count(). I have an IQuerable<Lead> object and want to count the total records that would be returned when enumerated. But calling Count() on an IQueryable gives the error:

The method 'Count' is not supported

Digging around I found this advice which is to define the Linq query as an IEnumerable. This appears to work - calling Count() on the IEnumerable<Lead> object returns the total records.

What I'd like to know is where the enumeration operation takes place. Is it Dynamics side, or is it pulling all leads into my web server memory and counting there? The whole reason I'm performing the count in the first place is to guard against pulling too large a dataset into memory...

Tom Troughton
  • 3,941
  • 2
  • 37
  • 77

1 Answers1

6

LINQ for CRM queries are translated to QueryExpression queries and therefore are limited to its capabilities. QueryExpression queries do not support aggregates (like count, sum, average), so indeed your query will pull all selected records into memory.

The preferred method to get a proper count is using a FetchXml query.

A count with QueryExpression can also be achieved in the following way. (I am not sure how this is translated into SQL; it may be slightly less performant.)

Here an example:

var query = new QueryExpression("account")
{
    PageInfo = new PagingInfo
    {
        Count = 1,
        PageNumber = 1,
        ReturnTotalRecordCount = true
    }
};

var result = service.RetrieveMultiple(query);

if (result.TotalRecordCountLimitExceeded)
    throw new InvalidOperationException("Cannot get record count.");

return result.TotalRecordCount;

As you can see, there is a limit for the number of records that can be counted. I believe it currently is 50,000. In OnPremise deployments you can configure this limit.

Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42
  • I suspected as much. And of course there's no way to convert an `IEnumerable` query to the required XML syntax to avoid having to write essentially the same query for two different mechanisms? – Tom Troughton Jul 22 '15 at 10:05
  • Well, actually you can use your existing `QueryExpression` query to get a total count by adding a few properties to it. I extended my answer. – Henk van Boeijen Jul 22 '15 at 10:29