How to generate Fetch XML from LINQ using LinqPad

Linqpad is a great tool to query any system using LINQ.

Fetch XML with LinqPad

Fetch XML with LinqPad

If we want to use this tool connected with CRM we need to install a plugin that you can get by going to the downloads tab in the following site.

Generating Fetch From Linq

Once we have LinqPad properly installed and we are able to query results in CRM it would be great to be able to convert those Linqueries into Fetch. To do so we can follow the instructions described by Keydet (Microsoft Employee) in this great post.

Basically we need to add into “My Extensions File” the following code in the MyExtensions class.

public static class MyExtensions
{
	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);
	
		return organizationResponse.FetchXml;
	}
 
	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[6];
		arguments[0] = (object)linqQuery.Expression;
		arguments[1] = (object)flag;
		arguments[2] = (object)flag2;
		arguments[3] = (object)projection;
		arguments[4] = (object)source;
		arguments[5] = (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);
		return query;
	}
	
}

Then pressing F4 we need to add 3 libraries (Microsoft.crm.sdk.proxy , microsoft.xrm.sdk and System.Runtime.Serialization (.NET 4.0)) and then we will be able to get the Fetch XML from the query by adding .ToFetchXML(this) to any LinQ statement between parentesis as shown in the image:


(from cm in lema_controlmeasureSet
join intersect in lema_controlmeasure_hazardouseventrevisionSet on cm.Id equals intersect.lema_controlmeasureid.Value
join he in lema_hazardouseventrevisionSet on intersect.lema_hazardouseventrevisionid.Value equals he.Id
where (he.lema_status.Value == 3 || he.lema_status.Value == 4)
select cm).ToFetchXml(this)

Getting QueryExpression from FetchXML

Now from any LinQ we can get easily our FetchXML. Then using the CRM2011 SDK we can easily generate QueryExpresions from Fetch as described here.

I think this was helpfull!!

Regards,

Miguel

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s