Rss

Convert LINQ to FetchXML

Ever since Dynamics CRM 2011, the power of using LINQ to query CRM data has been fantastic.  The true power and advantage of using LINQ queries however, is not in the query itself.  It is actually in the data returned from the query.  Because LINQ can return exactly what you are looking for, whether its a single entity or a list containing a single column or anything else.  Working with that makes the remainder of you code much shorter and less complicated than if it had returned an EntityCollection.  Because of the great ease in development, I use LINQ almost exclusively when writing code for CRM.

Of course, if you are reading this then I don’t need to explain that there are still many situations when FetchXML or QueryExpression are required.  I’m fairly certain that FetchXML can always be used instead of QueryExpression, but the reverse is not always true.  Making FetchXML the most useful of all CRM queries.  This poses a problem for those of us that are more comfortable writing LINQ queries.  Particularly when the query starts to become more complicated.  So, it would certainly be nice if there was a way that we could write a query in LINQ and then convert it to FetchXML.  Of course, there have been ways of doing this in the past, but as of this writing in the current version of CRM 2013, all of those old methods to convert LINQ to FetchXML are broken.  That includes the LINQ to CRM plugin for LINQPad, and the unsupported method of using reflection to retrieve the QueryExpression from the CRM LINQ Provider.  Every method that I’ve tried is not working.  Using reflection seems to work but only for the simplest possible query.  As soon as even a single where clause is added, the reflection method fails in CRM 2013.  It is probably possible to make that work again, but I haven’t figured it out yet.

What I have found is another way to convert LINQ to FetchXML.  The coolest part of this method is that it is technically supported!  Which means that it should always work, even in future updates that may break other methods, this technique will always be available as a fallback.  So what is the trick?  You’re going to love this:  Use the SOAPLogger from the SDK. Not many developers have used that tool before. Its best use is when developing on non .Net platforms. What it does for us in this case is intercept the Execute call to CRM when you run a LINQ query. That allows us to see the QueryExpression, which we can then convert to FetchXML.

Here’s a sample LINQ query that you can run in the logger:

//You must declare the service context using the provided SoapLoggerOrganizationService
OrganizationServiceContext dataContext = new OrganizationServiceContext(slos);

var query = dataContext.CreateQuery<Account>()
                       .Where(a => a.StateCode.Value == 0)
                       .Select(a => new Account { Name = a.Name, Telephone1 = a.Telephone1})
                       .ToList();

Normally we would only get to see QueryExpression in XML format. But we can easily modify the SoapLogger tool to output FetchXML instead.
In the file SoapLoggerOrganizationService.cs, find this method and modify it to look like this:

public OrganizationResponse Execute(OrganizationRequest request)
{
    var retrievemultiple = (Microsoft.Xrm.Sdk.Messages.RetrieveMultipleRequest)request;

    QueryExpressionToFetchXmlRequest fetchrequest = new QueryExpressionToFetchXmlRequest { Query = retrievemultiple.Query };
    QueryExpressionToFetchXmlResponse fetchresponse = (QueryExpressionToFetchXmlResponse)InnerService.Execute(fetchrequest);

    string result = WebUtility.HtmlDecode(fetchresponse.FetchXml);
    Console.WriteLine(FormatXml(result));
    this.OutputWriter.WriteLine(FormatXml(result));

    return new OrganizationResponse();

    //return this.ExecuteSoapRequest<ExecuteRequest, ExecuteResponse>(new ExecuteRequest(request)).Response;
}

When you run the program now, you will see an error in the console, but we don’t care about that. The query has been found and saved, and that’s all we want. We don’t need to see the query result here. If you want to use the SOAPLogger for normal purposes, you will need to revert those changes. Which is why the original line is left in there just commented out.

When you open the output.txt now, you will see the FetchXML query you are looking for. Here is the output from the sample LINQ above:

<fetch distinct="false" no-lock="false" mapping="logical">
  <entity name="account">
    <attribute name="name" />
    <attribute name="telephone1" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

That is of course a simple query. I could have just written that directly, or used Advanced Find to generate that one. It obviously gets more useful as the query gets more complicated.

869 total views, 11 views today

Leave a Reply

Your email address will not be published. Required fields are marked *