Dynamics 365 Calling FetchXML from C#

FetchXML is useful in that you can create queries through the Dynamics 365 user interface, and queries can include aggregates (sum, avg, min, max, count). Here we will go through an example of using FetchXML in C# through a console app.

First, create a new console app in Visual Studio:

Add assemblies:

• Microsoft.Xrm.Tooling.Connector
• Microsoft.Xrm.Sdk

In our example, we will retrieve multiple accounts. We will create some FetchXML. Go to Dynamics 365 and select a query. Note the ability to download the FetchXML:

If we run this query, we can see the results that will be returned:

Open the downloaded file to see the XML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
 <entity name="account">
 <attribute name="name" />
 <attribute name="primarycontactid" />
 <attribute name="telephone1" />
 <attribute name="accountid" />
 <order attribute="name" descending="false" />
 </entity>
</fetch>

Now, replace all the double quotes with single quotes:

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
 <entity name='account'>
 <attribute name='name' />
 <attribute name='primarycontactid' />
 <attribute name='telephone1' />
 <attribute name='accountid' />
 <order attribute='name' descending='false' />
 </entity>
</fetch>

Now, we will add this to our C# console app code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Tooling.Connector;
using System.ServiceModel;

namespace Carl.Crm.RetrieveMultipleConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var connectionString = @"AuthType = Office365; Url = https://yourcrm.crm.dynamics.com/;Username=your@email.com;Password=yourpassword";
                CrmServiceClient conn = new CrmServiceClient(connectionString);

                IOrganizationService service = (IOrganizationService)conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;

                string fetchquery = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                  <entity name='account'>
                                    <attribute name='name' />
                                    <attribute name='primarycontactid' />
                                    <attribute name='telephone1' />
                                    <attribute name='accountid' />
                                    <order attribute='name' descending='false' />
                                  </entity>
                                </fetch>";

                EntityCollection accounts = service.RetrieveMultiple(new FetchExpression(fetchquery));
                foreach (var c in accounts.Entities) {
                    Console.WriteLine("Name: {0}", c.Attributes["name"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.ReadLine();
        }
    }
}

This outputs:

Now, let’s change the query to a count. The FetchXML looks like:

<fetch distinct='false' mapping='logical' aggregate='true' >
  <entity name='account' >
    <attribute name='accountid' alias='accountid' aggregate='count' />
  </entity>
</fetch>

The full code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Tooling.Connector;
using System.ServiceModel;

namespace Carl.Crm.RetrieveMultipleConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {

                var connectionString = @"AuthType = Office365; Url = https://yourcrm.crm.dynamics.com/;Username=your@email.com;Password=yourpassword";
                CrmServiceClient conn = new CrmServiceClient(connectionString);

                IOrganizationService service = (IOrganizationService)conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;

                string fetchxmlquery = @" 
                                            <fetch distinct='false' mapping='logical' aggregate='true'> 
                                                <entity name='account'> 
                                                   <attribute name='accountid' alias='account_count' aggregate='count'/> 
                                                </entity> 
                                            </fetch>";

                EntityCollection account_count = service.RetrieveMultiple(new FetchExpression(fetchxmlquery));

                foreach (var c in account_count.Entities)
                {
                    Int32 count = (Int32)((AliasedValue)c["account_count"]).Value;
                    System.Console.WriteLine("Count of all accounts: " + count);

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.ReadLine();
        }
    }
}

Running this, we get:

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin