Bulk Delete in MVC

Question

I am having below code, code is summarized for better understanding, I have try catch blocks in each delete statements. :-

    foreach(var item in listEntry)
    {
           .. Calculation code

            string deletedata = "DELETE FROM [" + schemaName + "].[OT] WHERE CompanyId=" + companyId + " AND EmployeeId = " + StrECode + " AND OTDate = '" + StrInputDate.ToString("yyyy-MM-dd") + "'";
            ctx.Database.ExecuteSqlCommand(deletedata);

           .. Large Calculation code

            string deletedata1 = "DELETE FROM [" + schemaName + "].[ENTRY] WHERE CompanyId=" + companyId + " AND EmployeeId = " + StrECode + " AND EntryDate = '" + StrDate.ToString("yyyy-MM-dd") + "'";
            ctx.Database.ExecuteSqlCommand(deletedata1);
        }

       BulkInsert(parameters);
       BulkInsert(parameters);

I am simply deleting the records in a foreach loop then i am performing Bulk Insert. It was all working good but now data volume has increased. the foreach loops for 50,000 times.

Now what is happening is for some unknown reason application is stopping in the middle & it breaks somewhere in the foreach. so the data is getting deleted in large volumes & the Bulk Insert is not performed, so there is huge data loss.

I am not getting any error as so i also have logs, but the application doesn't reach the BulkInsert. And this is happening in some rare cases & not all the time.

What can i do in such a scenario? How to perform Bulk Delete? How to roll back transactions in my case?

Are you using EF6? (does your inner foreach calculations use the deleted items? why not deleting them at the end after foreach) — Andrei Filimon, Dec 23 '16 at 11:05
Also it is a bad practice to load so much data 50,000+ items via EF, you might think of processing the whole thing in small batches (use some pagination when you load the full list) — Andrei Filimon, Dec 23 '16 at 11:10
@AndreiFilimon I am running a large process in this page, there is no scope for paginatio, it is a kind of Payroll Processing, — Anup, Dec 23 '16 at 11:27
Can you run all the deletes at once? (not inside the foreach, but outside with a single query execute command), even if it is a large process you can still split it in small batches (pages) process 1000 at a time or something. — Andrei Filimon, Dec 23 '16 at 11:34
@AndreiFilimon yes i can do that, please suggest me some idea how to exactly do this. — Anup, Dec 23 '16 at 11:41
Solutions has been give here:http://stackoverflow.com/questions/11592176/bulk-delete-in-entity-framework — Andrei Filimon, Dec 23 '16 at 11:55

score 0 · Answer 1 · answered Dec 23 '16 at 13:51

Disclaimer: I'm the owner of Entity Framework Extensions

This library allows you to perform all bulk operations you need for your scenarios:

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

and even BulkSaveChanges.

It uses the Entity Framework transaction, so if you need it, you simply have to start a new transaction from Entity Framework.

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

Bulk Delete in MVC

1 Answers1