Update save.change database with loop

Question

User1670624291 posted

Hi,

I am currently updating my database like this:

        public JsonResult ImgPost(Programa_Cor_Info_Status statusDatapdf )
        {
            int iiii = statusDatapdf.ID_Programa;
            var countLines = db.Programa_Cor_Info_Status.Where(x => x.ID_Programa == iiii).Count();

                int i = 1;
                while (i <= countLines)
                {

                    Programa_Cor_Info_Status statuspd = new Programa_Cor_Info_Status()
                    {
                        ID_Programa = statusDatapdf.ID_Programa,
                        ID_Linha_Cor = statusDatapdf.ID_Linha_Cor + i,
                        ID_Programa_Malha = statusDatapdf.ID_Programa_Malha + i,
                        ImagePatch = statusDatapdf.ImagePatch.Replace("C:\\fakepath\\", ""),
                    };
                    db.Entry(statuspd).State = EntityState.Modified;
                    db.SaveChanges();
                    i++;
                }
            return Json("Index", "Home");

        }

As I intend to update several lines I have a counter with number of lines that enters my statuspd adding +1 to the desired lines. it happens that in the first lap (5487, 1, 1) everything goes, but in the second (5487, 2, 2) I have following problems:

System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'

Thanks,

Answer 1

User475983607 posted

I recommend making the save logic more efficient.  Use LINQ to return a collection of Programa_Cor_Info_Status.

var statuses = db.Programa_Cor_Info_Status.Where(x => x.ID_Programa == iiii)

Loop over the Programa_Cor_Info_Status collection and set the properties to what ever you like.  Then it is just one save.

However, this might not fix the concurrency error.  The error is telling you that the database record changed between the time you pulled the record and attempted the change.  I assume there are other issues with the code.  The the initial save might have worked or you're working with the same record.  Use the debugger to figure out where the bug(s) are in your logic.

Answer 2

User1670624291 posted

Hi,

it is probably still in the same register, but to close it I would have to leave my JsonAction method and go back in, no?

I tried to get the savechanges out of the cycle and the counting works well, but it gives the same error, at the end of the counting

... 
               while (i <= countLines)
                {

                    Programa_Cor_Info_Status statuspd = new Programa_Cor_Info_Status()
                    {
                        ID_Programa = statusDatapdf.ID_Programa,
                        ID_Linha_Cor = statusDatapdf.ID_Linha_Cor + i,
                        ID_Programa_Malha = statusDatapdf.ID_Programa_Malha + i,
                        ImagePatch = statusDatapdf.ImagePatch.Replace("C:\\fakepath\\", ""),
                    };
                    db.Entry(statuspd).State = EntityState.Modified;
                    i++;
                }
            db.SaveChanges();
            return Json("Index", "Home");

        }

I think this way would be correct, to process the data internally and save it at the end instead of one by one

Answer 3

User475983607 posted

You did not follow the recommendation.   If you want to keep the same overly complex logic then turn tracking off and add the disconnected entity to the DbContext manually. 

Please read the docs.

https://docs.microsoft.com/en-us/ef/core/saving/disconnected-entities#saving-single-entities

https://docs.microsoft.com/en-us/ef/core/querying/tracking

var countLines = db.Programa_Cor_Info_Status.AsNoTracking().Where(x => x.ID_Programa == iiii).AsNoTracking().Count();

context.Update(entity);
context.SaveChanges();

Rather than fetching the count you could simply return a collection of Programa_Cor_Info_Status.  Set each item in the collection however you like, then issue one SaveChanges().

Answer 4

User287926715 posted

Hi  MiguelMi,

I found a post with a similar problem, or you can refer to it, I hope it will help you.

How to apply db.SaveChanges in an update of many record in a loop?

Best Regards,

ChaoDeng

Answer 5

User1670624291 posted

Hey mgebhard,

I'm trying to follow your thinking and I came up with something like that, but I have the same problem:

            int idPro = statusDatapdf.ID_Programa;
            var countLines = db.Programa_Cor_Info_Status.AsNoTracking().Where(x => x.ID_Programa == idPro).AsNoTracking();

            int i = 0;
            foreach (var item in countLines) {
                i++;
                Programa_Cor_Info_Status statuspd = new Programa_Cor_Info_Status()
                {
                    ID_Programa = statusDatapdf.ID_Programa,
                    ID_Linha_Cor = statusDatapdf.ID_Linha_Cor + i,
                    ID_Programa_Malha = statusDatapdf.ID_Programa_Malha + i,
                    ImagePatch = statusDatapdf.ImagePatch.Replace("C:\\fakepath\\", ""),

                };
                db.Entry(statuspd).State = EntityState.Modified;         
            }
            db.SaveChanges();

System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'

Thanks,

Answer 6

User1120430333 posted

The error message means that the primary-key ID in the EF entity/object for update,  EF didn't find a record in the database table that matched the ID  for the EF entity/object resulting in no row/0 rows were affected in the database table. It's a clue that nothing happened for a given object against a table record.

Your second problem is that the saving of EF entitles/objects the loop code should be using a System.Transaction. Either all saves are committed to the database, otherwise, if one save failed, then all all previous successful saves are rolled back and nothing is saved.

Answer 7

User1670624291 posted

Hi,

I ask if it is a mistake of my design and logic? any way to record the data through a loop?

What I have is the following table:

Where the ID_COLOR is a 1 I can save the PATCH but I want to save the same value in the other IDs_COLOR 2 and 3.

Clarify that the correct thing would not be to store these values in a table of this type, but it is the only table that I have authorization to edit or create a new one.

thanks

Answer 8

User1120430333 posted

Assuming the ID is the primary key to the record, then how can it be 3 records with 1234? Secondly,  if you are trying to update  an object/record  in the database, you should  get the object/record first, modify it and then do the save. As I recall in EF6 you must get the record  for update, modify it and save using one context to get the object and use a new second context to do the save.

Answer 9

User753101303 posted

Hi,

Load db data, update them from your model and save them back. For example for now it seems you assume that some rows have consecutive values which may not be the case (for example if ID_COLOR 2 rows are deleted you need to update 1 and 3, rather than 1 and 2).

I suspect i++ is misplaced plus it depends on how concurrency is handled. This error means that the generated UPDATE statement doesn't find any row to update so if you want to understand what happens, trace this SQL statement to understand why it fails.