DEV Community

Cover image for Auto-Incrementing Sequences
Karen Payne
Karen Payne

Posted on • Updated on

Auto-Incrementing Sequences

Transaction numbers and invoice numbers that are alpha numeric are commonly used and in this code sample/article a method to produce alpha numeric is shown along with using a computed column.

Code to produce alpha numeric can be used in any type of project while the computed column is specific to SQL-Server.

Method for incrementing

This method accepts a string like AQW-23-10 and return AQW-23-11 which incremented the last series of numbers

public class Helpers
    /// <summary>
    /// Given a string which ends with a number, increment the number by 1
    /// </summary>
    /// <param name="sender">string ending with a number</param>
    /// <returns>string with ending number incremented by 1</returns>
    public static string NextValue(string sender)
        string value = Regex.Match(sender, "[0-9]+$").Value;
        return sender[..^value.Length] + (long.Parse(value) + 1)
            .ToString().PadLeft(value.Length, '0');
Enter fullscreen mode Exit fullscreen mode

The following overload accepts an int value to increment by rather than increment by 1 as in the above method.

public static string NextValue(string sender, int incrementBy)
    string value = Regex.Match(sender, "[0-9]+$").Value;
    return sender[..^value.Length] + (long.Parse(value) + incrementBy)
        .ToString().PadLeft(value.Length, '0');
Enter fullscreen mode Exit fullscreen mode


Real life examples, taking an order at a restaurant where the order number consist of the waiter’s identifier, transaction date/time, a transaction number and an incrementing value.

Perhaps the model might be

public class Transaction
    public string UserIdentifier { get; set; }
    public DateTime DateTime { get; set; }
    public int Id { get; set; }
    public int RegisterNumber { get; set; }

    public override string ToString() => $"{UserIdentifier},{RegisterNumber},{Id}";
Enter fullscreen mode Exit fullscreen mode

By having unique segments like waiter’s identifier there is no chance of issues in the backend database.

The same would be true for other examples like a person calls in an order to a store, the person taking the order has a unique identifier.

EF Core

In this example the following tables are used

Table Description Note
Customers Has customer details
CustomerSequence Has one record per customer to store thier sequence and a prefix to compose the final sequence AA1 first sequence for first customer
🔹 I kept the prefix short but can be whatever you want
Ordrers Customer orders

Here SequencePreFix is a unique string value to represent a specific customer and CurrentSequenceValue is the sequence, combine both together to produce an, in this case a invoice number for an order.

public static bool EntityFrameworkExample2(int customerId)
    using var context = new Context();
    // Get customer to add a new order
    var customer = context.CustomerSequence.FirstOrDefault(x => x.CustomerIdentifier == customerId);
    if (customer is not null)
        var prefix = customer.SequencePreFix;
        var sequenceValue = customer.CurrentSequenceValue;

            * If this is the first order for a customer start the sequence, otherwise increment
            * the sequence
        sequenceValue = string.IsNullOrWhiteSpace(sequenceValue) ? 
            $"{prefix}{Helpers.NextValue("0")}" : 

        // update the sequence
        customer.CurrentSequenceValue = sequenceValue;

        // add a new order
        Orders order = new() { CustomerIdentifier = customer.Id, InvoiceNumber = sequenceValue, OrderDate = DateTime.Now };

        return context.SaveChanges() == 2;

        return false;
Enter fullscreen mode Exit fullscreen mode

Example code to binary file


The model used, a simple invoice which uses protobuf-net 1 NuGet package to store information in a binary file.

public class Invoice
    public int Id { get; set; }
    public string Number { get; set; }
Enter fullscreen mode Exit fullscreen mode

In the Operation.cs class for the first use we mock-up data with various formats to show the NextValue method works on different formats.

public static List<Invoice> Invoices => new()
    new() { Id = 1, Number = "F1124" },
    new() { Id = 2, Number = "1278-120"},
    new() { Id = 3, Number = "3999/IKL/VII/21"},
    new() { Id = 4, Number = "0001"},
    new() { Id = 5, Number = "AA0001"},
    new() { Id = 6, Number = "BB0200"},
    new() { Id = 7, Number = $"BB-{Now.Year}-{Now.Month:D2}-0200"},
    // id, register number, transaction date/time, transaction number
    new() { Id = 8, Number = $"KP,22,{Now:MM/dd/yyyy hh:mm},01"},
Enter fullscreen mode Exit fullscreen mode

Then to increment the value this is done in DataOperations.Save() method.

public static void Save(List<Invoice> list)
    for (int index = 0; index < list.Count; index++)
        var newValue = Helpers.NextValue(list[index].Number);
        list[index].Number = newValue;

Enter fullscreen mode Exit fullscreen mode

In Program class (under the Classes folder), if the binary file does not create then create the file with data from above.

👉uncomment Operations.Reset() to delete the binary file to start over (or simply delete the file in Explorer)

public static void Init()
    // use to reset invoices back to original state

    if (!File.Exists(Operations.FileName))

Enter fullscreen mode Exit fullscreen mode

Increment with a SQL-Server computed column

The following example is a start for incrementing a sequence in table via computed column.

CREATE TABLE dbo.SampleComputed
    Id NUMERIC(18, 0) IDENTITY(1, 1) NOT NULL,
    ComputedIdentifier AS CASE
                              WHEN Id < 9999 THEN
                                  'A' + RIGHT('000' + CONVERT(VARCHAR, Id, 10), 4)
                                  'A' + CONVERT(VARCHAR, Id, 10)
                          END PERSISTED,
    CustomerName NVARCHAR(50) NULL,
    CONSTRAINT PK_SampleComputed
              ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
Enter fullscreen mode Exit fullscreen mode

For more on computed columns see the following repository.


What has been presented has been intentionally kept'd basic so that the reader can use as is or modify to suit their specific needs.

Source code

Clone the following GitHub repository


Using NextValue method with a properly thought out schema for data storage provide you with auto-incrementing alpha numeric values.

If there is a need for performing this with more assertions check out my Microsoft TechNet article Auto-incrementing alphanumeric invoice number with source code working with Entity Framework Core,

  1. protobuf-net is a contract based serializer for .NET code, that happens to write data in the "protocol buffers" serialization format engineered by Google. The API, however, is very different to Google's, and follows typical .NET patterns (it is broadly comparable, in usage, to XmlSerializer, DataContractSerializer, etc). It should work for most .NET languages that write standard types and can use attributes. 

Top comments (4)

webjose profile image
José Pablo Ramírez Vargas

How would you handle concurrency scenarios where you are pressed to generate multiple ID's that use the same sequence? As it is shown here, I see a problem with this: If the sequence is not centrally located, and anyone can give you a number to increment, the code can certainly encounter collisions.

karenpayneoregon profile image
Karen Payne

Great question, as stated at the end of the post I kept it basic which excludes concurrency violations.

When working with EF Core there is the ConcurrencyCheck attribute with the following code decisions can be made

For SqlClient

And we can keep going...

mohammedahmed profile image
Mohammed Ahmed Hussien

Greate article
But for many reasons maybe fall in more than one issue for that reason I implement the twitter's algorithm named SnowflakId in C# you can read see the code from my repo here or you can install it using NuGet package: Hussien.SnowflakId --Version 2.0.0
This library mange all things that related to create a sequence Id and get the datetime from id.

karenpayneoregon profile image
Karen Payne

Your library does not auto-increment alpha-numeric values which is the whole point of this article.