How to query Microsoft Dynamics CRM

There are several choices for querying Microsoft Dynamics CRM. Other than direct database (SQL Server) access, they all go through the web service that is provided by MS Dynamics CRM. Below I provide what I learned when I tried to pull data from MS Dynamics CRM.

Assumptions

I assume you are using Microsoft Dynamics CRM 4.0 (though I think most will apply to 3.0 also). I assume you already have a MS Dynamics CRM installed and working. If you are like me, someone else manages and installed it, but you need to pull or update data in it.

You will need an account to the system. It doesn’t have to be a particularly powerful user, but it does have to have the proper permissions to read or write or create or delete or whatever you want your program to do. Your program will be logging into the system with those credentials.

I assume you want to use the web service instead of direct database access. If you are lucky enough to have direct database access and you are doing read only operations, then this will definitely be the fastest. I was not so lucky, so I have to use the web service, which is not bad really.

What data is available?

The first thing you need is the url of the user interface to the MS Dynamics CRM. For example, https://crm or http://crm. For our examples, I’ll assume https is being used, but http should be the same. Once you have that you can go to the url

http://crm/sdk/list.aspx

This will give you a list of all entities that are available for query. It also provides metadata about each of the entities; including columns and their metadata. This is extremely useful.

How to find the web service

There are actually two web services. One that I never use, but you may want or need to, and another that I use all the time.

The first web service is a discovery web service. Its purpose is to get information about the environment that you are in. The url for this web service is

https://crm/mscrmservices/2007/AD/CrmDiscoveryService.asmx

The second (and most used) web service is the main web service and can be found at

https://crm/mscrmservices/2007/CrmServiceWSDL.aspx

You can use those urls in Visual Studio to add a web reference to the appropriate web service(s). This will import a WSDL and convert the WSDL to C# code for you. I recommend you do it this way. However, if you really want to download the WSDL and work with it you can do that also. You can also download the WSDL from the UI.

If you show all files in your solution you can expand your web service reference and look at the Reference.cs. These are the c# datatypes for the entities and properties that we saw when you looked at http://crm/sdk/list.aspx.

Working with the web service

Depending on your security, environment, etc you may need to specify more about the web service than many of the simple examples on the web show. In my case, I had to specify my own Authentication Token that specified my organization name in the CRM.

Below is a method that I created to call instead of calling the standard new CrmService() constructor.

private static CrmService GetWebService()
{
    CrmAuthenticationToken token = new CrmAuthenticationToken();
    token.AuthenticationType = AuthenticationType.AD;
    token.OrganizationName = "YourCompanyNameHere";

    CrmService crmService = new CrmService();
    crmService.CrmAuthenticationTokenValue = token;
    crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

    // big performance improvement for subsequent calls to web service
    crmService.PreAuthenticate = true;

    return crmService;
}

Where are all the methods on the web service?

When I first looked at the CrmService web service I was shocked. I thought I would see tons of methods that I can call, but instead I found a seven methods. Each one has a different use and each has its own advantages and disadvantages. Click on one of the links below to go to the docs that also contain good examples on using the methods.

Create - Creates an entity instance. Use this to create a new record in the database.

Delete - Deletes an entity instance. Use this to delete an existing record from the database.

Retrieve - Retrieves an entity instance using the specified ID. Use this when you only want one entity and you know its ID. This is the fastest for returning a single row for a given entity. This returns a strongly typed object. Only the properties you specify to be returned are populated in the object though. You can use intellisense on these objects.

RetrieveMultiple - Retrieves a collection of entity instances based on the specified query criteria. This returns a collection of strongly typed objects. Only the properties you specify to be returned are populated in the objects though. You can use intellisense on these objects. Can only operate on one entity, and gives Object Oriented filtering, paging, etc. This is the fastest for returning multiple rows for a given entity.

Update - Updates an entity instance. This uses a strongly typed object. You can use intellisense on these objects.

Fetch - Retrieves entity instances in XML format based on the specified query expressed in the FetchXML query language. This does NOT use strongly typed objects, and intellisense doesn’t work so you will have to lookup everything or use a saved query. Both the query and the results are xml based. This is by far faster than RetrieveMultiple or Execute (assuming you don’t need most of the properties in an entity to be brought back). This is also the only choice when you need to left outer join multiple tables. In general, this is your best choice for reporting because of all the joining, and the selecting properties from multiple entities. It is as close to SQL as you are going to get without being SQL. You have to work a little harder to work with this, but performance and flexibility are maximized. NOTE: Retrieve and RetrieveMultiple are technically faster according to the docs, but I think it depends on the query.

Execute - Executes business logic and special operations using messages. A ton of things (Anything that inherits from Request) can be passed to this method and the results are returned in a Response object. Search the Reference.cs for “ : Request” and you will see all objects that inherit from Request. You can join (link) entities, but only for the filter, not for selecting properties.

Samples

For starters download the SDK, or look on MSDN for the same samples, but on the web.

Execute Example

Below is an example of how you can use the Execute method to return multiple rows (entities) for a given query. You can build complex filters, but you can only select from the entity that the query is for. You specify what properties you want to bring back, and when you access the objects in the response you should only access the properties you specified in the request. Paging is built in and easy to do.

private void GetUsersUsingExecute()
{
    RetrieveMultipleResponse results = null;
    int pageNum = 1;
    while (results == null || (results != null && results.BusinessEntityCollection.MoreRecords))
    {

        // specify the columns we want to return
        ColumnSet cols = new ColumnSet();
        cols.Attributes = new string[] { "domainname", "systemuserid" };

        // Create the FilterExpression.
        FilterExpression filter = new FilterExpression();

        PagingInfo pageInfo = new PagingInfo();
        pageInfo.Count = 1000; // the number of rows in each batch
        pageInfo.PageNumber = pageNum;

        // Create the QueryExpression.
        QueryExpression query = new QueryExpression();

        // Set the properties of the QueryExpression.
        query.EntityName = EntityName.systemuser.ToString();
        query.ColumnSet = cols;
        query.Criteria = filter;
        query.PageInfo = pageInfo;
        query.Distinct = true;

        // Create the request object.
        RetrieveMultipleRequest request = new RetrieveMultipleRequest();

        // Set the properties of the request object.
        request.Query = query;

        // Execute the request.
        results = (RetrieveMultipleResponse)GetWebService().Execute(request);

    
        BusinessEntity[] users = results.BusinessEntityCollection.BusinessEntities;
        foreach (systemuser user in users)
        {
            Console.WriteLine(user.systemuserid.Value);
            Console.WriteLine(user.domainname);
        }
    

        pageNum++;
    } // end while

}

FetchXML Example

FetchXML is in my opinion the preferred method to retrieve data from the CRM due to its optimal performance and flexibility.

Helper Methods

You will want to use these methods when you are using FetchXML. You can just copy and paste them. They basically make working with the XML much easier.

private string ExtractNodeValue(XmlNode node, string childNodeName)
{
    XmlNode childNode = node.SelectSingleNode(childNodeName);
    return (childNode == null) ? string.Empty : childNode.InnerText;
}

private string ExtractNodeAttributeValue(XmlNode node, string childNodeName, string childNodeAttributeName)
{
    XmlNode childNode = node.SelectSingleNode(childNodeName);
    return (node == null) ? string.Empty : GetAttriubteValue(childNode, childNodeAttributeName);
}

private bool HasMoreResults(XmlDocument doc)
{
    return GetAttriubteValue(doc.DocumentElement, "morerecords") == "1";
}

private string GetPagingCookie(XmlDocument doc)
{
    
    XmlAttribute attribute = doc.DocumentElement.Attributes["paging-cookie"];
    return (attribute != null) ? attribute.InnerXml : string.Empty;
}

private string GetAttriubteValue(XmlNode node, string attributeName)
{
    XmlAttribute attribute = node.Attributes[attributeName];
    return (attribute != null) ? attribute.Value : string.Empty;
}



private string SetPagingInfo(string fetchXml, string pagingCookie, int pageNumber, int fetchCount)
{
    return string.Format(fetchXml, pageNumber, fetchCount, pagingCookie);
}

private string FormatXmlQuery(string xmlQuery)
{
    // add some white space for readability
    xmlQuery = xmlQuery.Replace("<", "\r\n<");
    xmlQuery = xmlQuery.Replace("<link-entity", "\r\n<link-entity");
    xmlQuery = xmlQuery.Replace("<filter", "\r\n<filter");
    xmlQuery = xmlQuery.Replace("<order", "\r\n<order");
    return xmlQuery;
}

XML Query

I store this in an XML file and read it in when I need it. Otherwise, trying to do this inline in code is messy and error prone. I STRONGLY recommend this method or at least not putting inline in your code. This example is complex for several reasons. To start with, there are several entities that are linked together and I am selecting attributes from each of them. There are even some outer joins here. Anything with mycustom_ in the name are properties that are only in my CRM; they are custom properties setup in the CRM. The alias attribute of the link-entity tag is ugly here, but is actually what is generated by the UI. The important thing to know is I can change it here, but it also has to be changed in my code. This xml also has c# placeholders for supporting paging. Also, note that I am not filtering the rows. I am selecting all rows (paged). I could have added a filter though.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" page="{0}" count="{1}" paging-cookie='{2}'>

    
    <entity name="opportunity">

        <attribute name="mycustom_opportunity_revenue"/>
        <attribute name="mycustom_customer_part_number"/>

        <link-entity name="mycustom_project" from="mycustom_projectid" to="mycustom_projectid" visible="false" link-type="outer" alias="opportunitymycustom_projectidmycustom_projectmycustom_projectid">
            <attribute name="mycustom_customer_description"/>
            <attribute name="mycustom_name"/>
        </link-entity>

        <link-entity name="systemuser" from="systemuserid" to="owninguser" visible="false" link-type="outer" alias="opportunityowningusersystemusersystemuserid">
            <attribute name="domainname"/>
        </link-entity>

        <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="opportunitycustomeridaccountaccountid">
            <attribute name="name"/>
            <attribute name="mycustom_baseregion"/>
        </link-entity>

        <link-entity name="mycustom_mag" from="mycustom_magid" to="mycustom_magid" visible="false" link-type="outer" alias="opportunitymycustom_magidmycustom_magmycustom_magid">
            <attribute name="mycustom_name"/>
        </link-entity>

        <link-entity name="product" from="productid" to="mycustom_12ncid" visible="false" link-type="outer" alias="opportunitymycustom_12ncidproductproductid">
            <attribute name="name"/>
        </link-entity>
        
        
        
    </entity>

</fetch>

The FetchXML code

The constants below MUST match EXACTLY those that are in the XML Query file that is read in the method below. The code below supports paging and reading the XML query from a file. This code requires the Helper methods shown above. There are no strongly typed properties or anything like that. If you need your data as anything except string, you will need to convert it. This is actually a small price to pay for having the most flexibility and performance possible when using the web service (obviously direct database access would be the fastest).

// NOTE: These need to match the XML Query
private readonly string OWNER_ALIAS = "opportunityowningusersystemusersystemuserid";
private readonly string PROJECT_ALIAS = "opportunitymycustom_projectidmycustom_projectmycustom_projectid";
private readonly string CUSTOMER_ALIAS = "opportunitycustomeridaccountaccountid";
private readonly string MAG_ALIAS = "opportunitymycustom_magidmycustom_magmycustom_magid";
private readonly string MYCUSTOM12NC_ALIAS = "opportunitymycustom_12ncidproductproductid";

public void GetOpportunitiesSampleUsingFetchXML()
{
    // Set up the CRM Service.
    CrmService service = GetWebService();

    // Define the fetch attributes.
    // The number of records per page to retrieve.
    int fetchCount = 500;

    // Initialize the page number.
    int pageNumber = 1;
   
    // The current paging cookie. For retrieving the first page, 
    // pagingCookie should be null.
    string pagingCookie = null;

    // read the query from file
    string xmlQueryText = File.ReadAllText("Query.xml");
    string fetchXml = xmlQueryText;


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

        // Execute the fetch query and get the xml result.
        string fetchResult = service.Fetch(xml);

        // Load the fetch result into XMLDocument to parse its cotents.
        XmlDocument doc = new XmlDocument();
        doc.LoadXml(fetchResult);

        // The paging-cookie attribute holds the paging cookie to pass in the next query.
        pagingCookie = GetPagingCookie(doc);

        // Retrieve the result nodes.
        XmlNodeList resultNodes = doc.DocumentElement.SelectNodes("result");

        // Retrieve all records from the result set.
        foreach (XmlNode resultNode in resultNodes)
        {
            string region = ExtractNodeValue(resultNode, CUSTOMER_ALIAS + ".mycustom_baseregion");
            string mycustom_customer_description = ExtractNodeValue(resultNode, PROJECT_ALIAS + ".mycustom_customer_description");
            string mycustom_project_name = ExtractNodeValue(resultNode, PROJECT_ALIAS + ".mycustom_name");
            string mycustom_customer_part_number = ExtractNodeValue(resultNode, "mycustom_customer_part_number"); 
            string mycustom_opportunity_revenue = Convert.ToDecimal(ExtractNodeValue(resultNode, "mycustom_opportunity_revenue"));
            string ownerAccount = ExtractNodeValue(resultNode, OWNER_ALIAS + ".domainname");
            string dwaccount = ExtractNodeValue(resultNode, CUSTOMER_ALIAS + ".name");
            string mycustom_mag_name = ExtractNodeValue(resultNode, MAG_ALIAS + ".mycustom_name");
            string mycustom_12nc_name = ExtractNodeValue(resultNode, MYCUSTOM12NC_ALIAS + ".name");

            string opportunityid = ExtractNodeValue(resultNode, "opportunityid");

        }

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

    } // end while

}

Other Resources for FetchXML

Fetch XML Schema - Docs, Examples, Syntax, etc

Intro to FetchXML and see Other Resources on it for great blogs

Retrieve Example

Here is a tweaked example from the docs for the Retrieve method. This works very similarly to the Execute Example with regards to the QueryExpression.

CrmService service = GetWebService();

// Create the column set object that indicates the properties to be retrieved.
ColumnSet cols = new ColumnSet();

// Set the properties of the column set.
cols.Attributes = new string [] {"fullname"};

// contactGuid is the GUID of the record being retrieved.
Guid contactGuid = new Guid("4D507FFE-ED25-447B-80DE-00AE3EB18B84");

// Retrieve the contact.
// The EntityName indicates the EntityType of the object being retrieved.
contact contact = (contact)service.Retrieve(EntityName.contact.ToString(), contactGuid, cols);

Retrieve Multiple Example

Here is a tweaked example from the docs for the Retrieve method. This works very similarly to the Execute Example with regards to the QueryExpression.

CrmService service = GetWebService();

// Create the ColumnSet that indicates the properties to be retrieved.
ColumnSet cols = new ColumnSet();

// Set the properties of the ColumnSet.
cols.Attributes = new string [] {"fullname", "contactid"};

// Create the ConditionExpression.
ConditionExpression condition = new ConditionExpression();

// Set the condition for the retrieval to be when the contact's address' city is Sammamish.
condition.AttributeName = "address1_city";
condition.Operator = ConditionOperator.Like;
condition.Values = new string [] {"Sammamish"};

// Create the FilterExpression.
FilterExpression filter = new FilterExpression();

// Set the properties of the filter.
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] {condition};

// Create the QueryExpression object.
QueryExpression query = new QueryExpression();

// Set the properties of the QueryExpression object.
query.EntityName = EntityName.contact.ToString();
query.ColumnSet = cols;
query.Criteria = filter;

// Retrieve the contacts.
BusinessEntityCollection contacts = service.RetrieveMultiple(query);

Generate FetchXML query using UI

If you want to use FetchXML, but are overwhelmed or just don’t want to think that hard you are in luck. The Advanced Find in the user interface allows you to save queries that you build using their ultra cool query builder. Lucky for us they are saved in FetchXML format, so all we have to do is read the saved query from the CRM and tweak it for our uses.

The code to Extract the Saved Query from the CRM

public string GetUserQuery(string queryName)
{
    // specify the columns we want to return
    ColumnSet cols = new ColumnSet();
    cols.Attributes = new string[] { "fetchxml"};
    
    // Create a ConditionExpression.
    ConditionExpression nameCond = new ConditionExpression();
    nameCond.AttributeName = "name";
    nameCond.Operator = ConditionOperator.Equal;
    nameCond.Values = new object[] { queryName};

    FilterExpression filter = new FilterExpression();
    filter.FilterOperator = LogicalOperator.And;
    filter.Conditions = new ConditionExpression[] { nameCond };

    // Create the QueryExpression.
    QueryExpression query = new QueryExpression();
    query.EntityName = EntityName.userquery.ToString();
    query.ColumnSet = cols;
    query.Criteria = filter;
    query.Distinct = true;

    // Create the request object.
    RetrieveMultipleRequest request = new RetrieveMultipleRequest();
    request.Query = query;

    // Execute the request.
    RetrieveMultipleResponse results = (RetrieveMultipleResponse)GetWebService().Execute(request);

    if (results.BusinessEntityCollection.BusinessEntities.Length > 0)
    {
        userquery result = (userquery)results.BusinessEntityCollection.BusinessEntities[0];

        string xmlQuery = result.fetchxml;
        xmlQuery =  FormatXmlQuery(xmlQuery);
        return xmlQuery;

    }
    else
    {
        return string.Empty;
    }
    


}

Just call the method and write the results to a file or console. Then

string xmlQuery = p.GetUserQuery("My Custom Query");
Console.WriteLine("Saved Query: " + xmlQuery);

Once you have the query, you will need to modify the fetch tag to look like the one in my FetchXML example.

Here is an alternate way to do the same thing. Be careful if your query is complex with outer joins, etc, you will lose some of that when you convert to a QueryExpression. If you have the ID or get it as described in the alternate way link, you can use the code below to get the actual FetchXML stored in the CRM.

public string GetUserQuery(Guid queryId)
{
    CrmService ws = GetWebService();
    userquery userQuery = (userquery)ws.Retrieve(EntityName.userquery.ToString(), queryId, new AllColumns());
    string xmlQuery = userQuery.fetchxml;
    xmlQuery = xmlQuery.Replace("<", "\r\n<");

    return xmlQuery;
}

Switching Between FetchXML and QueryExpression

There are two methods that all you to switch between FetchXML and QueryExpression. They are FetchXmlToQueryExpression and QueryExpressionToFetchXml. See here for more details on the subject. A word of warning, the documentation does NOT tell you this, but you cannot do outer joins or handle selects from anything but the main entity. If this is possible, someone please let me know. For example, if I try to convert the XML in the FetchXML example to a QueryExpression, I will lose the columns I selected when I look at the results. On the other hand, I can go from any QueryExpression to FetchXML syntax without any problem because FetchXML is much more expressive.

Additional Resources

After you read my entry, you may want to start here for info on how to use queries, etc.

Top most Docs for all versions of Microsoft Dynamics CRM at: http://technet.microsoft.com/en-us/library/bb496810.aspx