September 24, 2015

Top 8 errors made by Dynamics CRM developers - Part 2 - Not choosing the correct query method

This is the second post in a 8 part series describing the most common errors made by Dynamics CRM developers. This time I would like to focus on:

Issue #2 - Not choosing the correct query method


Dynamics CRM offers 3 basic SDK based methods to retrieve data (I will not cover direct SQL access). In historical order those are: FetchXML, QueryExpression and LINQ. 

Below is an example on how using each of those methods retrieve the first and last names of contacts which city starts with the letter A.

IOrganizationService orgService = new OrganizationService("CRM");

// ----------
// Parameters
// ----------   
int topCount = 10;
string letterToSearchFor = "a";

// ---------------
// Using Fetch XML
// ---------------
string fetchXml = String.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical'
                                            distinct='false' page='1' count='{0}'>
                                    <entity name='contact'>                                                
                                    <attribute name='firstname' />                                               
                                    <attribute name='lastname' />    
                                    <order attribute='firstname' descending='false' />
                                    <filter type='and'>
                                        <condition attribute='address1_city' operator='like' value='{1}%' />
                                    </filter>
                                    </entity>
                                </fetch>", topCount, letterToSearchFor);

EntityCollection ecollFetch = orgService.RetrieveMultiple(new FetchExpression(fetchXml));

foreach (Entity contact in ecollFetch.Entities)
{
    string firstName = contact.Attributes.ContainsKey("firstname") ? (string)contact["firstname"] ?? "" : "";
    string lastName = contact.Attributes.ContainsKey("lastname") ? (string)contact["lastname"] ?? "" : "";

    Console.WriteLine("First name: {0}   Last name: {1}", firstName, lastName);
}

// ---------------------
// Using QueryExpression
// ---------------------
QueryExpression query = new QueryExpression("contact");
query.ColumnSet = new ColumnSet("firstname", "lastname");
query.Criteria.AddCondition("address1_city", ConditionOperator.BeginsWith, letterToSearchFor);
query.AddOrder("firstname", OrderType.Ascending);
query.PageInfo = new PagingInfo() { PageNumber = 1, Count = topCount };

EntityCollection ecollQueryExpression = orgService.RetrieveMultiple(query);

foreach (Entity contact in ecollQueryExpression.Entities)
{
    string firstName = contact.Attributes.ContainsKey("firstname") ? (string)contact["firstname"] ?? "" : "";
    string lastName = contact.Attributes.ContainsKey("lastname") ? (string)contact["lastname"] ?? "" : "";

    Console.WriteLine("First name: {0}   Last name: {1}", firstName, lastName);
}

// ----------
// Using LINQ
// ----------
DataContext data = new DataContext(orgService);

var contacts = (from c in data.ContactSet
                where c.Address1_City.StartsWith(letterToSearchFor)
                orderby c.FirstName
                select new
                {
                    c.FirstName,
                    c.LastName
                }).Take(topCount).ToList();

foreach (var contact in contacts)
{
    Console.WriteLine("First name: {0}   Last name: {1}", contact.FirstName, contact.LastName);
}

Each next version is more readable and less error prone. Why use bloated XML when you can build a QueryExpression? Why use QueryExpression if you can use, the more readable, type safe and with Intellisense support, LINQ?

Off course LINQ is not always the answer. Although possible it gets quite messy when using dynamic queries. If you don't know the attribute names during build - QueryExpression seems to be the best choice. Another reason to use QueryExpression is wanting to get passed the 5000 (or else defined) query limit by using paging.
When to use FetchXML? I know only one reason you would want to do that - when building aggregate queries, described here - https://msdn.microsoft.com/en-us/library/gg309565.aspx. If you want to retrieve a count of records or total of some field, use FetchXML aggregates. It will be much better performance wise, because the aggregation is done directly in SQL instead of retrieving all the data and calculating it later. Any other reason? Cannot think of any.

To summarize the following rules should be used:

  • FetchXML should only be used when utilizing aggregate queries, else:
  • QueryExpression should be used when:
    • The query has dynamic attribute names
    • Paging is required, in most cases when the expected number of results is greater than the limit (5000 by default).
  • In all other cases type safe LINQ queries should be used.

How to spot this?


  • Fetch XML is used, without it being an aggregate query
  • QueryExpression is used with well known attribute names
Posted by at