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

Advertisements

5 thoughts on “Filtering Views by intercepting Retrieve Multiple Queries with a Plugin

    1. hachecrm2011 Post author

      Hi fumihisa:

      The plugin executes every time a Retrieve Multiple is called for that entity. But then within the code you can see the I just apply my modification for the DRINKING WATTER USER ROLE. Using this same function you could apply the filtering only for the particular situation when you want it to apply.

      Reply
  1. valamar

    Hi Hach,
    Do you try it on outlook plugin? For me it works on web version of application well but on outlook it does nothing.

    Reply

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