My practices on FetchXML (MS Dynamics CRM 4.0)

  • Article

 

Since few months, I work on developing a WPF UI that directly connects to MS Dynamics CRM 4.0. In this post I will present you some of the practices I use in this project.

Restriction of the result

Reduce the attributes to return

Specify all desired attributes and not all-attributes element. all-attributes will return all fields of the desired entities:

<attribute name=”…” />

<attribute name=”…” />

instead of:

<all-attributes />

I really recommend you to manually set all the attributes you need. This will reduce the size of the response and in a same time, this reduce the SQL request generated by CRM.

Code Snippet

  1. <fetch distinct="true" mapping="logical">
  2.   <entity name="contact">
  3.     <attribute name="contactid" />
  4.     <attribute name="gendercode" />
  5.     <attribute name="firstname" />
  6.     <attribute name="lastname" />
  7.   </entity>
  8. </fetch>

 

Take few minutes to create your own paging mechanism.

Step 1 :

  • Create a context object for the server calls:

Code Snippet

  1. /// <summary>
  2. /// Context object to keep between all crm calls
  3. /// </summary>
  4. public class PaginationContext
  5. {
  6.     /// <summary>
  7.     /// Id of the page
  8.     /// </summary>
  9.     public int Page { get; set; }
  10.     /// <summary>
  11.     /// Number of element to return
  12.     /// </summary>
  13.     public int ElementCount { get; set; }
  14.     /// <summary>
  15.     /// Paging cookie
  16.     /// </summary>
  17.     public string PagingCookie { get; set; }
  18.     /// <summary>
  19.     /// HAs more records to return
  20.     /// </summary>
  21.     public bool HasMoreRecords { get; set; }
  22. }
  • Encapsulate the query encapsulation and parse the return of the FetchXML query:

Code Snippet

  1. /// <summary>
  2. /// Generic mechanism to paginate
  3. /// </summary>
  4. /// <param name="query"></param>
  5. /// <param name="context"></param>
  6. /// <param name="paginationcontext"></param>
  7. /// <returns></returns>
  8. public static XElement PaginateResults(this StringBuilder query,
  9.     CrmDataContext context,
  10.     PaginationContext paginationcontext)
  11. {
  12.  
  13.     XElement result = null;
  14.     XDocument fetchQueryDoc = XDocument.Parse(query.ToString());
  15.     XElement rootQueryElement = fetchQueryDoc.Element("fetch");
  16.  
  17.     // Initialize the number od records to return
  18.     XAttribute countAttribute = rootQueryElement.Attribute("count");
  19.     if (rootQueryElement.Attribute("count") != null)
  20.         countAttribute.Value = paginationcontext.ElementCount.ToString();
  21.     else
  22.         rootQueryElement.Add(new XAttribute("count", paginationcontext.ElementCount.ToString()));
  23.  
  24.     // Initialize the page id
  25.     XAttribute pageAttribute = rootQueryElement.Attribute("page");
  26.     if (rootQueryElement.Attribute("page") != null)
  27.         pageAttribute.Value = paginationcontext.Page.ToString();
  28.     else
  29.         rootQueryElement.Add(new XAttribute("page", paginationcontext.Page.ToString()));
  30.  
  31.     // Initialize the paging cookie
  32.     XAttribute pagingCookieAttribute = rootQueryElement.Attribute("paging-cookie");
  33.     if (rootQueryElement.Attribute("paging-cookie") != null)
  34.         pagingCookieAttribute.Value = paginationcontext.PagingCookie.ToString();
  35.     else
  36.         rootQueryElement.Add(new XAttribute("paging-cookie", paginationcontext.PagingCookie.ToString()));
  37.  
  38.     using (var service = context.CreateService())
  39.     {
  40.         string fetchResult = service.Fetch(fetchQueryDoc.ToString());
  41.         XDocument resultDoc = XDocument.Parse(fetchResult);
  42.         if (resultDoc != null)
  43.         {
  44.             XElement rootElement = resultDoc.Element("resultset");
  45.             if (rootElement.Attribute("morerecords") != null && rootElement.Attribute("morerecords").Value == "1")
  46.                 paginationcontext.HasMoreRecords = true;
  47.             else
  48.                 paginationcontext.HasMoreRecords = false;
  49.             if (rootElement.Attribute("paging-cookie") != null && string.IsNullOrEmpty(rootElement.Attribute("paging-cookie").Value) == false)
  50.             {
  51.                 string pagingCookie = rootElement.Attribute("paging-cookie").ToString().TrimStart("paging-cookie=\"".ToCharArray()).TrimEnd('"');
  52.             }
  53.             result = rootElement;
  54.         }
  55.     }
  56.     return result;
  57.  
  58. }
  • Test the code:

Code Snippet

  1. [TestMethod]
  2. public void TestPagination()
  3. {
  4.     CrmDataContext context;
  5.     // ...
  6.     // Code to connect to CRM
  7.     // ...
  8.     using (var service = context.CreateService())
  9.     {
  10.         FetchXMLHelper.PaginationContext paginationContext = new FetchXMLHelper.PaginationContext() { ElementCount = 50, HasMoreRecords = true };
  11.         do
  12.         {
  13.             StringBuilder fetchXml = new StringBuilder();
  14.             fetchXml.Append("<fetch distinct=\"false\" mapping=\"logical\" >");
  15.             fetchXml.Append("<entity name=\"account\">");
  16.             fetchXml.Append("   <all-attributes />");
  17.             fetchXml.Append("</entity>");
  18.             fetchXml.Append("</fetch>");
  19.  
  20.             XElement element = fetchXml.PaginateResults(context, paginationContext);
  21.             if (element != null)
  22.             {
  23.                 // ...
  24.                 // Code to deserialize the result of the fetch query
  25.                 // ...
  26.             }
  27.         } while (paginationContext.HasMoreRecords);
  28.  
  29.     }
  30. }

Joining entities

FetchXML offers the ability to return fields from parent entity, linq queries not.

Using aliases

Using aliases for you linked entities will facilitate the execution and your deserialization. If you do not set an alias, you will not be able to return fields with same names from different entities.

Code Snippet

  1. <fetch distinct="true" mapping="logical">
  2.   <entity name="contact">
  3.     <attribute name="contactid" />
  4.     <attribute name="gendercode" />
  5.     <attribute name="firstname" />
  6.     <attribute name="lastname" />
  7.     <attribute name="createdby" />
  8.     <attribute name="createdon" />
  9.     <attribute name="modifiedby" />
  10.     <attribute name="modifiedon" />
  11.     <filter type="and">
  12.       ...      
  13.     </filter>
  14.       <link-entity name="account" from="accountid" to="parentcustomerid"alias="accountAlias">
  15.       <attribute name="accountid" />
  16.       ...
  17.       <attribute name="createdby" />
  18.       <attribute name="createdon" />
  19.       <attribute name="modifiedby" />
  20.       <attribute name="modifiedon" />
  21.     </link-entity>
  22.   </entity>
  23. </fetch>

Here is the result of previous FetchXML queyry:

Code Snippet

  1. <resultset>
  2.   <result>
  3.     <contactid>...</contactid>
  4.     <gendercode>...</gendercode>
  5.     <firstname>...</firstname>
  6.     <lastname>...</lastname>
  7.     <createdby>...</createdby>
  8.     <createdon>...</createdon>
  9.     <modifiedby>...</modifiedby>
  10.     <modifiedon>...</modifiedon>
  11.     <accountAlias.accountid>...</accountAlias.accountid>
  12.     ...
  13.     <accountAlias.createdby>...</accountAlias.createdby>
  14.     <accountAlias.createdon>...</accountAlias.createdon>
  15.     <accountAlias.modifiedby>...</accountAlias.modifiedby>
  16.     <accountAlias.modifiedon>...</accountAlias.modifiedon>
  17.   </result>
  18.   ...
  19. </resultset>

In the example, you can now returns createdby (createdon, modifiedby, modifiedon) on two different entities. After in your project, you can easily create you own deserialization mechanism having the alias as parameter.

Left Join

You cannot use left join using Linq. Fetch queries enable left join. This helps you a lot, if you need to return in each contact query specific fields of parent entity, this reduces the number of request to server.

Code Snippet

  1. <fetch distinct="true" mapping="logical">
  2.   <entity name="contact">
  3.     <attribute name="contactid" />
  4.     <attribute name="gendercode" />
  5.     <attribute name="firstname" />
  6.     <attribute name="lastname" />
  7.     <filter type="and">
  8.       ...
  9.     </filter>
  10.     <link-entity name="account" from="accountid" to="parentcustomerid" alias="accountAlias" link-type="outer">
  11.       <attribute name="accountid" />
  12.       ...
  13.     </link-entity>
  14.   </entity>
  15. </fetch>

 

Quick generation of fetch XML query

Here is in 5 points a quick way to generate your FetchXML query:

  • Use the advance find of MS CRM;
  • Use the graphical user interface to define your query;
  • Run the query (click on Find button) ;
  • Press CTRL+N, this will create a new IE window but will keep the context;
  • Replace the url in the address bar
    • javascript:alert(resultRender.FetchXml.value);

This will open a pop-up with the expected FetchXml

clip_image001