Shows how to create a query expression that uses condition operators.
Example
This sample code shows how to build the following SQL query using a query expression:
SELECT contact.fullname, contact.address1_telephone1
FROM contact
LEFT OUTER JOIN account
ON contact.parentcustomerid = account.accountid
AND
account.name = 'Microsoft'
WHERE (contact.address1_stateorprovince = 'WA'
AND
contact.address1_city in ('Redmond', 'Bellevue', 'Kirland', 'Seattle')
AND
contact.address1_telephone1 like '(206)%'
OR
contact.address1_telephone1 like '(425)%'
AND
DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
AND
DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
AND
contact.emailaddress1 Not Null
)
using System;
using System.Xml;
using CrmSdk;
using Microsoft.Crm.Sdk.Utility;
namespace Microsoft.Crm.Sdk.HowTo.Query
{
///
/// This sample shows how to use condition operators in a database query.
///
public class ConditionOperators
{
public ConditionOperators()
{
}
public static bool Run(string crmServerUrl, string orgName)
{
bool success = true;
try
{
// Set up the CRM Service.
CrmService service = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.GetCrmService(crmServerUrl, orgName);
service.PreAuthenticate = true;
#region Setup Data Required for this Sample
// Create an account
account microsoftAccount = new account();
microsoftAccount.name = "Microsoft";
Guid microsoftAccountId = service.Create(microsoftAccount);
// Create some contacts that meet the query conditions
contact contact206 = new contact();
contact206.fullname = "Adam Carter";
contact206.firstname = "Adam";
contact206.lastname = "Carter";
contact206.emailaddress1 = "adamc@microsoft.com";
contact206.address1_city = "Redmond";
contact206.address1_stateorprovince = "WA";
contact206.address1_telephone1 = "(206)555-5555";
contact206.parentcustomerid = new Customer();
contact206.parentcustomerid.type = "account";
contact206.parentcustomerid.Value = microsoftAccountId;
contact contact425 = new contact();
contact425.fullname = "Adina Hagege";
contact425.firstname = "Adina";
contact425.lastname = "Hagege";
contact206.emailaddress1 = "adinah@microsoft.com";
contact425.address1_city = "Bellevue";
contact425.address1_stateorprovince = "WA";
contact425.address1_telephone1 = "(425)555-5555";
contact425.parentcustomerid = new Customer();
contact425.parentcustomerid.type = "account";
contact425.parentcustomerid.Value = microsoftAccountId;
Guid contact206Id = service.Create(contact206);
Guid contact425Id = service.Create(contact425);
#endregion
// Build the following SQL query using QueryExpression:
//
// SELECT contact.fullname, contact.address1_telephone1
// FROM contacts
// LEFT OUTER JOIN accounts
// ON contact.parentcustomerid = account.accountid
// AND
// account.name = 'Microsoft'
// WHERE (contact.address1_stateorprovince = 'WA'
// AND
// contact.address1_city in ('Redmond', 'Bellevue', 'Kirland', 'Seattle')
// AND
// contact.address1_telephone1 like '(206)%'
// OR
// contact.address1_telephone1 like '(425)%'
// AND
// DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
// AND
// DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
// AND
// contact.emailaddress1 Not NULL
// )
// Create state condition
ConditionExpression stateCondition = new ConditionExpression();
stateCondition.AttributeName = "address1_stateorprovince";
stateCondition.Operator = ConditionOperator.Equal;
stateCondition.Values = new string[] { "WA" };
// Create city condition
ConditionExpression cityCondition = new ConditionExpression();
cityCondition.AttributeName = "address1_city";
cityCondition.Operator = ConditionOperator.In;
cityCondition.Values = new string[] { "Redmond", "Bellevue", "Kirkland", "Seattle" };
// Create telephone condition for area code 206.
ConditionExpression phoneCondition206 = new ConditionExpression();
phoneCondition206.AttributeName = "address1_telephone1";
phoneCondition206.Operator = ConditionOperator.Like;
phoneCondition206.Values = new string[] { "(206)%" };
// Create telephone condition for area code 425.
ConditionExpression phoneCondition425 = new ConditionExpression();
phoneCondition425.AttributeName = "address1_telephone1";
phoneCondition425.Operator = ConditionOperator.Like;
phoneCondition425.Values = new string[] { "(425)%" };
// Create the filter used to OR the phone conditions.
FilterExpression phoneFilter = new FilterExpression();
phoneFilter.FilterOperator = LogicalOperator.Or;
phoneFilter.Conditions = new ConditionExpression[] { phoneCondition206, phoneCondition425 };
// Create the city filter
FilterExpression cityFilter = new FilterExpression();
cityFilter.Conditions = new ConditionExpression[] { cityCondition };
// Create the "created in last 30 days" condition
ConditionExpression last30DaysCondition = new ConditionExpression();
last30DaysCondition.AttributeName = "createdon";
last30DaysCondition.Operator = ConditionOperator.LastXDays;
last30DaysCondition.Values = new object[1];
last30DaysCondition.Values[0] = 30;
// Create the "Not NULL" condition
ConditionExpression emailNotNullCondition = new ConditionExpression();
emailNotNullCondition.AttributeName = "emailaddress1";
emailNotNullCondition.Operator = ConditionOperator.NotNull;
// Create the "created in last 30 days" filter
FilterExpression last30DaysFilter = new FilterExpression();
last30DaysFilter.Conditions = new ConditionExpression[] { last30DaysCondition };
// Create the "Not NULL" filter
FilterExpression emailNotNullFilter = new FilterExpression();
emailNotNullFilter.Conditions = new ConditionExpression[] { emailNotNullCondition };
// Create the outer most filter to AND the state condition with the other filters
FilterExpression outerFilter = new FilterExpression();
outerFilter.FilterOperator = LogicalOperator.And;
outerFilter.Conditions = new ConditionExpression[] { stateCondition };
outerFilter.Filters = new FilterExpression[] { cityFilter, phoneFilter, last30DaysFilter, emailNotNullFilter };
// Determine which columns are returned
// NOTE: the ID of the entity being queried will always be returned
ColumnSet resultSetColumns = new ColumnSet();
resultSetColumns.Attributes = new string[] { "fullname", "address1_telephone1" };
// Create the outer join condition
ConditionExpression outerJoinCondition = new ConditionExpression();
outerJoinCondition.AttributeName = "name";
outerJoinCondition.Operator = ConditionOperator.Equal;
outerJoinCondition.Values = new string[] { "Microsoft" };
// Create the outer join filter.
FilterExpression outerJoinFilter = new FilterExpression();
outerJoinFilter.Conditions = new ConditionExpression[] { outerJoinCondition };
// Create the outer join link.
LinkEntity outerJoinAccount = new LinkEntity();
outerJoinAccount.JoinOperator = JoinOperator.LeftOuter;
outerJoinAccount.LinkCriteria = outerJoinFilter;
outerJoinAccount.LinkFromAttributeName = "contactid";
outerJoinAccount.LinkFromEntityName = EntityName.contact.ToString();
outerJoinAccount.LinkToAttributeName = "accountid";
outerJoinAccount.LinkToEntityName = EntityName.account.ToString();
// Put everything together in an expression.
QueryExpression qryExpression = new QueryExpression();
qryExpression.Criteria = outerFilter;
qryExpression.ColumnSet = resultSetColumns;
// Set the table to query.
qryExpression.EntityName = EntityName.contact.ToString();
qryExpression.LinkEntities = new LinkEntity[] { outerJoinAccount };
// Return all records.
qryExpression.Distinct = false;
// Execute the query.
BusinessEntityCollection contactsResultSet = service.RetrieveMultiple(qryExpression);
// Iterate through each contact to build a call list.
foreach (contact aContact in contactsResultSet.BusinessEntities)
{
// Access only columns included in the column set of the query expression.
// NOTE: All other columns will be null except for the entities ID, which is always returned.
Console.WriteLine("Contact's ID: " + aContact.contactid.Value);
Console.WriteLine("Contact Name: " + aContact.fullname);
Console.WriteLine("Contact Phone: " + aContact.address1_telephone1);
}
#region check success
// Validate that an expected contact was returned.
if (contactsResultSet.BusinessEntities.Length == 0)
{
success = false;
}
else
{
bool contactFound = false;
foreach (contact aContact in contactsResultSet.BusinessEntities)
{
if (aContact.contactid.Value == contact206Id ||
aContact.contactid.Value == contact425Id )
{
contactFound = true;
break;
}
}
if (!contactFound)
{
success = false;
}
}
#endregion
#region Remove Data Required for this Sample
// Delete contacts.
service.Delete(EntityName.contact.ToString(), contact206Id);
service.Delete(EntityName.contact.ToString(), contact425Id);
// Delete account.
service.Delete(EntityName.account.ToString(), microsoftAccountId);
#endregion
}
catch (System.Web.Services.Protocols.SoapException)
{
// Perform error handling here.
throw;
}
catch (Exception)
{
throw;
}
return success;
}
}
}
