Database registry is not totally updated

Question

I'm developing an app in C# that needs to update some entries on an existing database. I'm using Linq.

Ok, so, I have this User with its properties (Id, Name, City, ToUpdate, ToInsert, IsActive, IdUserUpdate and so on).

My app has a foreach, and inside I'm editing an existing registry on my database for each iteration.

It goes like this:

myList.ForEach(X => {
    var oldRegistry = database.MyTable.FirstOrDefault(Y => Y.Id == X.Id);

    oldRegistry.City = X.City;
    oldRegistry.IdUserUpdate = -3;
    oldRegistry.ToUpdate = false;
    oldRegistry.ToInsert = true;
    oldRegistry......................

    database.SaveChanges();
});

The problem is that from the second iteration on, the oldRegistry does not get its ToUpdate and ToInsert fields updated (both bit in the database).

I've tested almost everything, and nothing... If I add a breakpoint on that savechanges, I can scan my oldRegistry properties that are about to be saved to the database, and everything is like it should, but when I check the database after the SaveChanges, not all the fields are updated (IdUserUpdated is updated, but ToUpdate and ToInsert are not).

I've tried reloading the database's entities but since my foreach has 2000 iterations everytime, it takes a ridiculous amount of time to clear them (10 entities per iteration). The code for this is the following:

foreach (var entity in database.ChangeTracker.Entries())
   {
    entity.Reload();
   }

Is ID a primary key in the database? If ID is a primary key you need to delete the row and add new one. It may be faster to use a stored procedure and then in stored procedure do the update instead of a delete and add. — jdweng, Aug 24 '20 at 10:15
Maybe for the other records, the `ToUpdate` and `ToInsert` are already having the same values that you want to update. Can you confirm if that is not the case? — Sowmyadhar Gourishetty, Aug 24 '20 at 10:19
@SowmyadharGourishetty Nope. It's not the case. They are both set to false in the database. — José Reis, Aug 24 '20 at 10:22
So you either have to delete the old entry before adding new information or do the update through a stored procedure. — jdweng, Aug 24 '20 at 10:23
@jdweng Why do I need to delete the registry and insert a new one? I'm using its Id just to find it. — José Reis, Aug 24 '20 at 10:52
See following : https://stackoverflow.com/questions/29675081/how-to-update-primary-key-from-entity-framework — jdweng, Aug 24 '20 at 11:00
@jdweng I am not trying to update the Primary Key value tho... — José Reis, Aug 24 '20 at 11:14
Read the line. Doesn't matter. Any changes to a table with a Primary key requires row to be deleted. The SQL Server has two commands 1) Insert 2) Update. When you have a PK insert will only work if PK is not in the database, and update will only work when item is in the database. The Entity only uses Insert. — jdweng, Aug 24 '20 at 12:18

score 1 · Answer 1 · answered Aug 24 '20 at 11:47

Your code looks to be correct. Try the below code once if this would work by any chance.

var updateIds = myList.Select(x => x.Id).ToList();
// Get all the records which needs to be updated in a single call
var recordsToBeUpdated = database.MyTable.Where(x => updateIds.Contains(x.Id));

foreach (var record in recordsToBeUpdated)
{
   var list = myList.FirstOrDefault(x => x.Id == record.Id);

   record.City = list.City;
   record.IdUserUpdate = -3;
   record.ToUpdate = false;
   record.ToInsert = true;
}

// Call SaveChanges after updating the values for all the records
database.SaveChanges();

Thank you for your reply. I understand what you are thinking. I'll take a look and see if I can implement this on our code. — José Reis, Aug 24 '20 at 14:38

Database registry is not totally updated

1 Answers1