MarkItUp

Creating a FetchXML 'In' clause for a CRM query

Categories

• CRM

One of the ways to query Crm is to use FetchXML which allows you to send XML queries off to the CRM server and get results back.  There's a page here which shows some Fetch XML Examples:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk1_2/htm/v1d2fetchxmlexamples.asp

As you can see, a simple query might look something like this:

<fetch mapping="logical">
  <entity name="serviceappointment">
    <attribute name="subject" />
    <filter type="and">
      <condition attribute="statuscode" operator="eq" value="4" />
    </filter>
  </entity>
</fetch>

The values that can be accepted for the operator on the condition are:

<AttributeType name='operator' dt:type='enumeration'
    dt:values='lt gt le ge eq ne null not-null in not-in between
    not-between like not-like yesterday today tomorrow
    next-seven-days last-seven-days next-week last-week this-month
    last-month next-month on on-or-before on-or-after this-year
    last-year next-year eq-userid ne-userid eq-businessid
    ne-businessid' required='yes' default='eq' />

Today I was the first time that I'd needed to use the 'In' clause of the condition and was having trouble working it out.  Finally I decided to reverse engineer a solution by writing code which you can then convert to FetchXML format.  Here is the code that I had to write:

CrmService service = new CrmService();
service.Credentials = CredentialCache.DefaultCredentials;

//Create the base query
QueryExpression query = new QueryExpression();
query.ColumnSet = new AllColumns();
query.EntityName = EntityName.serviceappointment.ToString();

// Add an 'In' condition to the query
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "regardingobjectid";
condition.Operator = ConditionOperator.In;

object[] guids = new object[] {
    new Guid("7522228E-FFFF-DB11-B7BC-00142209BBBB"),
    new Guid("A00007DA-FFFF-DB11-B7BC-00142209BBBB"),
    };
condition.Values = guids;

FilterExpression filter = new FilterExpression();
filter.Conditions = new ConditionExpression[] { condition };
query.Criteria = filter;

QueryExpressionToFetchXmlRequest expression = new QueryExpressionToFetchXmlRequest();
expression.Query = query;
QueryExpressionToFetchXmlResponse queried = (QueryExpressionToFetchXmlResponse)service.Execute(expression);

string fetchXML = queried.FetchXml;

And here is the Fetch XML formatted solution that is returned by that query - note that when using the 'In' clause that you need to remove the 'value' attribute and use multiple 'value' child nodes instead:

<fetch mapping='logical'>
    <entity name='serviceappointment'>
        <all-attributes />
        <filter type='and'>
            <condition attribute='regardingobjectid' operator='in'>
                <value>7579A28E-FC3A-DB11-B7BC-00142209B048</value>
                <value>A0A9A7DA-F23A-DB11-B7BC-00142209B048</value>
            </condition>
        </filter>
    </entity>
</fetch>