EF loop to update with async await

Question

I need to update my database with about 100K records over a slow network.

I thought it might be a good idea to run them async and not have to wait for the response before I run the next one. I've got this far:

        private async void button2_Click(object sender, EventArgs e)
        {

            var customerIDs = File.ReadAllLines(@"c:\temp\customerids.txt").ToList();

            foreach (var customerID in customerIDs)
            {
                var result = await DoSomethingWithTheCustomerAsync(customerID);
            }
            MessageBox.Show("Done");
        }



 private static async Task<bool> DoSomethingWithTheCustomerAsync(string strCustomerid)
        {
            var customerid = int.Parse(strCustomerid);
            using (var ctx = new MyEntities())
            {

                var customer = await ctx.Customers.FirstOrDefaultAsync(c => c.CustomerID == customerid);
                customer.CustomerNotes += " updated async";
                await ctx.SaveChangesAsync();
                return true;
            }
        }

I'd really don't want to wait for the result in the foreach-loop and then continue. I'd like to wait for all the tasks after the loop has run. I tried making a list with tasks and do Task.WaitAll(tasklist) but then the database wasn't updated.

And if this can be done, is it a good idea?

score 1 · Accepted Answer · answered Apr 04 '14 at 13:29

If at all possible, try to do this in a single message and have the "batch processing" done on the server side. E.g., a stored procedure.

It is possible to start 100k simultaneous database operations (over 100k separate connections), but this may bring your db to its knees:

private async void button2_Click(object sender, EventArgs e)
{
  var customerIDs = File.ReadAllLines(@"c:\temp\customerids.txt").ToList();
  var updates = customerIDs.Select(id => DoSomethingWithTheCustomerAsync(id));
  var results = await Task.WhenAll(updates);
  MessageBox.Show("Done");
}

score 0 · Answer 2 · answered Apr 04 '14 at 13:07

0

Realise this isn't using the EF route but, take a look at the SqlBulkCopy Class. This will be more efficient to insert your rows. You can also batch them in groups of rows to make it even quicker.

answered Apr 04 '14 at 13:07

sarin

5,227
3
34
63

not 100 - 10.000. SqlBulkCopy has overhead in setup - doign that for 100 rows is not smart. I use 60.0000 rows per update normally in a LAN. – TomTom Apr 04 '14 at 13:08
1

Apologies your original post was a little ambiguous. SqlBulkCopy is to "bulk load a SQL Server table with data from another source.". If you want to bulk update you could [bulk load to a temporary table first and then Merge the data in using a stored procedure](http://stackoverflow.com/questions/12521692/c-sharp-bulk-insert-sqlbulkcopy-update-if-exists). The alternative would be to put your data into a Table datatype and pass it to your stored procedure in a batch. Might be worth doing a few load tests with different methods to see what works best for you. – sarin Apr 04 '14 at 13:25

EF loop to update with async await

2 Answers2