Simplifying CRM Queries using Object Initializers

When I joined my first CRM project more than half a year ago, I found the way to create queries quite tedious:

  • create the condition expressions
  • create the filter expression
  • optionally create the link expressions
  • create the query expression

leading to endless pages of query construction code, as illustrated in this MSDN sample and elsewhere.

Fortunately I found that you can streamline CRM4 queries by using object initializers introduced in VS 2008. No more proliferation of temporary variables storing query parts

public contact GetContactUniqueByName(Guid organisationId, 
  string firstname, string lastname, string email)
{
  QueryExpression query = new QueryExpression {
    EntityName = EntityName.contact.ToString(),
    ColumnSet = new ColumnSet {
      Attributes = contactColumns
    },
    Criteria = new FilterExpression {
      FilterOperator = LogicalOperator.And,
      Conditions = new ConditionExpression[] { 
        new ConditionExpression {
          AttributeName = "parentcustomerid",
          Operator = ConditionOperator.Equal,
          Values = new object[] { organisationId }
        },
        new ConditionExpression {
          AttributeName = "firstname",
          Operator = ConditionOperator.Equal,
          Values = new string[] { firstname },
        },
        new ConditionExpression {
          AttributeName = "lastname",
          Operator = ConditionOperator.Equal,
          Values = new string[] { lastname }
        },
        new ConditionExpression {
          AttributeName = "emailaddress1",
          Operator = ConditionOperator.Equal,
          Values = new string[] { email }
        }
      }
    }
  };
  ...
}

Trying to apply the same strategy to CRM2011, I found that the Conditions property is no longer assignable, which means you cannot pass the ConditionExpression array as object initializer.

A little helper method works around this new restriction

public FilterExpression CreateFilter(LogicalOperator op, 
    params ConditionExpression[] conditions)
{
  var filter = new FilterExpression(op);
  filter.Conditions.AddRange(conditions);
  return filter;
}

allowing the above query to be re-written (using the shortened ConditionExpression constructor) for CRM2011 as:

public contact GetContactUniqueByName(Guid organisationId, 
    string firstname, string lastname, string email)
{
  QueryExpression query = new QueryExpression
  {
    EntityName = Contact.EntityLogicalName,
    ColumnSet = new ColumnSet(true),
    Criteria = CreateFilter(LogicalOperator.And,
        new ConditionExpression(
          "parentcustomerid", ConditionOperator.Equal, organisationId),
        new ConditionExpression(
          "firstname", ConditionOperator.Equal, firstname),
        new ConditionExpression(
          "lastname", ConditionOperator.Equal, lastname),
        new ConditionExpression(
          "emailaddress1", ConditionOperator.Equal, email)
	)
  };

  ...
}

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

%d bloggers like this: