Bulk Copy Update C# ASP.NET MVC

Question

I have 2 tables both with relatively large amounts of information. As part of the process I need to update Table 2(Inv) with the information from Table 1. Essentially copy the CC value from Table 1(Acc) to PCC value in Table 2.

The amount of updates vary from 4000 to 40000 rows and the page hangs for quite a while. I cannot run this in the background because another process has to run directly after it which needs this update to happen first and output to user.

How can I speed things up?

Current code:

            var invList = db.Inv.Where(m => m.ID == GI.Id).ToList();
            var invListAcc = invList .Select(m => m.AccNum).ToList();
            var accExtract = db.Acc.Where(m => invListAcc .Contains(m.AccNum)).ToList();
            foreach(var inv in invList)
            {
                var invDB = db.Inv.Find(inv.Id);
                var accCC = accExtract.Where(m => m.AccNum== invDB.AccNum).Select(p=>p.CC).FirstOrDefault();
                if(accCC != null)
                {
                    invDB.PCC = accCC;
                    db.Entry(invDB).State = EntityState.Modified;
                    db.SaveChanges();

                } 
            }

Table : Acc

+------------+---------+
|    AccNum  |   CC    |
+------------+---------+
|    123     |   34    |
|    124     |   24    |
+------------+---------+

Table : Inv

+-------+------------+---------+
|  Id   |    AccNum  |   PCC   |
+-------+------------+---------+
|   1   |    123     |         |
|   2   |    124     |         |
+-------+------------+---------+

Answer 1

move the db.SaveChanges(); out side of the foreach look... yes it will all be done.. your using the framework incorrectly

 var invList = db.Inv.Where(m => m.ID == GI.Id).ToList();
 var invListAcc = invList .Select(m => m.AccNum).ToList();
 var accExtract = db.Acc.Where(m => invListAcc .Contains(m.AccNum)).ToList();
 foreach(var inv in invList)
 {
      var invDB = db.Inv.Find(inv.Id);
      var accCC = accExtract.Where(m => m.AccNum== invDB.AccNum).Select(p=>p.CC).FirstOrDefault();
      if(accCC != null)
      {
           invDB.PCC = accCC;
           db.Entry(invDB).State = EntityState.Modified;

      } 
 }
 db.SaveChanges();

also see if you can just fetch what you are working with instead of calling var invDB = db.Inv.Find(inv.Id); on each loop... this is a db call to the db for each loop which is expensive

use the other answer here to fetch all the records to loop... then update then SaveChanges.

UPDATE

• update the PCC of all Invoice with the CC of an Account where the invoice is linked to the account.

• your solution depends on how you have modeled your entities

• i wouldn't recommend you keep this running in the system, as it should be a once of requirement
  • but if you modeled them correctly you could do something like;

using include

var invList = db.Inv.include(x=>x.Acc).tolist(); -- required EF extension for include, this is 1 db call
foreach(var item in invList)
{
    var account = item.Accl
    if (account != null )
    {
        item.PCC = account.CC  
    }   
}
db.SaveChanges();

Answer 2

You can do it quickly using pure SQL

update A
set A.ACC =  B.CC
from Inv A inner join Acc B
on B.AccNum = A.AccNum