Insert/Update bulk data to sql table in entity framework

Question

I have one product table with consist of product number , price and currency. I upload the data through excel file with the data. When i upload the data then i create a list at back end with all data from excel and then send entire list to server to update/insert data in the table.

               foreach(Product value in item)
                {
                    Product p = context.Product.SingleOrDefault(p => p.ProductNumber == value.ProductNumber && p.CurrencyId == value.CurrencyId);
                    if (p!= null)
                    {

                        context.Entry<Product>(p).Property(p => p.ProductPrice).IsModified = true;
                        p.ProductPrice = value.ProductPrice ;

                        context.SaveChanges();
                    }
                    else
                    {

                        context.Product.Add(value);
                        context.SaveChanges();
                    }
                }

But as the data increases (Product increase) then upload takes a lot of time. Any suggestion to optimize this? One stuff i am thinking is of Index on productnumber and currency column.

Answer 1

The performance issue is not caused because of the index (Of course if no index exists, this will also help) but because of the number of database round-trip your code is performing.

For every product:

• You check if the product exists (1 database round-trip)
• You insert/update the product (1 database round-trip)

So if you upload 10,000 products from your excel file, you will perform 20,000 database round-trip which is insane.

There are some tricks which may help the performance a little bit like adding multiple products to insert with AddRange instead of Add, but you would end up with the same performance issue caused by the number of database round-trip.

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library allows to dramatically improve performance by performing:

• BulkSaveChanges
• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

Example:

// Insert or Update using ProductNumber && CurrencyId as the Key
ctx.BulkMerge(list, operation =>
{
    operation.ColumnPrimaryKeyExpression = x => new {x.ProductNumber, x.CurrencyId};
});