Using LINQ in CRM 2011 Plugins

One of the technical points that CRM 2011 has introduced is the ability to use LINQ queries to invoke the CRM Service as opposed to the traditional Execute methods or FetchXML.

This allows the use of both simple LINQ queries to access data within CRM, and also the ability to use typed methods at design-time that combines with Visual Studio IntelliSense to make developing code easier.

This is a subject that this blog has touched upon before when the method was made available in one of the later CRM 4 SDK Releases – however I thought this topic was worth revisiting in the context of CRM 2011, and seeing how we can incorporate LINQ into Plugins.

To break this article down, this fits into two sections:

1. Generating Typed Code for invoking the CRM Service via LINQ
2. Using the Generated Code within CRM 2011 Plugins

Addendum – Registering Device ID

Generating Typed Code for invoking the CRM Service via LINQ

The CrmSvcUtil tool is provided as part of the CRM 2011 SDK for referencing the CRM Services and generating a set of code classes based on the Service WSDL which can then allow for typed design-time LINQ queries in Visual Studio.

(this is obviously similar to the Visual Studio SvcUtil tool for generating code from other Service WSDL files)

The CrmSvcUtil can be found in the Bin folder of the CRM SDK:


image

The CrmSvcUtil within the BIN folder of the CRM 2011 SDK

This utility can be run to generate a set of code files from which you can invoke the CRM 2011 WCF Service, to do this we can run the executable from the command line with the following parameters:

crmsvcutil 
  /url:"[x]/XRMServices/2011/Organization.svc" 
  /username:"[y]" /password:"[z]" 
  /language:cs /namespace:DevCrm /out:"DevCrm.cs" /serviceContextName:Xrm

The values for [x], [y] and [z] then depend on your CRM 2011 Deployment method:


[x] [y] [z]
CRM Online Https Url of your CRM Online Organisation. Your Windows Live Username Your Windows Live Password
On-Premise Http or Https Url of your CRM Server and Organisation Your Domain Username Your Domain Password
IFD Deployment Http or Https Url of the Internet Facing CRM Server for your Organisation Your IFD Username Your IFD Password

NOTE: You can also run this command using a config file as opposed to including all the various parameters in the command line.

Documentation on the other parameters can be found in the CRM 2011 SDK. In some cases for On-Premise or IFD Deployments a Device ID and Password may also be required, see the addendum at the end of this post for more details.

From running the CrmSrvUtil tool, this will produce a large CS/VB file containing code for invoking the CRM 2011 Webservice using typed methods and classes – such that these types will include any custom entities or attributes that exist within the CRM Deployment we have used to generate the code:


image

Viewing the code generated by the CrmSvcUtil tool in Visual Studio

We can then use these types in different projects as the method of connecting the project to the CRM Service.

Using the Generated Code within CRM 2011 Plugins

If we using this code in an external project (say for a separate Web Application or a traditional Console Application) we could compile the code as a separate DLL and then reference this DLL to keep the code separate – almost as a Data Access Layer and a separate Business Logic layer.


image

Compiling the code produced by the CrmSvcUtil in a separate Project to produce a DLL that our Plugin can then reference separately – noting here that both the Microsoft.Xrm.Sdk and System.Runtime.Serialization DLLs must be referenced here to accomplish this.

However in this example of developing a Plugin, the project needs to be self contained for registration to the CRM Database so we instead add the code file to our new Plugin Project directly:

image

Authoring a new Plugin Project in Visual Studio including the CrmSvcUtil generated code

With the generated DevCrm.cs code included in the project, we can then begin building the Plugin logic itself and add the usual code to create a reference to the CRM 2011 Service from the Plugin Context as normal for invoking requests to the service:

IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
// get reference to CRM Web Service
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

However we can then add a further reference to invoke the typed code we have produced from our CRM Deployment via the CrmSvcUtil tool:

 
// Use reference to invoke pre-built types service 
DevCrm.Xrm myCrmService = new DevCrm.Xrm(service); 

This then allows us to reference the CRM Service using typed LINQ methods as opposed to untyped object references – the following table compares a method of counting the number of Opportunities that are associated to a particular Account in CRM, showing the code without the LINQ methods and code with LINQ:

Untyped Object Reference (Without LINQ)

QueryByAttribute queryByAttribute = new QueryByAttribute("opportunity");
queryByAttribute.ColumnSet = new ColumnSet(new string[] { "opportunityid" });
queryByAttribute.Attributes.Add("customerid");
queryByAttribute.Values.Add(accountId);
EntityCollection retrieved = service.RetrieveMultiple(queryByAttribute);
return retrieved.Entities.Count;

Typed LINQ Method

var opportunities = myCrmService.OpportunitySet.Where(i => i.CustomerId.Id == accountId);
return opportunities.ToList().Count;

NOTE: Here we use .ToList().Count to determine the number of returned records from the the Select statement, as the LINQ Count and Sum operations are not supported for using LINQ with CRM 2011.

The LINQ Methods then help to reduce the amount of lines of code and hopefully ensure more human-readable code, particularly as Visual Studio will then also provide IntelliSense when working with the different (possibly customised) CRM Attributes and Entities:


image

Building a LINQ query in Visual Studio using IntelliSense via the typed Classes and Methods generated by the CrmSvcUtil tool for a particular deployment of CRM 2011

This naturally makes invoking multiple queries and searches easier than looking up different Attribute Names, State Codes, Picklists and other values.

To look at using this in practise, we can build a similar plugin to one detailed in this blog’s earlier post concerning Plugins in CRM 2011 with the difference of invoking the CRM 2011 service via typed LINQ methods:

    public class CRM2011Plugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            try
            {
                IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
                Entity opportunityProductEntityImage;
                bool isDelete = false;
                Guid opportunityProductId = Guid.Empty;
                
                #region Get Opportunity Product Entity Image
                if (context.MessageName == "Create")
                {
                    // use Post Image for Create Messages
                    if (context.PostEntityImages.Contains("PostImage") &&
                        context.PostEntityImages["PostImage"] is Entity)
                    {
                        opportunityProductEntityImage = (Entity)context.PostEntityImages["PostImage"];
                    }
                    else
                    {
                        throw new Exception("No Post Image Entity in Plugin Context for Create Message");
                    }
                }
                else if (context.MessageName == "Update")
                {
                    // use Post Image for Create Messages
                    if (context.PostEntityImages.Contains("PostImage") &&
                        context.PostEntityImages["PostImage"] is Entity)
                    {
                        opportunityProductEntityImage = (Entity)context.PostEntityImages["PostImage"];
                    }
                    else
                    {
                        throw new Exception("No Post Image Entity in Plugin Context for Create Message");
                    }
                }
                else if (context.MessageName == "Delete")
                {
                    // use Pre Image for Delete Messages
                    if (context.PreEntityImages.Contains("PreImage") &&
                        context.PreEntityImages["PreImage"] is Entity)
                    {
                        opportunityProductEntityImage = (Entity)context.PreEntityImages["PreImage"];
                        isDelete = true;
                    }
                    else
                    {
                        throw new Exception("No Pre Image Entity in Plugin Context for Delete Message");
                    }
                }
                else
                {
                    // Plugin not valid for any other Message Types
                    throw new Exception("SumOpportunityProducts Plugin is not valid for the '" + context.MessageName + "' message type");
                }

                if (opportunityProductEntityImage.LogicalName != "opportunityproduct")
                {
                    // plugin is valid for this entity
                    throw new Exception("SumOpportunityProducts Plugin is not valid for the '" + opportunityProductEntityImage.LogicalName + "' entity type");
                }
                #endregion

                #region Plugin Business Logic

                if (isDelete == true)
                {
                    opportunityProductId = opportunityProductEntityImage.Id;
                }

                // Opportunity Product must have a reference to an Opportunity
                if ( opportunityProductEntityImage.Attributes.Contains("opportunityid"))
                {
                    EntityReference opportunityId = (EntityReference) opportunityProductEntityImage.Attributes["opportunityid"];
                    // get reference to CRM Web Service
                    IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                    IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
                    // Use reference to invoke pre-built types service
                    DevCrm.Xrm myCrmService = new DevCrm.Xrm(service);

                    decimal opportunityProductsTotalValue = 0;

                    // get value of total Opportunity Products
                    var opportunityProducts = myCrmService.OpportunityProductSet
                        // conditions
                        .Where(i => i.OpportunityId.Id == opportunityId.Id)
                        // if a PreState Delete Operation - do not include the Opportunity Product we are deleting!
                        .Where(i => i.Id != opportunityProductId)
                        // operation
                        .Select(i => new { i.Id, i.ExtendedAmount });

                    foreach (var op in opportunityProducts)
                    {
                        if (op.ExtendedAmount != null)
                        {
                            opportunityProductsTotalValue += op.ExtendedAmount.Value;
                        }
                    }

                    // get Opportunity
                    var associatedOpportunity = myCrmService.OpportunitySet.FirstOrDefault(i => i.Id == opportunityId.Id);

                    associatedOpportunity.new_customamountfield = new Money();                    
                    associatedOpportunity.new_customamountfield.Value = opportunityProductsTotalValue;

                    // flag the opportunity as being updated
                    myCrmService.UpdateObject(associatedOpportunity);

                    // save changes made via the LINQ queries
                    myCrmService.SaveChanges();                    
                }
                else
                {
                    throw new Exception("Opportunity Product does not have an association back to an Opportunity");
                }

                #endregion
            }
            catch (Exception ex)
            {
                throw new InvalidPluginExecutionException("LINQPlugin.SumOpportunityProducts --> Execute'" + ex.Message + "'");
            }
        }
    }

This simple plugin uses this LINQ method of invoking the CRM Service to sum the total value of Opportunity Products to a custom field on the Opportunity – essentially duplicating CRM’s native functionality to tally the Opportunity Products.

This would be useful if we were creating a Plugin to tally a custom entity associated to the Opportunity, or had added other custom fields to the Opportunity Product entity which we needed to be tallied on the parent Opportunity – however here this serves as a simple example of how we could use LINQ in CRM 2011 Plugins, and how this can reduce the volume of code required for our Business Logic.

To Register this Plugin, we would then compile and run the Plugin Registration tool and register the DLL against the relevant steps required – in this instance PostCreate, PostUpdate and PreDelete of the OpportunityProduct entity:


image

Registering the LINQ Plugin into CRM 2011 against the Opportunity Product entity with PostCreate, PostUpdate and PreDelete steps

As often with Plugins – it can be better to test the code using the Synchronous steps as opposed to the Asynchronous steps as these will display errors on-screen as opposed to the being hidden within the CRM Asynchronous service.

This concept of using LINQ queries as opposed to manually building Fetch XML in the form of QueryExpression and Execute messages is a useful technique for managing custom code – it is more suited for large code bases and is often used for the CRM Portal Projects, however it is also useful within Plugins or Plugin Projects where we want to incorporate quick queries to and from CRM.

Further Reading

  • The following post is from the Dynamics CRM Team Blog and provides a download to a LINQPad tool that can be used to test LINQ Queries against a deployment of CRM 2011, and translate LINQ Queries into Fetch XML – useful for testing LINQ statements before using them in code projects:

    http://blogs.msdn.com/b/crm/archive/2011/01/11/a-better-way-to-learn-linq-to-crm-linqpad-plugin-for-ms-crm-2011-is-available.aspx

  • This blog post from Pogo69 gives a very comprehensive overview of LINQ for CRM 4, most of which is equally applicable for using LINQ with CRM 2011.

    Pogo69, The Missing LINQ CRM 4.0 SDK Advanced Developer Extensions

  • Addendum – Registering Device ID

    Often when working with the CrmSvcUtil we may need to register the Development Workstation as a device, and receive a Device ID and Password – this is not required for use when using a CRM Online deployment as the CrmSvcUtil will automatically register your development workstation, however can be required for On-Premise or IFD Deployments.

    The SDK contains a Code Project titled DeviceRegistration – in order to obtain a DeviceId and DevicePassword that the CrmSrvUtil tool can require, we must compile this project and run the executable that is produced.

    NOTE: We do not need to worry about any of the code in this project, we simply need to compile the code as-is from the SDK.


    image

    The Device Registration Project within the CRM 2011 SDK


    image

    Compiling the Device Registration Tool in Visual Studio

    This will then either register your workstation for a Device ID and Password – or if the device is already registered, inform you of your device id and password.

    image

    Running the compiled Registration Tool to generate a unique Device ID and Password

    We can then invoke this Device ID and Password when running the CrmSvcUtil tool included in the SDK.

    Advertisements
    This entry was posted in CRM 2011, Development, LINQ, Technical, xRM and tagged , , . Bookmark the permalink.

    9 Responses to Using LINQ in CRM 2011 Plugins

    1. The following article is excellent for showing how to reduce the size of the CrmSvcUtil generated code to allow for inclusion in Plugins.

      As there is a fixed maximum size for registering a Plugin, this is useful when working on larger CRM 2011 Projects.

      Erik Pool: Filtering Generated Entities with CrmSvcUtil

    2. Pingback: Using LINQ in CRM 2011 Plugins | CRM Consultancy Blog - Spunje

    3. crm says:

      Have you tried the same concept with workflows and linq. I am trying that keep getting a casting exception when trying to get the linq query to return data.

      • Best bet might be post your code here or on the CRM Development Forum to let people take a look, and maybe also check whether the query is returning NULL as opposed to an object that can be cast.

    4. Stephanus says:

      I have the exact some problem when trying to use workflow and linq – keep getting a cast exception. I’ve posted my code and problem here: http://social.microsoft.com/Forums/en/crmdevelopment/thread/c0374fb7-f8a0-4096-be55-281bc6285c5b. Any ideas why?

      • The only idea off the top of my head would be mismatching versions (i.e. the SDK DLL versions) of those CRM is using and those your .NET Project is referencing – but if you have the same code working in a Plugin, that does not seem likely. If I get time will see if I can reproduce the problem on a VM, as looks to have stumped the forums as well.

    5. Evan says:

      Great guide, thanks!
      I’m getting type inference errors every time I try to query anything, however. For instance, I can’t do: var opportunities = myCrmService.OpportunitySet.ToList(); Instead it forces me to write: var opportunities = myCrmService.OpportunitySet.ToList();
      While it’s not really a breaking issue, it would be nice not to have to explicitly pass in the type each time when it should be able to figure it out.
      Any ideas?

      • Evan says:

        Ugh, brackets got HTML parsed… the 2nd code example should read: var opportunities = myCrmService.OpportunitySet.ToList<Opportunity>();

      • Evan says:

        Nevermind, figured it out. Turns out it was an issue with mismatched namespaces when I had to change the top one in the generated file and forgot to regenerate the whole file with the new namespace.

    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