EF Core error "Cannot insert duplicate key row in object 'dbo.Transfers with unique index&-CodePudding

I'm trying to update (MS Docs) multiple rows without knowing the primary key and without fetching each row first as I might have hundreds or thousands of rows to update.

Scenario - I have a webhook that's coming into my controller and it provides me with a list if Stripe transfer ids (ex. "12345", "67890", "111111"). I have a table called Transfer that has this column 'StripeTransferId' but it's not the primary key for the table.

Problem - I need to loop through each entry from the webhook and update each entity in my DB without knowing the primary key and without fetching each row before hand, as there might be thousands of rows to update.

Here is an example of what I need to do

[HttpPost("stripe")]
public async Task<IActionResult> StripeWebhook()
{
     // parse and read data from webhook
     // get a list of stripeTransferIds from the webhook data
     // loop through each id and update corresponding row in db
     foreach(string stripeTransferId in stripeTransferIds) {
         // change the status of the transfer in the DB to complete and update
         var existingTransfer = new Core.Entities.Transfer { TransferStatus = TransferStatus.Complete, StripeTransferId = transferId };
         _context.Attach(existingTransfer);
         _context.Entry(existingTransfer).State = EntityState.Modified;
     }
     
     _context.SaveChangesAsync();
     
}

Doing this I get an error below saying it's trying to insert and not update the row

Message [string]:"Cannot insert duplicate key row in object 'dbo.Transfers' with unique index 'IX_Transfers_StripeTransferId'. The duplicate key value is (tr_1KNMXEH6wPz6LzpKxzl1Q39h).\nThe statement has been terminated."

FYI - If I comment out the line that attaches the entity I get an error saying 0 rows were affected.

CodePudding user response：

I have a table called Transfer that has this column 'StripeTransferId' but it's not the primary key for the table. . . . I need to loop through each entry from the webhook and update each entity in my DB without knowing the primary key and without fetching each row

If StripeTransferId has a unique index then you can treat it as the key. Just configure the entity with that as the Key, and your update using a stub entity will work. EF doesn't care which unique index, unique constraint, or Primary Key constraint you use for the entity key.

Or use a Raw SQL Query to perform the update, which is probably your best bet. Especially since you might be able to send all the (StripeTransferId, State) pairs to the database in one call using a Table-Valued parameter, JSON, XML, or a comma-delimited string (depending on your RDBMS).

CodePudding user response：

I hope that you use await in your original code. Since attaching is not working I would try this

var existingTransfers = await _context.Set<Transfer>().Where(t=> 
 stripeTransferIds.Contains (t.StripeTransferId ).ToListAsync(); 

foreach(var item in existingTransfers)
item.TransferStatus = TransferStatus.Paid;

await _context.SaveChangesAsync();

this way you can put everything in one transaction. If you have a very large quantiy of Ids maybe it makes a sense to user raw sql string or even a stored procedure to increase performance