Using PowerShell to get a D365 record count

The Microsoft.Xrm.Data.PowerShell module provides a number of very useful functions for getting information from Microsoft CRM/D365 using PowerShell. The Get-CrmEntityRecordCount is just one of those functions but it only returns a count of all records for an entity. What if I need a record count for a filtered view? Fortunately, this is where the Get-CrmRecordsByFetch function can be combined with FetchXml aggregation.

# Create my connection to D365
$conn = Connect-CrmOnlineDiscovery -InteractiveMode

# Create my FetchXML with the relevant filter to return an aggregate (Count)
$count = Get-CrmRecordsByFetch -conn $conn -Fetch @"
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
    <entity name="msdyn_workorder">
        <attribute name="msdyn_name" alias="msdyn_name_count" aggregate="count" />
        <filter type="and">
            <condition attribute="createdon" operator="on-or-before" value="2018-02-01" />

# Get the actual number of records from the AliasedValue


LINQPad – D365 Connections

You can create connections to Dynamics 365 (CRM 2016 etc) without using the LINQPad CRM Driver.

  1. Create a new query
  2. Select Query | References and Properties from the menu (F4)
  3. Add the following references to the Query Properties
  4. linqpad-d365-1
  5. If you have an assembly with Early Bound entities you can also add it here.
  6. Open the Additional Namespace Imports tab and select the following namespaces. Include any Early Bound entity namespaces here too.
  7. linqpad-d365-2
  8. Enter the following code into LINQPad to make your connection and generate your queries.
  9. The Util.GetPassword is the LINQPad utility method for retrieving a password encrypted in the LINQPad Password Manager.
void Main()
 string url = ""; 
 string username = "";
 string password = Util.GetPassword("d365-crmadmin");
 CrmServiceClient conn = new CrmServiceClient($"Url={url};Username={username};Password={password}; AuthType=Office365");

conn.OrganizationServiceProxy.Timeout = new System.TimeSpan(0, 3, 0);
 IOrganizationService orgService = conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;

Context = new XrmServiceContext(orgService);

// Write your queries as normal
 // var myQuery = from a in Context.AccountSet where ....

public XrmServiceContext Context { get; set; }


D365 Portal – Cache Tool

The D365 Portal needs to be configured to automate the clearing of the cache within the Portal for data that is modified in D365. As a Portal Administrator you can access a hidden portal page, once you have logged in, to manually clear the cache. After logging in, navigate to <portal url>/_services/about. On the page, in addition to some details about your portal is a Clear Cache button which forces the portal cache to be refreshed.

Microsoft.Dynamics.CRM.Between Dates

A quick example of using the Between function for the Dynamics 365 Web API.

$filter=Microsoft.Dynamics.CRM.Between(PropertyName='createdon',PropertyValues=["2017-01-01T00:00:01Z","2017-04-30T23:59:00Z"]) and statecode eq 0
  • The dates need to be defined in UTC format to ensure quality of the data returned. It can be specified as “mm/dd/yyy” but this may be confusing if you are operating in non-US date formats.
  • The Functions can be combined with your other standard filters e.g. statecode eq 0.