FlushAsync only saving 1 row to database

[Ansiem]

Hi,

I am trying to output multiple rows to the database with the SQL Output binding but each time only one rows gets inserted.
I've got the following code:

    public class StoreMetadata
    {
        [FunctionName("StoreMetadata")]
        public async Task RunAsync(
            [QueueTrigger("extracted-blobs", Connection = "BlobStorage")]string myQueueItem,
            ILogger log,
            [Sql("dbo.Ibans",
            CommandType = System.Data.CommandType.Text,
            ConnectionStringSetting = "SqlConnectionString")] IAsyncCollector<Iban> ibans
)
        {
            log.LogInformation("Store metadata function has been triggered!");

            DetectedMetadataEvent detectedMetadataEvent = null;

            try
            {
                detectedMetadataEvent = JsonSerializer.Deserialize<DetectedMetadataEvent>(myQueueItem);
            }
            catch (Exception ex)
            {
                // TODO: Handle
                log.LogError($"Error while deserializing: {ex.Message}");

            }
 
            if (detectedMetadataEvent != null)
            {
                if (detectedMetadataEvent.PiiEntities != null)
                {
                    foreach ( CustomPiiEntity entity in detectedMetadataEvent.PiiEntities)
                    {
                        if (entity != null)
                        {
                            if (entity.Category.Equals(PiiEntityCategory.InternationalBankingAccountNumber.ToString())){
                              await ibans.AddAsync(new Iban
                                {
                                    AccountNumber = entity.Text,
                                    SubCategory = entity.SubCategory,
                                    DocumentId = 4004,
                                    ConfidenceScore = entity.ConfidenceScore,

                                });
                            }
                        }
                    }
                }
               

                   await ibans.FlushAsync();
            }
            
        }
    }

When debugging I can see that the variable 'ibans' does have multiple objects so I don't understand why only 1 rows gets inserted into the database, maybe because '_rowLock' is 1?

[lucyzhang929]

Hi @Ansiem, can you verify that all rows have a unique primary key?

[Ansiem]

Hi @lucyzhang929, I am not assigning primary keys to the objects within the code since I have a field called Id as auto increment in my database.

[Charles-Gagnon]

@Ansiem I can confirm this is a bug when dealing with identity columns and I'll be working on a fix.

In the meantime you can workaround this by moving the call to FlushAsync() into the for loop so you call it after every item you add.

foreach ( CustomPiiEntity entity in detectedMetadataEvent.PiiEntities)
{
    if (entity != null)
    {
        if (entity.Category.Equals(PiiEntityCategory.InternationalBankingAccountNumber.ToString())){
          await ibans.AddAsync(new Iban
            {
                AccountNumber = entity.Text,
                SubCategory = entity.SubCategory,
                DocumentId = 4004,
                ConfidenceScore = entity.ConfidenceScore,

            });
           await ibans.FlushAsync();
        }
    }
}

[Charles-Gagnon]

@Ansiem I just published a new version of the extension with the fix for this issue, let us know if you're still seeing any issues after updating!

https://github.com/Azure/azure-functions-sql-extension/releases/tag/v0.1.319-preview

[Ansiem]

@Charles-Gagnon issue has been resolved in the update, thank you!