saving changes in a loop in identity franework

Question

I want to save changes to the SQL table in a loop. I have the following code:

string[] request={"test1", "test2", "test3"};
TransactionData TTD = new TransactionData();
 using ( IdContext = new ProofContext())
                {
                    foreach(var req in request)
                    {
                        TTD.Description = req;
                        TTD.RecordId = 12;
                        TTD.PaymentStatus = "Failed";
                        IdContext.TransactionData.Add(TTD);
                        IdContext.SaveChanges();
                    }
                    
                }

I want to save three rows in the SQL table with three different ID. The table has one identity column and it is the primary key too. When I run my code, the changes are saved the very first time, but second time when I tried to save the changes, I got the error saying:

Cannot insert explicit value for identity column in table TransactionData when Identity Insert is set off. Below is the image:

I tried running this statement on SQL side:

SET IDENTITY_INSERT TransactionData ON

but still , I keep getting the error. This is the table model:

public partial class TransactionData

{

   

    public int InfoId { get; set; }  // this is the identity column in the database

    public decimal UnitPrice { get; set; }

    public decimal ServiceFee { get; set; }

    public decimal TotalAmount { get; set; }

    public string Sku { get; set; }

}

any help will be greatly appreciated.

Answer 1

Hi @Anjali Agarwal , Welcome to Microsoft Q&A,

In the IdContext.TransactionData.Add(TTD) method, Entity Framework will mark the state of the object as "added", but because you use the same TTD object in the loop and it has been inserted once, EF will try to insert it again using the same primary key (InfoId), resulting in duplicate insertion of the identity column.

You need to create a new TransactionData instance in each loop instead of reusing the same object.

string[] request = { "test1", "test2", "test3" };

using (var IdContext = new ProofContext())
{
foreach (var req in request)
{
// Create a new TransactionData instance each time through the loop
TransactionData TTD = new TransactionData
{
Description = req,
RecordId = 12,
PaymentStatus = "Failed"
};

IdContext.TransactionData.Add(TTD);
IdContext.SaveChanges();
}
}

Each SaveChanges saves the current TTD object to the database, and the InfoId column (identity column) automatically generates a unique value, so identity insert errors do not occur.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.