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.

Comments

trackback Develop 1 Limited Blog
4:00 PM on Thursday, January 05, 2012

Convert CRM2011 Linq into a Query Expression and FetchXml

Convert CRM2011 Linq into a Query Expression and FetchXml

pingback com-lab.biz
12:26 AM on Wednesday, August 07, 2013

Pingback from com-lab.biz

How do you compile LINQ queries against CRM 2011? | user60

Comments are closed