Saving data in a loop using Entity Framework Core

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 an identity column as its primary key. 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 an error:

Cannot insert explicit value for identity column in table TransactionData when Identity Insert is set off

Here's a screenshot for illustration:

I tried running this statement on SQL side in order to fix this error

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

In an ORM, an object is roughly akin to a row. If you keep giving the ORM the same object instance via Add, it will get confused as to whether you're trying to update that row vs add a new row (or similar, depending on the exact scenario).

If you intend this to be a new row/relationship: use a new object each time - and if you're adding multiple rows, let the ORM add them as a batch rather than row-by-row.

If you don't want ORM semantics: ditch the ORM for this specific job, and use a more direct tool such as Dapper (which simply executed SQL based on the object you pass in to represent parameters/arguments); since it won't be holding an object graph, it won't care if you use the same object with different values on successive calls.