Skip to main content

Dynamics CRM API: QueryExpression In Use

During the recent SharePoint 2007 and Dynamics CRM 3.0 integration project I have come across the issue using QueryExpression class of Dynamics CRM API.

Consider the following scenario:
1. We need to search for the contact in CRM using API and following criteria: contact first name and last name are equal to the values provided by search functionality or email is equal to the value provided by search functionality - email = email_value OR (first name = first_name_value AND last name = last_name_value)
2. If contact exists then we just use the identifier to do something with that contact if not exists - create the contact

Initially I wrote the following code to retrieve the desired contact:

string sEmail = ...; // initialize email value
string sFirstName = ...; // initialize first name
string sLastName = ...; // initialize last name

try
{
QueryExpression queryContact = new QueryExpression();
queryContact.EntityName = "contact";
queryContact.ColumnSet = new AllColumns();
queryContact.Criteria = new FilterExpression();
queryContact.Criteria.FilterOperator = LogicalOperator.Or;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "emailaddress1";
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { sEmail };

queryContact.Criteria.Conditions = new ConditionExpression[] { condition1 };

FilterExpression filter1 = new FilterExpression();
filter1.FilterOperator = LogicalOperator.And;

ConditionExpression condition2 = new ConditionExpression();
condition2.AttributeName = "firstname";
condition2.Operator = ConditionOperator.Equal;
condition2.Values = new object[] { sFirstName };

ConditionExpression condition3 = new ConditionExpression();
condition3.AttributeName = "lastname";
condition3.Operator = ConditionOperator.Equal;
condition3.Values = new object[] { sLastName };

filter1.Conditions = new ConditionExpression[] { condition2, condition3 };

queryContact.Criteria.Filters = new FilterExpression[] { filter1 };

BusinessEntityCollection retrievedContacts = service.RetrieveMultiple(queryContact);

if (retrievedContacts.BusinessEntities.Length > 0)
{
gResult = ((contact)retrievedContacts.BusinessEntities[0]).contactid.Value;
}
}
catch (System.Web.Services.Protocols.SoapException soapex)
{
... // error handling
}
catch (Exception ex)
{
... // error handling
}

In my case first name and last name were provided, but email address was empty string. When I ran the code this code - BusinessEntityCollection retrievedContacts = service.RetrieveMultiple(queryContact); - raised an exception: Server was unable to process request. I looked into that very informative exception details and it said "Invalid argument"... After some trial and error process I realized that it was empty condition value that caused an exception.

I changed my code to look as follows:


string sEmail = ...; // initialize email value
string sFirstName = ...; // initialize first name
string sLastName = ...; // initialize last name
string sEmptyValue = "Empty";
try
{
QueryExpression queryContact = new QueryExpression();
queryContact.EntityName = "contact";
queryContact.ColumnSet = new AllColumns();
queryContact.Criteria = new FilterExpression();
queryContact.Criteria.FilterOperator = LogicalOperator.Or;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "emailaddress1";
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { string.IsNullOrEmpty(sEmail) ? sEmptyValue : sEmail };

queryContact.Criteria.Conditions = new ConditionExpression[] { condition1 };

FilterExpression filter1 = new FilterExpression();
filter1.FilterOperator = LogicalOperator.And;

ConditionExpression condition2 = new ConditionExpression();
condition2.AttributeName = "firstname";
condition2.Operator = ConditionOperator.Equal;
condition2.Values = new object[] { string.IsNullOrEmpty(sFirstName) ? sEmptyValue : sFirstName };

ConditionExpression condition3 = new ConditionExpression();
condition3.AttributeName = "lastname";
condition3.Operator = ConditionOperator.Equal;
condition3.Values = new object[] { string.IsNullOrEmpty(sLastName) ? sEmptyValue : sLastName };

filter1.Conditions = new ConditionExpression[] { condition2, condition3 };

queryContact.Criteria.Filters = new FilterExpression[] { filter1 };

BusinessEntityCollection retrievedContacts = service.RetrieveMultiple(queryContact);

if (retrievedContacts.BusinessEntities.Length > 0)
{
gResult = ((contact)retrievedContacts.BusinessEntities[0]).contactid.Value;
}
}
catch (System.Web.Services.Protocols.SoapException soapex)
{
... // error handling
}
catch (Exception ex)
{
... // error handling
}

After the change all works well.


Comments

Popular posts from this blog

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server.

According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition.
The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you wou…

SharePoint 2010 Search Issue - FQDN Crawl

I have recently set up a standalone SharePoint 2010 environment.

The Web application was created with host header and the site collection is accessible from the client machines, but not internally. That was because of using FQDN to access the Web site.

The error when I tried to access site internally was similar to the one described here:

You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version

And the search returned the following error in the Event Log:

"The start address cannot be crawled.

Context: Application 'Search_Service_Application', Catalog 'Portal_Content'

Details: This item could not be crawled because the crawler could not connect to the repository."

One of the suggestions was to disable the loopback check, but that would compromise the Web server.

So what I have done was:

1. Added a binding to my IIS Web site for a different port. Let's say my Web server name is win-v7m…

SharePoint 2013 - Setting Up External Content Type

There were earlier posts where we discussed External Content Types setup for SharePoint 2010:

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

External Content Types - Reload - Setting up for SQL Server database using SQL Server authentication - SharePoint 2010 Server

This one is about creating connection to the custom SQL Server database (External System) in SharePoint 2013.

1. Create Secure Store Service Target Application

1.1. Go to Central Administration -> Manage Service Applications -> Secure Store Service Application. Click "Generate New Key" if required:


1.2. Provide Pass Phrase:

1.3. Create "New" to create new Target Application:


1.4. Provide the name and other parameters and click "Next":

Note: It's good idea to specify "Group" for Target Application Type. In that case you would be able to manage access to the external data using Active Directory groups rather …