Entity Framework Updating 100K Records

Question

I am using EF6 with SQLCE database with few tables which have more than 200K records each. I am trying to update records in one of those tables but unfortunately, the updating process is very slow using the bellow methods:

var original = db.Users.Find(updatedUser.UserId);

if (original != null)
{
    original.BusinessEntityId = updatedUser.BusinessEntityId;
    original.Email = updatedUser.Email;
    original.EmployeeId = updatedUser.EmployeeId;
    original.Forename = updatedUser.Forename;
    original.Surname = updatedUser.Surname;
    original.Telephone = updatedUser.Telephone;

    db.SaveChanges();
}

The second method I tried is to actually get all records from database, then do the comparison in memory and after loop through resulted List in order to update the database:

  foreach (var _data in comparedList)
  {
      using (var db = new MyContext())
      {
          db.Users.Attach(data);
          var entry = context.Entry(data);
          entry.Property(p => p.Forename).IsModified = true;
          context.SaveChanges();
      }
  }

Any idea about efficient way of updating records using EF?

EDIT

I've used also techniques presented in the Fastest Way of Inserting in Entity Framework they eventually lead to the "Store update, insert, or delete statement affected an unexpected number of rows (0).” errors.

Don't call `SaveChanges()` until you are done updating all the records. — hunch_hunch, Sep 22 '14 at 19:14
use a stored procedure which accepts a userid as parameter. It is much faster and use the database engine for mass update. — user3514987, Sep 22 '14 at 19:58
I suggest to alter your question to give a full reproduction of the problem. Then ping me and I will reopen the post. — Patrick Hofman, Sep 22 '14 at 21:40

score 3 · Answer 1 · answered Sep 22 '14 at 19:15

3

EF is not designed for mass updates - it is designed for queries and transactional updates (a few records at a time).

If you need to mass-update data then either write a SQL statement that will do all of the updates or use an ETL tool like SSIS.

answered Sep 22 '14 at 19:15

D Stanley

149,601
11
178
240

1

I am aware of it, however I am looking the fastest way doing it using EF. – Jim Sep 22 '14 at 21:07

Entity Framework Updating 100K Records

EDIT

1 Answers1