How to Insert to DB from CVS file using Loop statement

Question

Im having problem with my code. Im inserting to my database using an uploaded comma delimeted (CSV) file which has 5 row in it. the first row successfully inserted to the DB but in the second loop to insert the second row i encounter an error:

Cannot insert explicit value for identity column in table 'bdoToDbs' when IDENTITY_INSERT is set to OFF.

Here is my Loop Codes

            while (i < users.Count)
            {
                ViewBag.CompanyCode = users[i].CompanyCode;
                ViewBag.ProductCode = users[i].ProductCode;
                ViewBag.TransactionDate = users[i].TransactionDate;
                ViewBag.TransactionTime = users[i].TransactionTime;
                ViewBag.OriginatingBranch = users[i].OriginatingBranch;
                ViewBag.CustomerNumber = users[i].CustomerNumber;
                ViewBag.TransactionOrigin = users[i].TransactionOrigin;
                ViewBag.TypeOfPayment = users[i].TypeOfPayment;
                ViewBag.CheckNumber = users[i].CheckNumber;
                ViewBag.TransactionAmount = users[i].TransactionAmount;
                ViewBag.CustomerName = users[i].CustomerName;

                bdoToDb.CompanyCode = ViewBag.CompanyCode;
                bdoToDb.ProductCode = ViewBag.ProductCode;
                bdoToDb.TransactionDate = ViewBag.TransactionDate;
                bdoToDb.TransactionTime = ViewBag.TransactionTime;
                bdoToDb.OriginatingBranch = ViewBag.OriginatingBranch;
                bdoToDb.CustomerNumber = ViewBag.CustomerNumber;
                bdoToDb.TransactionOrigin = ViewBag.TransactionOrigin;
                bdoToDb.TypeOfPayment = ViewBag.TypeOfPayment;
                bdoToDb.CheckNumber = ViewBag.CheckNumber;
                bdoToDb.TransactionAmount = ViewBag.TransactionAmount;
                bdoToDb.CustomerName = ViewBag.CustomerName;
                bdoToDb.UserName = ViewBag.DisplayName;
                bdoToDb.UserIP = HttpContext.Connection.RemoteIpAddress.ToString();
                bdoToDb.UserDate = DateTime.Now.ToString("MM/dd/yyyy");
                i++;
                _context.Add(bdoToDb);
                await _context.SaveChangesAsync();
            });
            return RedirectToAction(nameof(Index));
        }

Answer 1

One of the beautiful about EF core is that it keeps track of changes and then you can commit all those changes all at once with a save. If you move the

       await _context.SaveChangesAsync();

outside of your while loop (edited: AND construct a new object on each iteration as per the astute comment below), it will work. In the case of identity insert tables, you don't set the ID of the object, but so that you are able to know what that ID ends up being, after you call save changes, it updates the ID field of your object. So after your first save, the ID field is set to whatever the next ID was, you then update all the properties on the object and then use .Add so it tries to insert it, but the ID is already set and flagged as modified. You could also get around this by constructing a new instance of the object each time or perhaps EF has a way to clear that ID and mark it as unmodified... but I would recommend moving your save to the end as it will likely be more efficient. If this is for very large amounts of data, perhaps save every time you pass 1000 records or so, but unless data is huge, wouldn't worry about it.

To the followup inquiry, something along these lines: (you really just need distinct instances of objects because EF is storing these in memory)

            ViewBag.TransactionAmount = users[i].TransactionAmount;
            ViewBag.CustomerName = users[i].CustomerName;

            // added constructor
            bdoToDb = new BdoToDb(); // <- don't know name of your class

            bdoToDb.CompanyCode = ViewBag.CompanyCode;
            bdoToDb.ProductCode = ViewBag.ProductCode;