Monthly Archives: July 2013

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

Advertisements

Filtering Views by intercepting Retrieve Multiple Queries with a Plugin

The best way of filtering System Views by diferent parameters or by roles is to capture the Retrieve Multiple queries and adding filters to the related queryexpresion.

It sound like something very difficult but this can be archieved easyly by creating a plugin and registering it over the Retrieve Multiple Call of the related entity.

This plugin must be registered with Pre-Operation, Synchronous and Server settings as shown on the image.

Plugin Registration for Retrieve Multiple

Plugin Registration for Retrieve Multiple

The development of the plugin is quite easy.

First of all the plugin has to inherit from the following base class.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using Microsoft.Xrm.Sdk;
using System.Globalization;

namespace L.Plugin
{
    public abstract class LPluginBase : IPlugin
    {       
        static protected IPluginExecutionContext GetExecutionContext(System.IServiceProvider serviceProvider)
        {
            IPluginExecutionContext pluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            return pluginExecutionContext;
        }

        public abstract void Execute(System.IServiceProvider serviceProvider);
    }

    public static class MessageNames
    {
        public static String Create
        {
            get
            {
                return "Create";
            }
        }

        public static String Delete
        {
            get { return "Delete"; }
        }
    }

    public static class ExecutionStage
    {
        public static int PreEvent
        {
            get { return 20; }
        }

        public static int PostEvent
        {
            get { return 40; }
        }
    }
}

Then the plugin that intercepts the call and adds a new filter base on a field of the calling entity will look like:


namespace L.DW.RetrieveCaching
{
    using System;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using L.Plugin;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Messages;

    /// <summary>
    /// PreValidateHazardousEventRevisionRetrieveMultiple Plugin.
    /// </summary>    
    public class PreValidateHazardousEventRevisionRetrieveMultiple : LPluginBase
    {
        public override void Execute(IServiceProvider serviceProvider)
        {

               if (serviceProvider == null)
                {
                    throw new ArgumentNullException("serviceProvider");
                }

                
                Microsoft.Xrm.Sdk.IPluginExecutionContext context =
                      (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

                // Get the OrgService
                IOrganizationServiceFactory serviceFactory =
                    (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Get a reference to the tracing service.
                ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

                // Check that all of the following conditions are true:
                //  1. plug-in is running synchronously
                //  3. plug-in is running on the 'RetrieveMultiple' event
                if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
                {
                  
                    // The InputParameters collection contains all the data passed in the message request.
                    if (context.InputParameters.Contains("Query"))
                    {
                        if (context.InputParameters["Query"] is QueryExpression)
                        {
                            if (UserHasRole(context.InitiatingUserId, "Drinking Water Team User", service))
                            {
                                // Get the QueryExpression from the property bag
                                QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];

                                // We can modify the original query to just show record with Completed or Completed with issues estaus.

                                ConditionExpression statusCompletedCond = new ConditionExpression()
                               {
                                   AttributeName = "L_status",
                                   Operator = ConditionOperator.In,
                                   Values = { 3, 4 }
                               };

                                objQueryExpression.Criteria.AddCondition(statusCompletedCond);
                            }

                        }

                    }
                }

            
        }

We could make something even more complex by filtering the results from the status of a field in a N to N relationship.


namespace L.DW.RetrieveCaching
{
    using System;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using Lema.Plugin;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Messages;

    /// <summary>
    /// PreValidateControlMeasureRetrieveMultiple Plugin.
    /// </summary>    
    public class PreValidateControlMeasureRetrieveMultiple : LPluginBase
    {
        public override void Execute(IServiceProvider serviceProvider)
        {
            if (serviceProvider == null)
            {
                throw new ArgumentNullException("serviceProvider");
            }
                           if (serviceProvider == null)
                {
                    throw new ArgumentNullException("serviceProvider");
                }

                
                Microsoft.Xrm.Sdk.IPluginExecutionContext context =
                      (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

                // Get the OrgService
                IOrganizationServiceFactory serviceFactory =
                    (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Get a reference to the tracing service.
                ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

                // Check that all of the following conditions are true:
                //  1. plug-in is running synchronously
                //  3. plug-in is running on the 'RetrieveMultiple' event
                if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
                {                                                    
                    // The InputParameters collection contains all the data passed in the message request.
                    if (context.InputParameters.Contains("Query"))
                    {
                        if (context.InputParameters["Query"] is QueryExpression)
                        {
                            if (UserHasRole(context.InitiatingUserId, "Water Team User", service))
                            {

                                // Get the QueryExpression from the property bag
                                QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];
                                objQueryExpression.Distinct = true;

                                // Calculate completed Revisiones
                                string[] CompletedGuids = getAllOpenRevisionsGuid(service);

                                // Add the filter using the N to N middle table 
                                LinkEntity linkEntity1 = new LinkEntity();
                                linkEntity1.JoinOperator = JoinOperator.Natural;
                                linkEntity1.LinkFromEntityName = "L_controlmeasure";
                                linkEntity1.LinkFromAttributeName = "L_controlmeasureid";
                                linkEntity1.LinkToEntityName = "L_controlmeasure_hazardouseventrevision";
                                linkEntity1.LinkToAttributeName = "L_controlmeasureid";

                                // Condition : where hazarrevisionID is in the returned Guidvalues
                                ConditionExpression statusCompletedCond = new ConditionExpression("L_hazardouseventrevisionid", ConditionOperator.In, CompletedGuids);

                                linkEntity1.LinkCriteria.AddCondition(statusCompletedCond);

                                objQueryExpression.LinkEntities.Add(linkEntity1);     
                                   
                            }

                        }

                    }
                }


        }

NOTE: Probably this second option of filtering by the N to N relationship could be done without making 2 queries but I couldn’t make it work, so as my teacher used to say, divide and you will win 😛 !!! Using 2 queries helped me to filter the results based on a N to N relationship.

I hope it was helpful!

Cheers,

Hache

Scheduling workflows on CRM

I found this amazing post about scheduling jobs on CRM that could make our lives a lot simpler!

It is about scheduling jobs on CRM using the Bulk Delete Schedule system already present on CRM.

Click Dimensions Blog

Thanks a lot to the click dimensions crowd for it!

And here:

http://blogs.msdn.com/b/mvpawardprogram/archive/2013/03/11/scheduling-recurring-workflows-in-microsoft-dynamics-crm-2011-online-and-on-premise.aspx

Integrating CRM2011 with ANY external system

Working for one of our clients I was asked to integrate CRM2011 with the Altitude VoIP Call Center Aplication. At the beggining i thought it would be an imposible task mainly because CRM2011 executes on a web server different from the client machine with IEplorer where the VoIP Phone Device was connected.

Talking to the altitute development team, they provided me with a test windows form application that installed on the client machine would do the work of connecting to the Call Center.

Once i got the windows form aplication working i had the idea of connecting both systems (CRM2011 and the Windows Form App) using a SIlverlight application on the CRM2011 EndPoint that will work just as a Control Panel of the Windows form application. This silverlight APP will send simple commands as text messages to the Windows Form and viceversa. All the hard work will be done by the Windows Form app and the Silverlight will have just come buttons and will just send and receive simple commands looking like this.

Silverlight Control Panel

Silverlight Control Panels

To be able to communicate a Silverlight app with any windows form application we can use the famous Eneter Libraries the way described in this great post of Ondrej Uzovic.

This way the silverlight control panel will send messages like:

  • “Call 648332222”
  • “Hold phoneCall” or “Restore it”.
  • “Mute phonecall”
  • “Trasfer call”

And the Windows Form application after reciving the messages, will just call the same functions invoked by its buttons (call, hold…). The windows form app looks like this.

Windows Form APP conected to Silverlight

Windows Form APP conected to Silverlight

Following this architechture we will be able to conect CRM with any external system as far as this system is able to connect to a Windows Form Aplication.

Regards,