Consecutive number per year with Entity Framework Core

Recently a requirement for a project was that there has to be a registrations table with a column with the following format “2023_0001”. This format contains the year and a consecutive number. Each year, the number has to start with 1 again. In this post I want to present some possible solutions for this problem and weigh up arguments against each other to find a reliable solution.

Requirements

There are already some requirements we know about:
– There must be no concurrency issues
– Each number has to be unique

I have already seen other approaches where especially concurrency issues could not be mitigated in scalable apps. Some were using synchronization inside the application with a semaphore. But this is not scalable. If your application runs on multiple instances or servers the synchronization is useless. Others were using for-loops to try to save a record. Whenever in the meantime another record with the same number was already added, it was trying again for several times until a unique number was set. In the worst case this will still fail or need a lot of iterations. The only realiable way is to work on the database level.

Use a subquery

Of course you can just create a subquery to retrieve a column with the desired format. The premise is that your table contains a column with the date of the record creation.


SELECT Id,
(SELECT TOP 1 CONCAT(YEAR(SubRegistrations.CreateDate), '_', ROW_NUMBER() OVER (ORDER BY SubRegistrations.CreateDate))
FROM Registrations as SubRegistrations
WHERE YEAR(SubRegistrations.CreateDate) = YEAR(Registrations.CreateDate) AND SubRegistrations.CreateDate <= Registrations.CreateDate
ORDER BY SubRegistrations.CreateDateDESC) as RegistrationNumber
FROM Registrations

Pros

No concurrency issues because the value is calculated only when reading data.

Cons

When a record is deleted, the whole numbering will change for the records that are newer than the deleted record.

The query is rather expensive regarding performance.

Functions like ROW_NUMBER() are specific database features. This might not be ideal for Entity Framework.

We have to use a custom sql query.

Use a trigger

Almost every modern database supports triggers. A trigger in Microsoft SQL Server, with a solution to our problem looks like this:


CREATE TRIGGER TriggerGenerateNumberPerYear
    ON  dbo.Registrations 
    AFTER INSERT
 AS 
 BEGIN
     SET NOCOUNT ON;
 
     UPDATE Registrations 
     WITH (TABLOCKX)
     SET RegistrationNumber = CONCAT(
         YEAR(Registrations.CreateDate), 
            (SELECT COUNT(*) FROM Registrations as CountRegistrations WHERE YEAR(CountRegistrations.CreateDate) = YEAR(Registrations.CreateDate)) + 1 - (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY inserted.Id) FROM inserted WHERE inserted.Id <= Registrations.Id ORDER BY inserted.Id DESC)
     )
     FROM Registrations
     WHERE Id IN (SELECT Id FROM inserted)
 END
 GO

This will create a trigger that always executes after an insert is made to the table (Defined by “AFTER INSERT”). The trigger will run in the same transaction as the insert. It will retrieve the count of all registrations (inlcuding the new ones) and subtract the index of the record that has to be inserted. This will also work if you insert multiple records at the same time. It will always make an insert followed by an additional update though. There is also a solution where we can use an “INSTEAD OF INSERT” trigger, so we don’t need to update the records. But for this solution we need to build a dynamic query since we need to get a list of all columns to build the insert statement. Alternatively a temporary table can be used. Both solutions are not ideal and I will not go further into detail here.

Pros

No concurrency issues because everything is one transaction and the table will be locked with TABLOCKX for reading and writing during the update statement.

Good performance since this script will only run after an insert and not on every read.

The numbering is consistent even when records are deleted because the value is stored in a column on the table.

Cons

We need to create a migration or use custom sql to create a trigger.

The trigger is very database specific.

Use a transaction with table locking

The last approach we are looking at, is more controllable from the application:


public async Task CreateRegistrationAsync()
{
    var registration = new RegistrationEntity();

    dbContext.Add(registration);

    using (var scope = await dbContext.Database.BeginTransactionAsync())
    {
        await dbContext.LockTableAsync<RegistrationEntity>();

        registration.RegistrationNumber = await GetNextRegistrationNumberAsync();

        await dbContext.SaveChangesAsync();

        await scope.CommitAsync();
    }
}

private async Task<string> GetNextRegistrationNumberAsync()
{
    int year = DateTime.UtcNow.Year;

    var count = await dbContext.CaseApplications
        .Where(x => x.DateCreated.Year == year)
        .CountAsync();

    return $"{year}_{count + 1:D4}";
}

As you can see everything is done inside a transaction, like you would normally use it with Entity Framework Core. The interesting method here is “LockTableAsync”:


public static class DbContextExtensions
{
    public static async Task<int> LockTableAsync<TEntity>(this DbContext dbContext, CancellationToken cancellationToken = default)
        where TEntity : class
    {
        if (dbContext.Database.IsSqlServer())
        {
            var entityType = dbContext.Model.FindEntityType(typeof(TEntity));
            var tableName = entityType?.GetTableName();

            return await dbContext.Database.ExecuteSqlRawAsync($"SELECT TOP 1 1 FROM {tableName} WITH (TABLOCKX, HOLDLOCK)", cancellationToken);
        }

        // Lock table statements for other database engines can be added here

        return 0;
    }
}

I created an extension method for the DbContext class. Now you can lock any table with this method whenever you need it. In this example it is only implemented for Microsoft SQL Server syntax. We just select any value from the table and lock the table for reading and writing (TABLOCKX) and also hold the lock until the transaction is commited (HOLDLOCK).

Pros

No concurrency issues because everything is one transaction and the table will be locked with TABLOCKX for reading and writing during the insert statement.

Good performance since this script will only run after an insert and not on every read.

The numbering is consistent even when records are deleted because the value is stored in a column on the table.

We can manage the numbering business logic from within our application code.

Cons

If the application shuts down during a lock a table might be locked permanently and has to be unlocked.

Conclusion

All three options have a different approach to solve the problem. In the end we chose the last option where we use a transaction and table locking with Entity Framework Core. We prefer to manage the behaviour of the numbering from our application and not to store the logic in a trigger. Also we can even write unit tests for testing the numbering.