Build Queries with FetchXML instead of QueryExpression

Posted on December 23, 2013

1


When developing I like a good QueryExpression because once you have got one in code it’s easy to change it to the new query you are doing.  I usually work out the rough logic with an advanced find and then download the xml to see the names of the fields etc.

You can read some of my great blog posts on QueryExpression’s below or read on for some FetchXML fun

CRM 2011 – QueryExpressions where a field is null

CRM 2011 – How to select an Id in a QueryExpression

CRM 2011 – How to do Like statement in QueryExpression

Recently I was doing a QueryExpression but I need to link the query expression to two or three different entities and the query expression was getting out of hand.  I could do the query with an Advanced find so I was thinking if I could just use that it would be a whole lot easier than trying to convert the FetchXML to a QueryExpression.   So after a bit of googling I found that you can use FetchXML queries inside plugins.

It was while I was studying for CRM 2011 Extending CRM exam that I found this page – Build Queries With FetchXML and this line

A FetchXML query can be executed by using the IOrganizationService.RetrieveMultiple method. You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.

If you want to use FetchXML in your plugin/Custom Workflow you will need to download the FetchXML from your advanced find.  There is a very helpful button on the advanced find that appears after you have run the advanced find, which you can see below magnetismsolutions blog)

You will have to do a bit of conversion from the downloaded fetchXML because all the fields will be in double quotes and these will need to be in single quotes and you will need to put an @ at the front and double quotes around the whole FetchXML query whilst you save it to a string variable.  You then pass the string RetrieveMultiple as shown in the example below

The example below is taken from here

// Retrieve all accounts owned by the user with read access rights to the accounts and 
// where the last name of the user is not Cannon. 
string fetch2 = @"
   <fetch mapping='logical'>
     <entity name='account'> 
        <attribute name='accountid'/> 
        <attribute name='name'/> 
        <link-entity name='systemuser' to='owninguser'> 
           <filter type='and'> 
              <condition attribute='lastname' operator='ne' value='Cannon' /> 
           </filter> 
        </link-entity> 
     </entity> 
   </fetch> "; 

EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch2));

foreach (var c in result.Entities)
   {
   System.Console.WriteLine(c.Attributes["name"]);
   }

The results are same EntityCollection you would get from a QueryExpression, which makes it easy to reuse code you have written for query expressions.

The other thing you will probably need to do is integrate values and guids into the FetchXML.  You can pass in a guid and then put in double quotes, the guid variable with a + sign either side.  You can see the example below

Guid contactGuidVariable;

EntityReference contactEntityRef;

string fetch = @”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>
<entity name=’contact’>
<attribute name=’fullname’ />
<attribute name=’address1_upszone’ />
<attribute name=’address1_line1′ />
<attribute name=’address1_postalcode’ />
<attribute name=’createdon’ />
<attribute name=’contactid’ />
<order attribute=’fullname’ descending=’false’ />
<filter type=’and’>
<condition attribute=’statecode’ operator=’eq’ value=’0′ />
<condition attribute=’contactid’ operator=’eq’ uiname=’BEN HOSK’ uitype=’contact’ value='” + ContactGuidVariable + “‘ />
</filter>
</entity>
</fetch>”

if you pass in a guid you can just pass in the guid variable but if it’s an EntityReference then you would need to contactEntityRef.Id

Microsoft have written some good articles on this you can read here

Build Queries With FetchXML

Use FetchXML to Construct a Query – this one is good because it has code examples, which I have borrowed in this blog

Some good articles on this are

http://www.magnetismsolutions.com/blog/roshanmehta/2012/04/16/dynamics_crm_2011_querying_data_using_fetchxml

About these ads