I have an IotHub trigger which basically saves an incoming id to the database using Entity Framework if such an id does not exist.

[FunctionName("MainFunc")]
public static async Task Run(
    [IoTHubTrigger("messages/events",
                   Connection = "IotHubCompatibleEndpointConnectionString",
                   ConsumerGroup = "ttx_iothub_trigger_sqldb_cg")]
    EventData eventData, ILogger log)
{
    string id = GetIdFromMessage(eventData);
    var context = new MyEfDbContext();
    InsertIfNotExists(id);
    DoSomethingElse(context);
    context.SaveChanges();
}

The problem is that when there are a lot of messages sent to the iot hub, multiple trigger calls start working in parallel (at least when I debug the trigger) which causes the issue for InsertIfNotExists() method leading to duplicate key exception when more than 1 record with the same id, not existing in the database, is being processed.

What is the most appropriate way to fix it? Just swallow the exception because the record anyway will appear in the database?

1 Answers

1
Volodymyr Bilyachat On Best Solutions

You did not provide much code what you are doing in InsertIfNotExists but as I see your problem is that you have context.SaveChanges(); in the end which means that firstly you modify in memory then you save, which means that at same time you can have multiple instances doing the same. Here is most important to insert quickly to database but even that wont guarantee one insert at a time.

So i see few options here

Option 1. You can also can have try catch, and on error just do second trip to get by id since you know that record is inserted.

Option 2. Transact sql has merge statement which actually do upsert. If you are using entity framework core (You did not provide if its core or not but I assume its core) you can use extension

DataContext.DailyVisits
    .Upsert(new DailyVisit
    {
        UserID = userID,
        Date = DateTime.UtcNow.Date,
        Visits = 1,
    })
    .On(v => new { v.UserID, v.Date })
    .WhenMatched(v => new DailyVisit
    {
        Visits = v.Visits + 1,
    })
    .RunAsync();

Option 3. Would be to use transactions.