C#•2y ago

❔ EF Core "upsert" - insert if it doesn't exist, update if it does

I'm trying to "optimize" the command below.

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.

Stack Overflow

Update Row if it Exists Else Insert Logic with Entity Framework

What is the most efficient way to implement update row if it exists, else insert new row logic using Entity Framework? Or are there any patterns for this?

2 Replies

HulkstanceOP•2y ago

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);
    }
}

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);
    }
}

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);
        });
    }
}

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);
        });
    }
}

Accord•2y ago

Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF Core "upsert" - insert if it doesn't exist, update if it does