Posted on 5. January 2012

Convert CRM2011 LINQ Query into QueryExpression / FetchXml

With the introduction of Linq within the Dynamics CRM 2011 sdk, I've more or less stopped using QueryExpressions and FetchXml in server side code. This makes it increasingly painful when I've got to convert my queries to fetchXml or QueryExpressions for use in Javascript or Silverlight code. This sample shows how you can convert those linq queries into a QueryExpression or FetchXml by way of a command line utility.

This is a useful tool for debugging your linq queries as well to check that they are going to execute as you expect - however I would highly recommend LinqPad for a more indepth debuger/learning tool (www.linqpad.net).

This sample also demonstrates how to compile a Linq query from a text string - although this is something that shouldn't be done to get round the lack of dynamic query support in Linq - please see :  http://www.develop1.net/public/post/Dynamically-construct-a-where-query-on-a-Dynamics-CRM-2011-Linq-query.aspx  for an example of how to do this.

The main challenge was how to find the Query Expression from a linq query. This is solved by accessing a private method named 'GetQueryExpression'. 

QueryExpression query = (QueryExpression)linq.Provider.GetType().InvokeMember("GetQueryExpression", BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, linq.Provider, arguments);

When you run the sample, you are prompted first to enter the details of your CRM server, and then you can enter a linq query. The Context must be named ctx - so a query would look like:

>from c in ctx.CreateQuery<Contact>()
>select c

To convert your query, simply press enter with a blank line.

The query is output to the console as well as saved as a text file for future reference.

Download Sample

Posted on 3. January 2012

Dynamically construct a where query on a Dynamics CRM 2011 Linq query

Linq is an excellent way of homogenising the way we query our data – the CRM 2011 Linq provider is no exception. One thing that we occasionally come up against is the need to query based on a variable number of where clauses.

With FetchXml and QueryExpressions dynamic filters were straightforward as we could simply iteratively construct the query. With Linq however, constructing a dynamic where clause is more complex due to the compile time nature of the queries.

If we had a list of last names that could change a runtime, and we wanted to query contacts that matched theses last names using Linq, the query would look something like:

var contactsNonDynamic = ( 
                        from c in context.CreateQuery() 
                        where c.LastName == lastNames[0] || c.LastName == lastNames[1] || c.LastName == lastNames[2] 
                        select new { Id = c.Id, LastName = c.LastName }); 

If the number of values in the lastNames list is variable, then we are in trouble.

This sample demonstrates how multiple values can be added to the FilterExpression part of a Linq query given a variable number of conditions. It uses the PredicateBuilder and AsExpandable features of LinqKit (http://www.albahari.com/nutshell/linqkit.aspx). LinqKit is included in the sample as a single file to avoid having to GAC or deploy to the bin folder.

Once you've built and deployed the sample (using the Developer Toolkit), update any contacts and the resulting dynamic LINQ query will run the following SQL Query against the MSCRM database:

exec sp_executesql N'select  
top 5001 "contact0".LastName as "lastname" 
, "contact0".ContactId as "contactid"  
from 
 Contact as "contact0"  
where 
 (((("contact0".LastName = @LastName0 or (("contact0".LastName = @LastName1 or "contact0".LastName = @LastName2)))))) order by 
 "contact0".ContactId asc',N'@LastName0 nvarchar(14),@LastName1 nvarchar(16),@LastName2 nvarchar(13)',@LastName0=N'Chand (sample)',@LastName1=N'Francis (sample)',@LastName2=N'Cook (sample)'

Notes:

  1. The sample will throw an exception simply to show your the result of the query when you update a contact.
  2. This will not work with Sandboxed PlugIns due to the code access security policy enforced.
  3. The solution uses the Developer Toolkit that is part of the Dynamics CRM 2011 SDK.
To build the sample, you must install the CRM 2011 developer toolkit that is found within the CRM 2011 SDK at \tools\developertoolkit\crmdevelopertools_installer.msi
 
To deploy to CRM, simply connect to the organisation when promoted with the 'Connect to Dynamics CRM Server' dialog and then select 'Deploy' from the solution menu.