Friday, August 13, 2010

Limiting the Records in a BulkDeleteRequest

A recent inquiry to the CRM Developer forum got me thinking: since the BulkDeleteRequest.QuerySet property ignores the PagingInfo property of the query instance, how would one limit the operation to a certain number of records?  The answer: use two queries.

It’s not always simple to construct a QueryExpression or QueryByAttribute instance to limit the set of records returned, without using the PagingInfo.  If all you really want to do is take a hatchet to a certain number of records, then the best approach is as follows:

  1. Establish a QueryExpression or QueryByAttribute with the parameters required to gather the records you desire to delete.  Be sure to include the record’s “Key” attribute in the ColumnSet.
  2. Implement paging on this query.
  3. Establish an auxiliary QueryExpression instance.
  4. Gather the “Key” values from the desired “page” of the primary query, and establish them as values—in the auxiliary query—as a part of a condition upon the “Key” attribute.
  5. Pass the auxiliary query to the BulkDeleteRequest.

The solution here is straight-forward and easy to understand.  When the web service gives us lemons, all we need is sugar water.  Example code below:

// Initialize our first query
QueryByAttribute queryForInactiveLeads = new QueryByAttribute();

// Set the entity to "lead"
queryForInactiveLeads.EntityName = EntityName.lead.ToString();

// Retrieve only the GUID of the records
queryForInactiveLeads.ColumnSet = new ColumnSet(new string[] { "leadid" });

// Query for the "statecode" attribute
queryForInactiveLeads.Attributes = new string[] {"statecode"};

// Specifically, disqualified ones
queryForInactiveLeads.Values = new object[] { 2 };

// Now limit the pages to 10 records each
queryForInactiveLeads.PageInfo = new PagingInfo();
queryForInactiveLeads.PageInfo.Count = 10;

// And then call out the first page, only
queryForInactiveLeads.PageInfo.PageNumber = 1;

// Retrieve the matching records
BusinessEntityCollection disqualifiedLeads = this.crmService.RetrieveMultiple(queryForInactiveLeads);

// Don't want to bulk delete nothing
if (disqualifiedLeads.BusinessEntities.Count > 0)
  // Initialize the array that holds the Guids returned from the first query
  Guid[] disqualifiedLeadIds = new Guid[disqualifiedLeads.BusinessEntities.Count];

  // Initialize our array indexer
  int dLIndex = 0;

  // Iterate through each returned record and place its Guid into the array
  foreach (lead disqualifiedLead in disqualifiedLeads.BusinessEntities)
    disqualifiedLeadIds[dLIndex] = disqualifiedLead.leadid.Value;

  // Initialize our second query
  QueryExpression queryToBulkDelete10Leads = new QueryExpression(EntityName.lead.ToString());

  // Initialize our query criteria
  queryToBulkDelete10Leads.Criteria = new FilterExpression();

  // Set the filter operator to "Or", though it doesn't matter
  queryToBulkDelete10Leads.Criteria.FilterOperator = LogicalOperator.Or;

  // Initialize a new condtion for the filter that uses our Guid array
  ConditionExpression leadIdCondition = new ConditionExpression("leadid", ConditionOperator.In, disqualifiedLeadIds);

  // Load the condition into the filter

  // Initialize the BulkDeleteRequest
  BulkDeleteRequest bulkDeleteLeads = new BulkDeleteRequest();

  bulkDeleteLeads.JobName = "Delete 10 Disqualified Leads";
  bulkDeleteLeads.QuerySet = new QueryBase[] { queryToBulkDelete10Leads };
  bulkDeleteLeads.StartDateTime = CrmDateTime.Now;
  bulkDeleteLeads.SendEmailNotification = false;
  bulkDeleteLeads.ToRecipients = new Guid[] { };
  bulkDeleteLeads.CCRecipients = new Guid[] { };
  bulkDeleteLeads.RecurrencePattern = string.Empty;

  catch (SoapException sException)
    throw new ApplicationException(sException.Detail.InnerText);