CRM FetchXML

I recently was tasked to interface with Microsoft CRM to retrieve all contacts and their associated account whos first or last name matched a specified search string.   My first thought was to use the RetrieveMultiple Web Service method and QueryExpression provided by CRM SDK.   This worked great, except it did not return the account name for the contact. The only information returned by this method is the information stored in the Contact view in the crm database.

After doing some searching around, the only way for me to resolve this issue was to use FetchXML.  FetchXML allows you to create XML to query CRM and get back an XML representation of the data.

Start by writing your FetchXML query:

  1. string searchString = "Smith";
  2.  
  3. string fetchQuery = @"  
  4.   <fetch mapping='logical'>  
  5.          <entity name='contact'>  
  6.             <attribute name='contactid'/>  
  7.             <attribute name='firstname'/>  
  8.             <attribute name='lastname'/>  
  9.              <filter type='or'>  
  10.                  <condition attribute='firstname' operator='like' value='" + searchString + @"%' />  
  11.                <condition attribute='lastname' operator='like' value='" + searchString + @"%' />  
  12.              </filter>  
  13.              <link-entity name='account' to='accountid'>  
  14.                 <attribute name='accountid'/>  
  15.                 <attribute name='name'/>  
  16.              </link-entity>  
  17.         </entity>  
  18.   </fetch>  
  19.  ";

In the snippet above, i select contactid, firstname, and lastname from the contact entity and filter it to only include contacts whos first name or last name start with the specified search string. Then I link to the account entity by accountid, and select accountid and name from the account entity.

Make a call to the CRM Service:

  1. string result = service.Fetch(fetchQuery);

Following is the xml result returned from the call to the Fetch method:

  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <resultset morerecords="0" paging-cookie="&amp;amp;amp;amp;amp;lt;cookie page=&amp;amp;amp;amp;amp;quot;1&amp;amp;amp;amp;amp;quot;&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;lt;contactid last=&amp;amp;amp;amp;amp;quot;{12345678-ABCD-DCBA-1234-012345679CBA}&amp;amp;amp;amp;amp;quot; first=&amp;amp;amp;amp;amp;quot;{12345678-ABCD-DCBA-1234-01234567D124}&amp;amp;amp;amp;amp;quot; /&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;lt;/cookie&amp;amp;amp;amp;amp;gt;">
  3.   <result>
  4.     <contactid>{12345678-ABCD-DCBA-1234-01234567D124}</contactid>
  5.     <firstname>Smithers</firstname>
  6.     <lastname>Johnson</lastname>
  7.     <accountid.accountid>{12345678-ABCD-DCBA-1234-012345679567}</accountid.accountid>
  8.     <accountid.name>Company A</accountid.name>
  9.   </result>
  10.   <result>
  11.     <contactid>{12345678-ABCD-DCBA-1234-012345679CBA}</contactid>
  12.     <firstname>Joe</firstname>
  13.     <lastname>Smith</lastname>
  14.     <accountid.accountid>{12345678-ABCD-DCBA-1234-012345679ABC}</accountid.accountid>
  15.     <accountid.name>Company B</accountid.name>
  16.   </result>
  17. </resultset>

Create objects to deserialize the response:

First I created a Contact class

  1. public class Contact
  2. {
  3.     [XmlElement("contactid")]
  4.     public Guid ContactId { get; set; }
  5.     [XmlElement("accountid.accountid")]
  6.     public Guid AccountId { get; set; }
  7.     [XmlElement("firstname")]
  8.     public string FirstName { get; set;}
  9.     [XmlElement("lastname")]
  10.     public string LastName { get; set; }
  11.     [XmlElement("accountid.name")]
  12.     public string CompanyName { get; set; }
  13. }

Then create a ContactContainer class

  1. [Serializable]
  2. [XmlRoot("resultset")]
  3. public class ContactContainer
  4. {
  5.     private List _contacts = null;
  6.     public ContactContainer() { }
  7.     [XmlElement("result")]
  8.     public List Contacts
  9.     {
  10.         get { return _contacts; }
  11.         set { _contacts = value; }
  12.     }
  13. }

Finally, Deserialize the XML response:

  1. ContactContainer contacts = null;
  2. XmlSerializer serializer = new XmlSerializer(typeof(ContactContainer));
  3. StringReader sReader = new StringReader(result);
  4. contacts = (ContactContainer)serializer.Deserialize(sReader);