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));
        }

Possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — devNull, Sep 30 '19 at 04:01
I have a interface that get the users's active directory detail, I put it in the ViewBag. — Jose Baleros Jr, Sep 30 '19 at 05:03
But the values were overwritten in the loop so it is only the last row in the ViewBag — Sir Rufo, Sep 30 '19 at 05:20

Victor Thomas Wilcox Jr. · Accepted Answer · 2019-09-30T06:03:38.763

0

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;

edited Sep 30 '19 at 06:03

answered Sep 30 '19 at 04:06

Victor Thomas Wilcox Jr.

146
1
7

If I do this, it will only save the last row of the CSV to the database not the all the rows. – Jose Baleros Jr Sep 30 '19 at 05:01
2

@josebalerosjr The main problem is that you reuse the bdoToDb instance for each iteration. – Sir Rufo Sep 30 '19 at 05:30
1

@josebalerosjr This is EF. Every entity has to have its own object as long as the context is alive. Make a new bdoToDb object for every insert. – relatively_random Sep 30 '19 at 05:31
Good point, @josebalerosjr. Guess you need to construct a new object in any case. – Victor Thomas Wilcox Jr. Sep 30 '19 at 05:51
@relatively_random can you give me an idea how to? Im just new in asp.net core sorry. – Jose Baleros Jr Sep 30 '19 at 05:57
@josebalerosjr I updated the answer and code with relatively_random's astute observation considered. I found this a helpful treatise on some of the inner workings of EF core such as context and how it tracks changes, etc. You might find it similarly interesting: https://www.youtube.com/watch?v=LDRxo6wDIE0&t=4159s – Victor Thomas Wilcox Jr. Sep 30 '19 at 06:07
Thank you @VictorThomasWilcoxJr. Im gonna watch this video. – Jose Baleros Jr Sep 30 '19 at 06:19
Oh, just saw that Sir Rufo also spotted it, thanks to you as well! – Victor Thomas Wilcox Jr. Sep 30 '19 at 06:26
Hi guys. This article get my concern done. Is it against EF concept or not? https://stackoverflow.com/questions/43611326/raw-sql-in-net-core – Jose Baleros Jr Sep 30 '19 at 07:38
@josebalerosjr If you want to use raw sql then do it, if you want to use ORM like EF-core then do it, but do not mix them up. – Sir Rufo Sep 30 '19 at 07:44
Thank you @SirRufo but using this now i think i've already mix it, because most of my codes are auto generated from visual studio like controllers then i just edit some lines. I just use this raw in this part because im having a hard time doing it in ORM – Jose Baleros Jr Sep 30 '19 at 07:55
1

@VictorThomasWilcoxJr. Thank you. my code is working now.. I add the `bdoToDb = new BdoToDb();` to my previous code and it work perfectly fine. Thanks a lot. – Jose Baleros Jr Sep 30 '19 at 09:05

How to Insert to DB from CVS file using Loop statement

1 Answers1