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:
- 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.
- Implement paging on this query.
- Establish an auxiliary QueryExpression instance.
- 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.
- 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; dLIndex++; } // 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 queryToBulkDelete10Leads.Criteria.Conditions.Add(leadIdCondition); // 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; try { this.crmService.Execute(bulkDeleteLeads); } catch (SoapException sException) { throw new ApplicationException(sException.Detail.InnerText); } }
No comments:
Post a Comment
Unrelated comments to posts may be summarily disposed at the author's discretion.