Category Archives: C#

C# Simple query Join between to entities 1..N

Havin to entities query the first one and filter by values on the first one and second one.


QueryExpression query = new QueryExpression
{
	EntityName = "h_licence",
	ColumnSet = new ColumnSet("h_regno", "h_rootregnumber", "h_revisionnumber", "h_libraryname"),
};

query.Criteria.AddCondition("h_licencestatus", ConditionOperator.Equal, 919780001);
query.Criteria.AddCondition("h_licencetype", ConditionOperator.In, 919780002, 953210002, 953210001, 919780001);
query.Criteria.AddCondition("h_substatus", ConditionOperator.NotIn, 4); //Exclude NFA
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, "Active");

query.LinkEntities.Add(new LinkEntity("h_licence","h_licenceprofile","h_licenceprofileid","h_licenceprofileid",JoinOperator.Inner));
query.LinkEntities[0].LinkCriteria.AddCondition("h_enforcementriskcategory", ConditionOperator.Equal, os.Value);

return se.RetrieveMultiple(query);

Advertisements

Creating Associate Plugin

The Plugin needs to be registered on Post Operation of the Associate Plugin.

Associate Plugin

Associate Plugin

From the Context variable we can obtain the target entity and the associated entities.


namespace Lema.SiteVisits.Plugins
{
    using System;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.Linq;
    using System.Text;
    using Microsoft.Crm.Sdk;
    using Microsoft.Xrm.Sdk;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Xrm.Sdk.Query;
    using Microsoft.Xrm.Sdk.Client;

    public class PostSiteCategoryAssociate: IPlugin
    {       
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));          

            if (!context.InputParameters.Contains("Relationship")) { return; }
            Relationship relationship = (Relationship)context.InputParameters["Relationship"];
            if (relationship.SchemaName != "mike_mike_sitevisit_mike_sitevisittype") { return; }
            
            if (!context.InputParameters.Contains("Target")) { return; }
            EntityReference target = (EntityReference)context.InputParameters["Target"];
            if (!context.InputParameters.Contains("RelatedEntities")) { return; }
            EntityReferenceCollection related = (EntityReferenceCollection)context.InputParameters["RelatedEntities"];

            //Relationship is a reference to the relationship which this association is working against. i.e. mike_mike_sitevisit_mike_sitevisittype
            //Target is a reference to the primary Entity – i.e. Site Visit
            //RelatedEntities is a set of references pointing to the records to associate with – i.e. site Visits Type

            Guid SiteId = (Guid)target.Id;
            Entity sitev = service.Retrieve("mike_sitevisit", SiteId, new ColumnSet(true));

            string typenames = string.Empty;
            foreach (EntityReference sitetype in related)
            {
                typenames += sitetype.Name + ";";        
            }
            sitev.Attributes["mike_summary"] = typenames;
            service.Update(sitev);             
        }

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

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,

Query N to N relationship

Having and N to N relationship between points and parameters, from a point ID we want to retrieve all the related parameters.


 EntityCollection getEPParameters(IOrganizationService serv, Guid POINTid)
        {
            QueryExpression query = new QueryExpression()
            {   //Parameters entity
                EntityName = "mile_emissionpointparameters",
                ColumnSet = new ColumnSet(true),
                LinkEntities = 
                        {
                            new LinkEntity
                            {
                                LinkFromEntityName = "mile_emissionpointparameters", //Parameters table
                                LinkFromAttributeName = "mile_emissionpointparametersid", //Parameters table id
                                LinkToEntityName = "mike_mile_emissionpoint_mile_emissionpointpara", // N to N entity 
                                LinkToAttributeName = "mile_emissionpointparametersid", 
                                LinkCriteria = new FilterExpression
                                {
                                    FilterOperator = LogicalOperator.And,
                                    Conditions = 
                                    {
                                        new ConditionExpression
                                        {
                                            AttributeName = "mile_emissionpointid",
                                            Operator = ConditionOperator.Equal,
                                            Values = { POINTid }  // Point id
                                        }
                                    }
                                }
                            }
                        }
            };

            // Obtain results from the query expression.
            return serv.RetrieveMultiple(query);

        }

If what we need is to get a list of entities filtered by some values of the other entities in the N to N relationship we just need to use nested LinkEntities. Imagine we have an N 2 N between control measures and eventRevisions. We want to get all the measures related to any open – semiopen (L_status 3 or 4) revisions.


QueryExpression objQueryExpression = new QueryExpression("lema_controlmeasure");
objQueryExpression.ColumnSet = new ColumnSet(true);
objQueryExpression.Distinct = true;

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

// To be able to do a double Link we use another LinkEntity within the the original linked Entity
LinkEntity InnerLinkEntity = new LinkEntity();
InnerLinkEntity.JoinOperator = JoinOperator.Inner;
InnerLinkEntity.LinkFromEntityName = "L_controlmeasure_hazardouseventrevision";
InnerLinkEntity.LinkFromAttributeName = "L_hazardouseventrevisionid";
InnerLinkEntity.LinkToEntityName = "L_hazardouseventrevision";
InnerLinkEntity.LinkToAttributeName = "L_hazardouseventrevisionid";

// Filter of the inner Join by a value of the second entity of the N 2 N
InnerLinkEntity.LinkCriteria.AddCondition("L_status", ConditionOperator.In, 3, 4);

//Add this link to the original link
linkEntity1.LinkEntities.Add(InnerLinkEntity);

objQueryExpression.LinkEntities.Add(linkEntity1);   

EntityCollection results = _service.RetrieveMultiple(objQueryExpression);

Following this idea of having a InnerLinkEntity, we could add more innerLinkEntities being able to query N relationships at once!

I hope it was helpfull!!!

Cheers,

CRM2011 and C# : Avoiding the 5000 records limitation. Paging Retrieve Multiple Results.

As most of you will know when querying the CRM using RetrieveMultiple command there is a limitation of 5000 records that, when creating processes that afect a large number of rows, can´t be a real pain.

There are some ways of changing this limitation by touching the server registry or changing some SQL server values in the MSCRM_CONFIG Database as described in the following post, but here is a great solution to do it programatically without changing any configuration value.

Programatically using RetrieveMultipleRequest and RetrieveMultipleReponse.

 public EntityCollection GetAllLeads(OrganizationService service)
        {
            EntityCollection exit = new EntityCollection();

            try
            {
                QueryExpression queryServicios = new QueryExpression
                {
                    EntityName = "lead",
                    ColumnSet = new ColumnSet("leadid", "statecode", "statuscode"),
                    Criteria = new FilterExpression()
                };

                queryServicios.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);

                int pageNumber = 1;
                RetrieveMultipleRequest multiRequest;
                RetrieveMultipleResponse multiResponse = new RetrieveMultipleResponse();

                do
                {
                    queryServicios.PageInfo.Count = 5000;
                    queryServicios.PageInfo.PagingCookie = (pageNumber == 1) ? null : multiResponse.EntityCollection.PagingCookie;
                    queryServicios.PageInfo.PageNumber = pageNumber++;

                    multiRequest = new RetrieveMultipleRequest();
                    multiRequest.Query = queryServicios;
                    multiResponse = (RetrieveMultipleResponse)service.Execute(multiRequest);

                    exit.Entities.AddRange(multiResponse.EntityCollection.Entities);
                 }
                while (multiResponse.EntityCollection.MoreRecords);

             }
            catch (Exception ex)
            {
                log = new Logger(";;" + ex.Message);

            }

            return exit;
        }

To do paging using Fetch XML.

Hope it helps. Have a nice day.

Hache