Pages

Tuesday, September 7, 2010

N:N Relationship Utility Code (Javascript and C#)

It’s occurred several times recently that posters on the CRM Development forum have inquired about what code to use in order to check whether or not any two records are connected by a known N:N relationship.  Above and beyond this, a few even wonder how the AssociateEntitiesRequest and DisassociateEntitiesRequest messages work, and how best to implement it in their code, with these checks.  (If you don’t check, or know for certain that a relationship doesn’t already exist, calling these messages may produce an error.)

Many positive responses came forth from the community.  While the particulars of AssociateEntitiesRequest and DissassociateEntitiesRequest remain unchanged, there are two camps regarding the proper method to use for checking whether or not two records are related:

  1. Using a QueryExpression that makes heavy use of LinkEntities to map one entity, through the intersection entity (representing the N:N relationship), to the other entity.
  2. Using FetchXML to query the intersection entity directly.

These two methods exist because of a curious restriction by which RetrieveMultipleRequest is not allowed to query the intersection entity.

When I first approached the problem, I made the common mistake of trying to query the intersection entity directly by using a QueryByAttribute instance, which I then passed into my RetrieveMultipleRequest.  I was corrected by one astute Mahender Pal.  I found what I needed to correct the error of my ways on another blog.

The site illustrates Method 2, using FetchXML, which stood out immediately to me as superior to Method 1 which was practically the only example being sent around the forums.  I liked the solution so much, I customized it and made it dynamic, and built supporting code for the AssociateEntitiesRequest and DisassociateEntitiesRequest messages.  As an added bonus, I’ve implemented it in two languages: Javascript and C#.

Please note that the following code requires that you understand three things about the records you intend to check, associate, or disassociate:

  • You must know the schema name of the intersection entity.
  • You must know the schema names of the entities bound through the relationship.
  • You must know the value of their Key attributes, which is a Guid.

C#

Based on my work in a CRM Development thread, the following code extends the functionality by using a static class, called NNRelationship, to provide 3 principle methods: DoesRelationshipExist(), AssociateEntities(), and DisassociateEntities().  The later two include the first so that a single method call can check for the existence of a present relationship before performing their duties.  Optionally, the check can be circumnavigated with overloaded method declarations.

NNRelationshipHelpers.cs:

using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System;
using System.Web.Services;
using System.Xml;

namespace NNRelationshipHelpers {
  /// <summary>
  /// N:N Relationship helper methods
  /// </summary>
  public static class NNRelationship
  {
    /// <summary>
    /// Checks a CRM intersection entity for a link between records belonging to two entities: entity1 and entity2
    /// </summary>
    /// <param name="crmService">A reference to an instance of ICrmService</param>
    /// <param name="relationshipSchemaName">The schema name of the intersection entity between entity1 and entity2; Commonly also the name of the relationship</param>
    /// <param name="entity1SchemaName">The schema name of entity1</param>
    /// <param name="entity1KeyValue">The GUID value of the Key attribute for entity1; Identifies a specific record</param>
    /// <param name="entity2SchemaName">The schema name of entity2</param>
    /// <param name="entity2KeyValue">The GUID value of the Key attribute for entity2; Identifies a specific record</param>
    /// <returns>true, if entity1 and entity2 are linked; false, if not</returns>
    public static bool DoesRelationshipExist(ICrmService crmService, string relationshipSchemaName, string entity1SchemaName, Guid entity1KeyValue, string entity2SchemaName, Guid entity2KeyValue)
    {
      // Assemble FetchXML to query the intersection entity directly
      string fetchXml = "<fetch mapping='logical'> <entity name='" + relationshipSchemaName + "'>"
      + "<all-attributes />"
      + "<filter>"
      + "<condition attribute='" + entity1SchemaName + "id' operator='eq' value ='" + entity1KeyValue.ToString() + "' />"
      + "<condition attribute='" + entity2SchemaName + "id' operator='eq' value='" + entity2KeyValue.ToString() + "' />"
      + "</filter>"
      + "</entity>"
      + "</fetch>";

      // Perform the query
      string strResult = crmService.Fetch(fetchXml);

      // Prepare the results
      XmlDocument xmlDoc = new XmlDocument(); 
      xmlDoc.LoadXml(strResult);

      // Scan for results
      XmlNodeList nodeList = xmlDoc.SelectNodes("resultset/result");

      if (nodeList.Count == 0)
        return false;
      else
        return true;
    }

    /// <summary>
    /// Optionally performs a check on the records of entity1 and entity2, and if not already associated, performs an AssociateEntitiesRequest upon them.
    /// </summary>
    /// <param name="crmService">A reference to an instance of ICrmService</param>
    /// <param name="relationshipSchemaName">The schema name of the intersection entity between entity1 and entity2; Commonly also the name of the relationship</param>
    /// <param name="entity1SchemaName">The schema name of entity1</param>
    /// <param name="entity1KeyValue">The GUID value of the Key attribute for entity1; Identifies a specific record</param>
    /// <param name="entity2SchemaName">The schema name of entity2</param>
    /// <param name="entity2KeyValue">The GUID value of the Key attribute for entity2; Identifies a specific record</param>
    /// <param name="skipCheck">Set to 'true' to avoid performing a check before calling the AssociateEntitiesRequest</param>
    public static void AssociateEntities(ICrmService crmService, string relationshipSchemaName, string entity1SchemaName, Guid entity1KeyValue, string entity2SchemaName, Guid entity2KeyValue, bool skipCheck)
    {
      if (skipCheck || !DoesRelationshipExist(crmService, relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue))
      {
        Moniker moniker1 = new Moniker();
        moniker1.Name = entity1SchemaName;
        moniker1.Id = entity1KeyValue;

        Moniker moniker2 = new Moniker();
        moniker2.Name = entity2SchemaName;
        moniker2.Id = entity2KeyValue;

        AssociateEntitiesRequest request = new AssociateEntitiesRequest();
        request.Moniker1 = moniker1;
        request.Moniker2 = moniker2;
        request.RelationshipName = relationshipSchemaName;

        crmService.Execute(request);
      }
    }

    /// <summary>
    /// First performs a check on the records of entity1 and entity2, and if not already associated, performs an AssociateEntitiesRequest upon them.
    /// </summary>
    /// <param name="crmService">A reference to an instance of ICrmService</param>
    /// <param name="relationshipSchemaName">The schema name of the intersection entity between entity1 and entity2; Commonly also the name of the relationship</param>
    /// <param name="entity1SchemaName">The schema name of entity1</param>
    /// <param name="entity1KeyValue">The GUID value of the Key attribute for entity1; Identifies a specific record</param>
    /// <param name="entity2SchemaName">The schema name of entity2</param>
    /// <param name="entity2KeyValue">The GUID value of the Key attribute for entity2; Identifies a specific record</param>
    public static void AssociateEntities(ICrmService crmService, string relationshipSchemaName, string entity1SchemaName, Guid entity1KeyValue, string entity2SchemaName, Guid entity2KeyValue)
    {
      AssociateEntities(crmService, relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue, false);
    }

    /// <summary>
    /// Optionally performs a check on the records of entity1 and entity2, and if not already associated, performs a DisassociateEntitiesRequest upon them.
    /// </summary>
    /// <param name="crmService">A reference to an instance of ICrmService</param>
    /// <param name="relationshipSchemaName">The schema name of the intersection entity between entity1 and entity2; Commonly also the name of the relationship</param>
    /// <param name="entity1SchemaName">The schema name of entity1</param>
    /// <param name="entity1KeyValue">The GUID value of the Key attribute for entity1; Identifies a specific record</param>
    /// <param name="entity2SchemaName">The schema name of entity2</param>
    /// <param name="entity2KeyValue">The GUID value of the Key attribute for entity2; Identifies a specific record</param>
    /// <param name="skipCheck">Set to 'true' to avoid performing a check before calling the DisassociateEntitiesRequest</param>
    public static void DisassociateEntities(ICrmService crmService, string relationshipSchemaName, string entity1SchemaName, Guid entity1KeyValue, string entity2SchemaName, Guid entity2KeyValue, bool skipCheck)
    {
      if (skipCheck || DoesRelationshipExist(crmService, relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue))
      {
        Moniker moniker1 = new Moniker();
        moniker1.Name = entity1SchemaName;
        moniker1.Id = entity1KeyValue;

        Moniker moniker2 = new Moniker();
        moniker2.Name = entity2SchemaName;
        moniker2.Id = entity2KeyValue;

        DisassociateEntitiesRequest request = new DisassociateEntitiesRequest();
        request.Moniker1 = moniker1;
        request.Moniker2 = moniker2;
        request.RelationshipName = relationshipSchemaName;

        crmService.Execute(request);
      }
    }

    /// <summary>
    /// First performs a check on the records of entity1 and entity2, and if not already associated, performs a DisassociateEntitiesRequest upon them.
    /// </summary>
    /// <param name="crmService">A reference to an instance of ICrmService</param>
    /// <param name="relationshipSchemaName">The schema name of the intersection entity between entity1 and entity2; Commonly also the name of the relationship</param>
    /// <param name="entity1SchemaName">The schema name of entity1</param>
    /// <param name="entity1KeyValue">The GUID value of the Key attribute for entity1; Identifies a specific record</param>
    /// <param name="entity2SchemaName">The schema name of entity2</param>
    /// <param name="entity2KeyValue">The GUID value of the Key attribute for entity2; Identifies a specific record</param>
    public static void DisassociateEntities(ICrmService crmService, string relationshipSchemaName, string entity1SchemaName, Guid entity1KeyValue, string entity2SchemaName, Guid entity2KeyValue)
    {
      DisassociateEntities(crmService, relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue, false);
    }
  }
}

Javascript

Using Adi Katz’s blog as a guide, I established a new function in my CRM Javascript Library called Fetch(), which uses the indispensible, and somewhat comically named MischiefMayhemSOAP() function, and wrote the following DoesNNRelationshipExist(), AssociateEntities(), and DisassociateEntities() functions (thanks go to Thomas Van De Vosse, for his code example on calling AssociateEntitiesRequest from Javascript).  There aren’t any comments in the Javascript source, as the implementation is meant to mirror that of the C# above:

[UPDATE 9/9/10: Found a minor coding bug around the “skipCheck” switches in the if() statements of AssociateEntities and Disassociate entities.  Correction issued and available below.]

[UPDATE 12/8/2010: The Javascript code has been updated to fix a different bug with “skipCheck” and includes the FetchEncode() method necessary to make the script work.]

function MischiefMayhemSOAP(serviceUrl, xmlSoapBody, soapActionHeader, suppressError) {
  var xmlReq = "<?xml version='1.0' encoding='utf-8'?>"
    + "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"
    + "  xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
    + "  xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"
    + GenerateAuthenticationHeader()
    + "  <soap:Body>"
    + xmlSoapBody
    + "  </soap:Body>"
    + "</soap:Envelope>";

  var httpObj = new ActiveXObject("Msxml2.XMLHTTP");

  httpObj.open("POST", serviceUrl, false);

  httpObj.setRequestHeader("SOAPAction", soapActionHeader);
  httpObj.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
  httpObj.setRequestHeader("Content-Length", xmlReq.length);

  httpObj.send(xmlReq);

  var resultXml = httpObj.responseXML;

  var errorCount = resultXml.selectNodes("//error").length;
  if (errorCount != 0) {
    var msg = resultXml.selectSingleNode("//description").nodeTypedValue;
    
    if (typeof(suppressError) == "undefined" || suppressError == null) {
      alert("The following error was encountered: " + msg);
    }
    
    return null;
  } else {
    return resultXml;
  }
}

// FetchEncode is borrowed from the _HtmlEncode function from Microsoft's CRM scripts
function FetchEncode(fetchXml) {
  var c;
  var HtmlEncode = '';
  var buffer = '';
  var bufferLength = 500;
  var count = 0;

  if(fetchXml == null) {
    return null;
  }
  
  if (fetchXml == '') {
    return '';
  }

  for(var cnt = 0; cnt < fetchXml.length; cnt++) {
    c = fetchXml.charCodeAt(cnt);

    if (( ( c > 96 ) && ( c < 123 ) ) ||
      ( ( c > 64 ) && ( c < 91 ) ) ||
      ( c == 32 ) ||
      ( ( c > 47 ) && ( c < 58 ) ) ||
      ( c == 46 ) ||
      ( c == 44 ) ||
      ( c == 45 ) ||
      ( c == 95 )) {
      buffer += String.fromCharCode(c);
    } else {
      buffer += '&#' + c + ';';
    }

    if (++count == bufferLength) {
      HtmlEncode += buffer;
      buffer = '';
      count = 0;
    }
  }

  if (buffer.length) {
    HtmlEncode += buffer;
  }

  return HtmlEncode;
}

function Fetch(fetchXml) {
  var xmlSoapBody = "<Fetch xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"
    + "  <fetchXml>"
    + FetchEncode(fetchXml)
    + "  </fetchXml>"
    + "</Fetch>";
  
  var fetchResponse = MischiefMayhemSOAP("/MSCRMServices/2007/CrmService.asmx", xmlSoapBody, "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");

  if (fetchResponse != null) {
    var fetchResults = new ActiveXObject("Msxml2.DOMDocument");
    
    fetchResults.async = false;
    fetchResults.resolveExternals = false;
    fetchResults.loadXML(fetchResponse.text);
    
    return fetchResults;
  } else {
    return null;
  }
}

function DoesNNRelationshipExist(relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue) {
  var fetchXml = "<fetch mapping='logical'>"
    + "  <entity name='" + relationshipSchemaName + "'>"
    + "    <all-attributes />"
    + "    <filter>"
    + "      <condition attribute='" + entity1SchemaName + "id' operator='eq' value ='" + entity1KeyValue + "' />"
    + "      <condition attribute='" + entity2SchemaName + "id' operator='eq' value='" + entity2KeyValue + "' />"
    + "    </filter>"
    + "  </entity>"
    + "</fetch>";
 
  var fetchResults = Fetch(fetchXml);
  
  var nodeList = fetchResults.selectNodes("resultset/result");
  
  if (nodeList == null || nodeList.length == 0) {
    return false;
  } else {
    return true;
  }
}

function AssociateEntities(relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue, skipCheck) {
  if ((typeof skipCheck != "undefined" && skipCheck) || !DoesNNRelationshipExist(relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue)) {
    var xmlSoapBody = "<Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"
      + "  <Request xsi:type='AssociateEntitiesRequest'>"
      + "    <Moniker1>"
      + "      <Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity1SchemaName + "</Name>"
      + "      <Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity1KeyValue + "</Id>"
      + "    </Moniker1>"
      + "    <Moniker2>"
      + "      <Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity2SchemaName + "</Name>"
      + "      <Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity2KeyValue + "</Id>"
      + "    </Moniker2>"
      + "    <RelationshipName>" + relationshipSchemaName + "</RelationshipName>"
      + "  </Request>"
      + "</Execute>";
    
    MischiefMayhemSOAP("/MSCRMServices/2007/CrmService.asmx", xmlSoapBody, "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
  }
}

function DisassociateEntities(relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue, skipCheck) {
  if ((typeof skipCheck != "undefined" && skipCheck) || DoesNNRelationshipExist(relationshipSchemaName, entity1SchemaName, entity1KeyValue, entity2SchemaName, entity2KeyValue)) {
    var xmlSoapBody = "<Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"
      + "  <Request xsi:type='DisassociateEntitiesRequest'>"
      + "    <Moniker1>"
      + "      <Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity1SchemaName + "</Name>"
      + "      <Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity1KeyValue + "</Id>"
      + "    </Moniker1>"
      + "    <Moniker2>"
      + "      <Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity2SchemaName + "</Name>"
      + "      <Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + entity2KeyValue + "</Id>"
      + "    </Moniker2>"
      + "    <RelationshipName>" + relationshipSchemaName + "</RelationshipName>"
      + "  </Request>"
      + "</Execute>";
    
    MischiefMayhemSOAP("/MSCRMServices/2007/CrmService.asmx", xmlSoapBody, "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
  }
}

4 comments:

  1. Hey Dave,
    Its funny how when one person has an issue or question that there are usually more people out there having the same issue or question at the same time.

    Nice Post. Storing it in my Felix bag for when needed.

    Thanks,
    Robert

    ReplyDelete
  2. So, pretty major bug with the Javascript counterpart: I was evaluating the "skipCheck" parameter against a value of "false". I have no idea why I did that. Changed it. Also discovered that the absence of the FetchEncode function from the script would prevent its use; don't know why I forgot to include it, but anyone who grabbed it from Adi's blog would be fine. Both of those updates should make the Javascript component infinitely more usable.

    ReplyDelete
  3. I am trying to use the javascript functions. I get error for strInput undefined. Where do I declare it and what should be its value?

    ReplyDelete
    Replies
    1. Tejal,

      I'm not sure how it happened; but apparently the rename for 'strInput' to 'fetchXml' didn't translate to the interior code for that function--but it did in my personal library. I must have transferred the code without the full change, and just not known it all this time. Rename 'strInput' to 'fetchXml', and you should be fine. Thanks for the catch!

      -Dave

      Delete

Unrelated comments to posts may be summarily disposed at the author's discretion.