Optimizing a EF Core "upsert" operation

Question

I'm trying to "optimize" the command below. In other words, to make it suit my needs using best practices. I found a lot of posts regarding the "upsert" operation and there were a lot of solutions and in the end, I'm not sure which one is best.

The term upsert is a combination of the words “update” and “insert.” In the context of relational databases, an upsert is a database operation that will update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn't already exist.

There are only two tables (the database structure can be find below), and there are different event types for which we have these three booleans. Administrators can change these global notification settings on demand. If the record for a specific event type id doesn't exist, it should load it at runtime. Since these settings can be updated concurrently by many administrators at the same time, I think I should handle DbUpdateConcurrencyException https://github.com/petercwq/EFCorePractice/blob/master/EFCorePractice/DbContextConcurrencyExtension.cs. What do you think?

I'm not so good at EF Core, so I would like to know your opinion. I also had a look at https://stackoverflow.com/questions/5557829/update-row-if-it-exists-else-insert-logic-with-entity-framework and I feel like instead of using a stored procedure or an atomic transaction, I could use Update which cuts off the round trips to the database? I mean I wouldn't need to do .FindAsync and then insert/update.

This is something that should be done at the database engine level, in a stored procedure. Otherwise, you'll have to wrap the detect/update/insert in a transaction.

public sealed class UpsertGlobalNotificationSettingsCommand : IRequest
{
    public Guid EventTypeId { get; set; }
    public bool InternalNotificationsEnabled { get; set; }
    public bool EmailNotificationsEnabled { get; set; }
    public bool SmsNotificationsEnabled { get; set; }
}

public sealed class UpsertGlobalNotificationSettingsCommandHandler : IRequestHandler<UpsertGlobalNotificationSettingsCommand>
{
    private readonly AppDbContext _dbContext;

    public UpsertGlobalNotificationSettingsCommandHandler(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task Handle(UpsertGlobalNotificationSettingsCommand request, CancellationToken cancellationToken)
    {
        var existingSettings = await _dbContext.GlobalNotificationSettings.FindAsync(new object[] { request.EventTypeId }, cancellationToken);

        if (existingSettings == null)
        {
            var entity = new GlobalNotificationSettings
            {
                EventTypeId = request.EventTypeId,
                InternalNotificationsEnabled = request.InternalNotificationsEnabled,
                EmailNotificationsEnabled = request.EmailNotificationsEnabled,
                SmsNotificationsEnabled = request.SmsNotificationsEnabled
            };
            
            _dbContext.GlobalNotificationSettings.Add(entity);
        }
        else
        {
            existingSettings.InternalNotificationsEnabled = request.InternalNotificationsEnabled;
            existingSettings.EmailNotificationsEnabled = request.EmailNotificationsEnabled;
            existingSettings.SmsNotificationsEnabled = request.SmsNotificationsEnabled;
        }

        await _dbContext.SaveChangesAsync(cancellationToken);
    }
}

Database

public class EventType
{
    public Guid Id { get; set; }
    public string Name { get; set; } = default!;

    public ICollection<GlobalNotificationSettings> GlobalNotificationSettings { get; set; } = default!;
}

public class GlobalNotificationSettings
{
    public Guid EventTypeId { get; set; }
    public bool InternalNotificationsEnabled { get; set; }
    public bool EmailNotificationsEnabled { get; set; }
    public bool SmsNotificationsEnabled { get; set; }
    
    public EventType EventType { get; set; } = default!;
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    public DbSet<EventType> EventTypes => Set<EventType>();
    public DbSet<GlobalNotificationSettings> GlobalNotificationSettings => Set<GlobalNotificationSettings>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GlobalNotificationSettings>(entity =>
        {
            entity.HasKey(gns => gns.EventTypeId);

            entity.HasOne(et => et.EventType)
                .WithMany(gns => gns.GlobalNotificationSettings)
                .HasForeignKey(gns => gns.EventTypeId)
                .OnDelete(DeleteBehavior.Cascade);
        });
    }
}
```