How would I create a concurrency token check on multiple columns in my DB table in EF Core?

Question

I have to charge my users a monthly fee and want to make sure I don't charge them twice.

I created a table called PlatformFee that looks like this to check to see if I've charged them. If there's any entry with the ReceiverId and current month (date as 0624), then I don't charge, otherwise I charge a fee.

Can I set a ConcurrencyToken on both the the ReceiverID and Date so that if another entry tries to insert itself, it will throw a DbUpdateConcurrencyException? How would I do that?

2nd question - would I want a concurrency token or a unique key on both the ReceiverId and Date? Or both?

public class PlatformFee : BaseEntity
{
    public int ReceiverId { get; set; }
    public long Fee { get; set; }
    // Use 4 digit as date Ex. MMYY, 0524
    public int Date { get; set; }
}


var hasPaidMonthlyFee = await _dbContext.PlatforFee.Where(p => p.ReceiverId == id && p.Date == 0624).Any();

if (hasPaidMonthlyFee == null) 
{
    try 
    {
        // insert new record into table
        _dbContext.PlatformFee.Add(platformFee);
        await _dbContext.SaveChangesAsync();
        // pay fee and move on
    }  
    catch(DbUpdateConcurrencyException ex) 
    {
    }
}

Make a unique index in the database. Insert. If it fails - the record was already there (well, check the error code to make sure it is a unique index violation). — mjwills
– mjwills, Commented Jun 15, 2024 at 6:44

Steve Py · Accepted Answer · 2024-06-15 06:03:40Z

A concurrency token is applied to an entire row to tell if a row had been changed between the time a read for initial state and an update statement is run, to guard against concurrent updates by two users or processes.

A unique constraint on ReceiverID and Date would prevent duplicate rows from being inserted/updated to the same values which sounds more in line with what you want to prevent. This would prevent an existing row from being updated to an existing combination or a new row being inserted with an existing combination.

Your code example is a bit incorrect though. Since you are doing an .Any() then your inspection should be:

if (!hasPaidMonthlyFee)

Since the result of your query will be True or False whether a row exists or not respectively.

The unique constraint will guard against another record being inserted by another session/process between the time that:

var hasPaidMonthlyFee = await _dbContext.PlatforFee.Where(p => p.ReceiverId == id && p.Date == date).Any();

... executes and:

await _dbContext.SaveChangesAsync();

... executes. (Which should be quite rare)

Collectives™ on Stack Overflow

How would I create a concurrency token check on multiple columns in my DB table in EF Core?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related