UPDATE: There’s a known issue with the LINQPad Plugin for Microsoft Dynamics CRM 2011 and Office 365 authentication. I cover a workaround here.
Using FetchXML is necessary in many situations with CRM 2011. Using FetchXML becomes even more necessary if you are taking a “design for online” approach to solution building. I’m a big believer that everyone should approach solution design this way because then your solution will truly be portable between CRM Online and CRM OnPrem (i.e. the CRM you install yourself). Typically, the need for FetchXML shows up first when you are building custom SQL Server Reporting Services (SSRS) reports. That’s because FetchXML based reports are the only option in CRM Online. I mentioned in my CRM Online myth busters post that I prefer to use LINQPad + LINQPad Plugin for Microsoft Dynamics CRM 2011 to compose my queries, then convert them to FetchXML. This post is a walkthrough of how I do it. I got the inspiration for the code sample I will present from the following two samples:
Once you’ve installed LINQPad + LINQPad Plugin for Microsoft Dynamics CRM 2011, you need to add some code to the “My Extensions” file in the “My Queries” pane:
Place the code I provide you below within the MyExtensions class under the comment that says to “write custom extension methods here.” Here’s what the file looks like before you’ve added anything to it:
// Write code to test your extensions here. Press F5 to compile and run.
public static class MyExtensions
// Write custom extension methods here. They will be available to all queries.
// You can also define non-static classes, enums, etc.
Here’s the code you need to add under the comment:
public static string ToFetchXml(this IQueryable linqQuery, dynamic orgService)
var queryExpression = GetQueryExpression(linqQuery);
var expressionToFetchXmlRequest = new Microsoft.Crm.Sdk.Messages.QueryExpressionToFetchXmlRequest
Query = queryExpression
var organizationResponse = (Microsoft.Crm.Sdk.Messages.QueryExpressionToFetchXmlResponse)orgService.Execute(expressionToFetchXmlRequest);
private static Microsoft.Xrm.Sdk.Query.QueryExpression GetQueryExpression(IQueryable linqQuery)
object projection = null;
object source = null;
object linkLookups = null;
bool flag = false;
bool flag2 = false;
object arguments = new object;
arguments = (object)linqQuery.Expression;
arguments = (object)flag;
arguments = (object)flag2;
arguments = (object)projection;
arguments = (object)source;
arguments = (object)linkLookups;
Microsoft.Xrm.Sdk.Query.QueryExpression query = (Microsoft.Xrm.Sdk.Query.QueryExpression)linqQuery.Provider.GetType().InvokeMember("GetQueryExpression", BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, linqQuery.Provider, arguments);
In addition to adding the code, you need to add references to a few .NET assemblies. From within the “My Extensions” file, press the F4 key to bring up the properties dialog. Add references to the following assemblies (note your location for CRM 2011 SDK assemblies might be different):
Now, once you’ve written and executed the query that you want in LINQPad:
…you can then wrap the query in parenthesis and call the ToFetchXml() extension method to get the FetchXML:
BAM! This approach has really helped me be productive in composing FetchXML queries. I use this mostly when writing SSRS reports, but also find it useful for other occasions where I need to use FetchXML. See the LINQ Limitations section of the Use LINQ to Construct a Query documentation for more details.