Linq To CRM Query Alternatives

All replies

• 

  

  0

  Sign in to vote

  Hello jcaruso,

  Use JavaScript SOAP to retrieve your records. Here's an example article:

  Also, here is some JavaScript code to help you manipulate CRM data: http://www.bizforward.cws-international.com/2011/01/26/creating-records-in-crm-2011-using-javascript/

  // Logical Operator
  var LogicalOperator = { And:"And", Or:"Or", Like:"Like", Equal:"Equal", NotEqual:"NotEqual", GreaterThan:"GreaterThan", LessThan:"LessThan", Inner:"Inner", LeftOuter:"LeftOuter", Natural:"Natural", Eq:"eq", Null:"Null", NotNull:"NotNull" };
  
  // CRM Service
  function CrmService(entityName, logicalOperator) 
  {
    if (logicalOperator == null)
      throw new Error("Must specify non-null value for logicalOperator");
  
    if (entityName == null)
      throw new Error("Must specify non-null value for entityName");
  	
    this.entityName = entityName;
    this.ColumnSet = new Array();
    this.LogicalOperator = logicalOperator;
    this.Conditions = new Array();
    this.LinkedEntities = new Array();
  }
  
  // getEntityName
  CrmService.prototype.getEntityName = function() 
  {
    return this.entityName;
  }
  
  // Condition
  function Condition(field, operator, value) 
  {
    this.Field = field;
    this.Value = CrmEncodeDecode.CrmXmlEncode(value);
  	
    if (operator == null)
      throw new Error("Must specify non-null value for operator");
  	
    this.Operator = operator;
  }
  
  // setEntityName
  CrmService.prototype.setEntityName = function() 
  {
    return this.entityName;
  }
  
  // AddColumn
  CrmService.prototype.AddColumn = function(columnName) 
  {
    this.ColumnSet[this.ColumnSet.length] = columnName;
  }
  
  // AddFilterCondition
  CrmService.prototype.AddFilterCondition = function(field, conditionOperator, value) 
  {
    this.Conditions[this.Conditions.length] = new Condition(field, conditionOperator, value);
  }
  
  // Linked Entity
  function LinkedEntity(linkFromEntityName, linkToEntityName, linkFromAttributeName, linkToAttributeName, joinOperator) 
  {
    this.LinkFromEntityName = linkFromEntityName;
    this.LinkToEntityName = linkToEntityName;
    this.LinkFromAttributeName = linkFromAttributeName;
    this.LinkToAttributeName = linkToAttributeName;
    if (joinOperator == null)
      throw new Error("Must specify non-null value for operator");
    this.JoinOperator = joinOperator;
    this.Conditions = new Array();
    this.FilterOperator = LOGICAL_OPERATOR_AND;
  }
  
  // AddFilterCondition
  LinkedEntity.prototype.AddFilterCondition = function(field, conditionOperator, value) 
  {
    this.Conditions[this.Conditions.length] = new Condition(field, conditionOperator, value);
    return this.Conditions[this.Conditions.length - 1];
  }
  
  // AddLinkedEntityCondition
  CrmService.prototype.AddLinkedEntityCondition = function(linkFromEntityName, linkToEntityName, linkFromAttributeName, linkToAttributeName, joinOperator) 
  {
    this.LinkedEntities[this.LinkedEntities.length] = new LinkedEntity(linkFromEntityName, linkToEntityName, linkFromAttributeName, linkToAttributeName, joinOperator);
    return this.LinkedEntities[this.LinkedEntities.length - 1];
  }
  
  // Retrieve Multiple
  function RetrieveMultipleResult(crmService) 
  {
    this.Rows = new Array();
    this.CrmService = crmService;
  }
  
  // AddRow
  RetrieveMultipleResult.prototype.AddRow = function() 
  {
    this.Rows[this.Rows.length] = new Row();
    return this.Rows[this.Rows.length - 1];
  }
   
  // Row
  function Row() 
  {
    this.Columns = new Array();
  }
  
  // Column
  function Column(columnName, value, dataType) 
  {
    this.ColumnName = columnName;
    this.Value = value;
    this.DataType = dataType;
  }
  
  // AddColumn
  Row.prototype.AddColumn = function(columnName, value) 
  {
    this.Columns[this.Columns.length] = new Column(columnName, value);
  }
  
  // GetColumn
  Row.prototype.GetColumn = function(columnName) 
  {
    for (columnNumber in this.Columns) {
      var column = this.Columns[columnNumber];
      if (columnName.toLowerCase() == column.ColumnName.toLowerCase())
        return column;
    }
    throw new Error("Column " + columnName + " does not exist");
  }
  
  // GetValue
  Row.prototype.GetValue = function(columnName) 
  {
    var column = this.GetColumn(columnName);
    return column.Value;
  }
  
  // SOAP
  CrmService.prototype.RetrieveMultiple = function()
  {
  	try {
  	  var xmlSoapHeader = "<?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\">";
  
  	  var xmlAuthHeader = GenerateAuthenticationHeader();
  
  	  var xmlSoapBody = "<soap:Body><RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\"><query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\"><q1:EntityName>" + this.getEntityName() + "</q1:EntityName><q1:ColumnSet xsi:type=\"q1:ColumnSet\"><q1:Attributes> ";
  
  	  for (var i=0; i<this.ColumnSet.length; i++) {
  		var column = this.ColumnSet[i];
  		xmlSoapBody = xmlSoapBody + "<q1:Attribute>" + column + "</q1:Attribute>";
  	  }
  
  	  xmlSoapBody += "</q1:Attributes></q1:ColumnSet><q1:Distinct>false</q1:Distinct><q1:PageInfo><q1:PageNumber>0</q1:PageNumber><q1:Count>0</q1:Count></q1:PageInfo><q1:LinkEntities>";
  
  	  if (this.LinkedEntities.length > 0) {
  		for (var i=0; i<this.LinkedEntities.length; i++) {
  		  var linkedEntity = this.LinkedEntities[linkedEntityNumber];
  		  xmlSoapBody += "<q1:LinkEntity> ";
  		  xmlSoapBody += "<q1:LinkFromAttributeName>" + linkedEntity.LinkFromAttributeName + "</q1:LinkFromAttributeName> ";
  		  xmlSoapBody += "<q1:LinkFromEntityName>" + linkedEntity.LinkFromEntityName + "</q1:LinkFromEntityName> ";
  		  xmlSoapBody += "<q1:LinkToEntityName>" + linkedEntity.LinkToEntityName + "</q1:LinkToEntityName> ";
  		  xmlSoapBody += "<q1:LinkToAttributeName>" + linkedEntity.LinkToAttributeName + "</q1:LinkToAttributeName> ";
  		  xmlSoapBody += "<q1:JoinOperator>" + linkedEntity.JoinOperator + "</q1:JoinOperator> ";
  		  xmlSoapBody += "<q1:LinkCriteria> ";
  
  		  if (linkedEntity.FilterOperator == null)
  			throw new Error("Must specify non-null value for FilterOperator");
  
  		  xmlSoapBody += "<q1:FilterOperator>" + linkedEntity.FilterOperator + "</q1:FilterOperator> ";
  		  xmlSoapBody += "<q1:Conditions> ";
  		  for (var i=0; i<linkedEntity.Conditions.length; i++) {
  			var conditionLinked = linkedEntity.Conditions[i];
  			xmlSoapBody += "<q1:Condition> ";
  			xmlSoapBody += "<q1:AttributeName>" + conditionLinked.Field + "</q1:AttributeName> ";
  			xmlSoapBody += "<q1:Operator>" + conditionLinked.Operator + "</q1:Operator> ";
  			  
  			if(conditionLinked.Operator != LogicalOperator.Null && conditionLinked.Operator != LogicalOperator.NotNull)
  			{
  				xmlSoapBody += "<q1:Values> ";
  				xmlSoapBody += "<q1:Value xsi:type=\"xsd:string\">" + conditionLinked.Value + "</q1:Value> ";
  				xmlSoapBody += "</q1:Values> ";
  			}
  			
  			xmlSoapBody += "</q1:Condition> ";
  		  }
  		  xmlSoapBody += " </q1:Conditions> ";
  		  xmlSoapBody += " <q1:Filters /> ";
  		  xmlSoapBody += "</q1:LinkCriteria> ";
  		  xmlSoapBody += "<q1:LinkEntities />";
  		  xmlSoapBody += "</q1:LinkEntity>";
  		}
  	  }
  
  	  if (this.LogicalOperator == null)
  		throw new Error("Must specify non-null value for LogicalOperator");
  
  	  xmlSoapBody += "</q1:LinkEntities><q1:Criteria><q1:FilterOperator>" + this.LogicalOperator + "</q1:FilterOperator><q1:Conditions> "; 
  
  	  for (var i=0; i<this.Conditions.length; i++) {
  		var condition = this.Conditions[i];
  
  		if (condition.Operator == null)
  		  throw new Error("Must specify non-null value for condition Operator");
  
  		xmlSoapBody += "<q1:Condition><q1:AttributeName>" + condition.Field + "</q1:AttributeName> <q1:Operator>" + condition.Operator + "</q1:Operator>";
  		
  		if(condition.Operator != LogicalOperator.Null && condition.Operator != LogicalOperator.NotNull)
  			xmlSoapBody += "<q1:Values><q1:Value xsi:type=\"xsd:string\">" + condition.Value + "</q1:Value></q1:Values>";
  
  		xmlSoapBody +="</q1:Condition>";
  	  }
  
  	  xmlSoapBody += "</q1:Conditions><q1:Filters /></q1:Criteria><q1:Orders /></query></RetrieveMultiple></soap:Body></soap:Envelope>";
  
  	  var xmlt = xmlSoapHeader + xmlAuthHeader + xmlSoapBody;
  	  var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
  	  xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
  	  xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
  	  xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
  	  xmlHttpRequest.setRequestHeader("Content-Length", xmlt.length);
  	  xmlHttpRequest.send(xmlt);
  
  	  if (xmlHttpRequest.responseXML == null || xmlHttpRequest.responseXML.xml == null || xmlHttpRequest.responseXML.xml == "") {
  		if (xmlHttpRequest.responseText != null && xmlHttpRequest.responseText != "")
  		  throw new Error(xmlHttpRequest.responseText);
  		else
  		  throw new Error("Error returning response");
  	  }
  
  	  var xmlResponse = xmlHttpRequest.responseXML.xml;
  	  if (xmlHttpRequest.responseXML.documentElement.selectNodes("//error/description").length > 0) {
  		throw new Error(xmlResponse);
  	  }
  
  	  var objNodeList = xmlHttpRequest.responseXML.documentElement.selectNodes("//BusinessEntity");
  
  
  	  var totalNodesCount = objNodeList.length;
  
  	  var result = new RetrieveMultipleResult(this);
  
  	  var nodeIndex = 0;
  	  var fieldTextTemp = "";
  	  var fieldText = "";
  	  if (totalNodesCount > 0) {
  		do {
  
  		  var row = result.AddRow();
  		  for (var i=0; i<this.ColumnSet.length; i++) {
  			var columnName = this.ColumnSet[i];
  			fieldText = "";
  			var valueNode = objNodeList[nodeIndex].getElementsByTagName("q1:" + columnName)[0];
  			if (valueNode != null) {
  			  fieldTextTemp = valueNode.childNodes[0].nodeValue;
  			  if (fieldTextTemp != null && fieldTextTemp != "") {
  				fieldText = fieldText + fieldTextTemp;
  			  }
  			}
  			row.AddColumn(columnName, fieldText);
  		  }
  		  nodeIndex = nodeIndex + 1;
  		}
  		while (totalNodesCount > nodeIndex)
  	  }
  	  return result;
  	}
  	catch(err) {
  	}
  	
  	return null;
  }
  

  ---

  Cornel Croitoriu - Senior Software Developer & Entrepreneur

  If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

  

  • Proposed as answer by Cornel Croitoriu Thursday, February 24, 2011 9:50 PM

  Thursday, February 24, 2011 9:50 PM
• 

  

  0

  Sign in to vote

  I haven't used CRM2011's LINQ provider yet, but from what I know of the CRM4.0 equivalent and reading through the CRM2011 SDK, your join should look more like:

   

  from a in EntitySetA
  join b in EntitySetB on a.regardingid.Id equals b.EntitySetBId
  where a.startdatetime <= b.cutoffdate
  

   

  If it the comparison between attributes from one entity type to another that is causing the problem (I know the CRM 4.0 XRM libraries don't like it), you could try breaking the query into two operations:

   

  var candidates =
  	{
  		from a in EntitySetA
  		join b in EntitySetB on a.regardingid.Id equals b.EntitySetBId
  		select new { EntitySetA = a, EntitySetB = b }
  	};
  
  var results =
  	{
  		from c in candidates
  		where c.EntitySetA.startdatetime <= c.EntitySetB.cutoffdate
  		select c
  	};
  

   

  Caveat: as per my statement above, I haven't yet used CRM2011 SDK, so I'm piecing this all together from what I can read.

  ---

  --pogo (pat)

  • Marked as answer by DavidJennawayMVP, Moderator Tuesday, May 10, 2011 1:50 PM

  Thursday, February 24, 2011 10:39 PM