How to delete records between two dates in bulk?

Question

I want to delete records between two dates. I use a Fetchxml expression to get the GUID of each record between the two dates, then I use service.Delete. Here is my code:

string fetchXml = @" <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                        <entity name='new_units'>
                            <link-entity name='new_alterunitorder' from ='new_orderlineid' to = 'new_unitsid' >
                                <attribute name='new_alterunitorderid' />
                                <filter type='and'>
                                    <condition attribute='new_orderdate' operator='on-or-after' value='" + startDate.ToShortDateString() + @"' />
                                    <condition attribute='new_orderdate' operator='on-or-before' value='" + endDate.ToShortDateString() + @"' />
                                    <condition attribute='new_orderlineid' operator='eq' uiname='" + uiName + @"' uitype='new_units' value='" + unitOrderId + @"' />
                                </filter>
                            </link-entity>
                        </entity>
                    </fetch>";

                    EntityCollection result = service.RetrieveMultiple(new FetchExpression(fetchXml));

                    //Delete the collection 
                    foreach (var id in result.Entities)
                    {
                        var entRef = id.GetAttributeValue<EntityReference>("new_alterunitorderid");
                        var guid = (Guid)entRef.Id;
                        service.Delete("new_alterunits", guid);
                    }

FetchXML has a limitation of 5000 records, how can I bypass that?! Also I read that looping through using service.Delete is not the recommended way of bulk delete. Please help me find the best way to achieve what I want to do!

score 3 · Answer 1 · answered Aug 02 '18 at 18:06

You might also want to look into using the native bulk delete service via the BulkDeleteRequest class.

Below is a basic example, and this article has a more detailed example. If you want to convert your FetchXML to a QueryExpression, you can use the FetchXmlToQueryExpressionRequest class.

// Set the request properties.
bulkDeleteRequest.JobName = "Backup Bulk Delete";

// Querying activities
bulkDeleteRequest.QuerySet = new QueryExpression[]
{
    opportunitiesQuery,
    BuildActivityQuery(Task.EntityLogicalName),
    BuildActivityQuery(Fax.EntityLogicalName),
    BuildActivityQuery(PhoneCall.EntityLogicalName),
    BuildActivityQuery(Email.EntityLogicalName),
    BuildActivityQuery(Letter.EntityLogicalName),
    BuildActivityQuery(Appointment.EntityLogicalName),
    BuildActivityQuery(ServiceAppointment.EntityLogicalName),
    BuildActivityQuery(CampaignResponse.EntityLogicalName),
    BuildActivityQuery(RecurringAppointmentMaster.EntityLogicalName)
};

// Set the start time for the bulk delete.
bulkDeleteRequest.StartDateTime = DateTime.Now;

// Set the required recurrence pattern.
bulkDeleteRequest.RecurrencePattern = String.Empty;

// Set email activity properties.
bulkDeleteRequest.SendEmailNotification = false;
bulkDeleteRequest.ToRecipients = new Guid[] { currentUserId };
bulkDeleteRequest.CCRecipients = new Guid[] { };

// Submit the bulk delete job.
// NOTE: Because this is an asynchronous operation, the response will be immediate.
_bulkDeleteResponse =
    (BulkDeleteResponse)_serviceProxy.Execute(bulkDeleteRequest);
Console.WriteLine("The bulk delete operation has been requested.");

score 2 · Answer 2 · answered Aug 02 '18 at 14:52

FetchXML has a limitation of 5000 records, how can I bypass that?

Answer is Paging cookie.

Bulk delete can be done using ExecuteMultipleRequest.

public static void BulkDelete(IOrganizationService service, DataCollection<EntityReference> entityReferences)
   {
       // Create an ExecuteMultipleRequest object.
       var multipleRequest = new ExecuteMultipleRequest()
       {
           // Assign settings that define execution behavior: continue on error, return responses. 
           Settings = new ExecuteMultipleSettings()
           {
               ContinueOnError = false,
               ReturnResponses = true
           },
           // Create an empty organization request collection.
           Requests = new OrganizationRequestCollection()
       };

       // Add a DeleteRequest for each entity to the request collection.
       foreach (var entityRef in entityReferences)
       {
           DeleteRequest deleteRequest = new DeleteRequest { Target = entityRef };
           multipleRequest.Requests.Add(deleteRequest);
       }

       // Execute all the requests in the request collection using a single web method call.
       ExecuteMultipleResponse multipleResponse = (ExecuteMultipleResponse)service.Execute(multipleRequest);
   }

Reference

That method has a limit as well: "Default max batch size is 1000, that means Using this you can create/update/delete up to 1000 records only at a time." And ExecuteMultipleRequest can only be called twice or an exception is thrown! https://msdn.microsoft.com/en-us/library/jj863631.aspx — Alan Judi, Aug 02 '18 at 15:00
@AlanJudi that's correct. normally within plugin or any other operations, we don't need more than this capacity. If you are doing this in some integration, yes you have to handle it yourself within this capacity like going batch by batch.. — Arun Vinoth, Aug 02 '18 at 16:47

score 2 · Accepted Answer · answered Aug 02 '18 at 19:39

Now that we've clarified the requirement, here's the gist of how I would solve this problem. It uses a simplified FetchXML query, but you'll get the idea.

Please note the addition of top='2000' to the Fetch.

var fetchXml = @" <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' top='2000'>
                     <entity name='new_sampledata' />                                  
                   </fetch>";

var result = svc.RetrieveMultiple(new FetchExpression(fetchXml));

var entityRefs = result.Entities.Select(e=> e.ToEntityReference());

//instead of e.ToEntityReference(), you'd use e.GetAttributeValue<EntityReference>("new_alterunitorderid")
//like this:
//var entityRefs = result.Entities.Select(e=> e.GetAttributeValue<EntityReference>("new_alterunitorderid"));

var batchSize = 1000;
var batchNum = 0;
var numDeleted = 0;

while (numDeleted < entityRefs.Count())
{
    var multiReq = new ExecuteMultipleRequest()
    {
        Settings = new ExecuteMultipleSettings()
        {
            ContinueOnError = false,
            ReturnResponses = false
        },
        Requests = new OrganizationRequestCollection()
    };

    var currentList = entityRefs.Skip(batchSize * batchNum).Take(batchSize).ToList();

    currentList.ForEach(r => multiReq.Requests.Add(new DeleteRequest { Target = r }));

    svc.Execute(multiReq);

    numDeleted += currentList.Count;
    batchNum++;
}

How to delete records between two dates in bulk?

3 Answers3