CRM 2011 Query Expression samples

Query Expression Sample 1

// Create the ConditionExpression.

ConditionExpression condition1 = new ConditionExpression("contractid", ConditionOperator.Equal, entityId);

 // Create the FilterExpression.

 FilterExpression filter = new FilterExpression();

// Set the properties of the filter.

 filter.FilterOperator = LogicalOperator.And;

 filter.Conditions.Add(condition1);

// Create the QueryExpression object.

QueryExpression query = new QueryExpression();

// Set the properties of the QueryExpression object.

query.EntityName = "contractdetail";

query.ColumnSet = new ColumnSet(true);

query.Criteria = filter;

//Build Retrieve request

RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

retrieve.Query = query;

             

//Retrieve SLARuleTemplates

RetrieveMultipleResponse response = (RetrieveMultipleResponse)service.Execute(retrieve);

Query Expression Sample(Link Entity) 2

//Create a query expression specifying the link entity alias and the columns of the link entity that you want to return

QueryExpression qe = new QueryExpression();

qe.EntityName = "account";

qe.ColumnSet = new ColumnSet();

qe.ColumnSet.Columns.Add("name");

qe.LinkEntities.Add(new LinkEntity("account", "contact", "primarycontactid", "contactid", JoinOperator.Inner));

qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");

qe.LinkEntities[0].EntityAlias = "primarycontact";

EntityCollection ec = _orgService.RetrieveMultiple(qe);

Query Expression Sample( One-To-Many Relationship)

// Construct query

// Condition where task attribute equals account id.

ConditionExpression condition = new ConditionExpression();

condition.AttributeName = "regardingobjectid";

condition.Operator = ConditionOperator.Equal;

condition.Values.Add(acctId.ToString());

//Create a column set.

ColumnSet columns = new ColumnSet("subject");

// Create query expression.

QueryExpression query1 = new QueryExpression();

query1.ColumnSet = columns;

query1.EntityName = "task";

query1.Criteria.AddCondition(condition);

EntityCollection result1 = _serviceProxy.RetrieveMultiple(query1);

Page Large Result Sets with Query Expression

//  Query using the paging cookie.

// Define the paging attributes.

// The number of records per page to retrieve.

int fetchCount = 3;

// Initialize the page number.

int pageNumber = 1;

// Initialize the number of records.

int recordCount = 0;

// Define the condition expression for retrieving records.

ConditionExpression pagecondition = new ConditionExpression();

pagecondition.AttributeName = "address1_stateorprovince";

pagecondition.Operator = ConditionOperator.Equal;

pagecondition.Values.Add("WA");

// Define the order expression to retrieve the records.

OrderExpression order = new OrderExpression();

order.AttributeName = "name";

order.OrderType = OrderType.Ascending;

// Create the query expression and add condition.

QueryExpression pagequery = new QueryExpression();

pagequery.EntityName = "account";

pagequery.Criteria.AddCondition(pagecondition);

pagequery.Orders.Add(order);

pagequery.ColumnSet.AddColumns("name", "address1_stateorprovince", "emailaddress1", "accountid");

// Assign the pageinfo properties to the query expression.

pagequery.PageInfo = new PagingInfo();

pagequery.PageInfo.Count = fetchCount;

pagequery.PageInfo.PageNumber = pageNumber;

// The current paging cookie. When retrieving the first page,

// pagingCookie should be null.

pagequery.PageInfo.PagingCookie = null;

Console.WriteLine("#\tAccount Name\t\t\tEmail Address");

  while (true)

  {

    // Retrieve the page.

     EntityCollection results = _serviceProxy.RetrieveMultiple(pagequery);

     if (results.Entities != null)

     {

        // Retrieve all records from the result set.

         foreach (Account acct in results.Entities)

         {

           Console.WriteLine("{0}.\t{1}\t\t{2}",++recordCount,acct.EMailAddress1,                       

           acct.Name);

         }

    }

    // Check for more records, if it returns true.

    if (results.MoreRecords)

    {

       // Increment the page number to retrieve the next page.

       pagequery.PageInfo.PageNumber++;

       // Set the paging cookie to the paging cookie returned from current results.

       pagequery.PageInfo.PagingCookie = results.PagingCookie;

    }

    else

    {

       // If no more records are in the result nodes, exit the loop.

       break;

    }

 }

Multiple condition with Query Expression.

// Create the ConditionExpression.

ConditionExpression condition1 = new ConditionExpression("new_contractserviceid", ConditionOperator.Equal, contractserviceid);

ConditionExpression condition2 = new ConditionExpression("new_componenttemplateid", ConditionOperator.Equal, componentTemplate.Get(executionContext).Id);

// Create the FilterExpression.

FilterExpression filter = new FilterExpression();

// Set the properties of the filter.

filter.FilterOperator = LogicalOperator.And;

filter.Conditions.Add(condition1);

filter.Conditions.Add(condition2);

// Create the QueryExpression object.

QueryExpression query = new QueryExpression();

// Set the properties of the QueryExpression object.

query.EntityName = "new_component";

query.ColumnSet = new ColumnSet(true);

query.Criteria = filter;

//Build Retrieve request

RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

retrieve.Query = query;

          

//Retrieve SLARuleTemplates

RetrieveMultipleResponse response = (RetrieveMultipleResponse)service.Execute(retrieve);