Unique string for each record in the database table

Question

In my Asp.Net MVC 5 project I use Entity Framework code first to work with MS SQL database. Suppose this is the table:

public class Ticket
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string ReferenceCode { get; set; }

    //Rest of the table
}

In this table, whenever I add a new code I want the ReferenceCode column to be a unique and random AlphaNumeric (containing only letters and digits) string with a specific length. This will allow users to refer to a specific ticket for instance.

These are some examples with 10 character lenght: TK254X26W1, W2S564Z111, 1STT135PA5...

Right now, I'm able to generate random strings with the given length. However, I'm not sure how to guarantee their uniqueness. I do it like this:

db.Tickets.Add(new Ticket()
{
   ReferenceCode = GenerateRandomCode(10),
   //...
});

To be exact, I want the GenerateRandomCode function or maybe another method to be able to make sure the generated string has not been used for another record.

I can use a for loop to check each generated code but I don't think it's a good idea. Especially after a while when the table will have thousands of records.

Answer 1

You can use Guid in order to generate unique (but not that random when it comes to security) keys.

Pulling from this SO question:

Guid g = Guid.NewGuid();
string GuidString = Convert.ToBase64String(g.ToByteArray());
GuidString = GuidString.Replace("=","");
GuidString = GuidString.Replace("+","");
GuidString = GuidString.ToUpper();

will generate a unique key to fit your ReferenceCode property needs but longer (22 characters). Collapsing it and using a X characters would no longer guarantee its uniqueness.

OZVV5TPP4U6XJTHACORZEQ

Answer 2

Mind an off-the-beaten-path solution? You've got two needs, that I can see:

• Randomness. You can't have a "deterministic" function, because if someone can guess the algorithm, they could figure out everyone elses' ticket numbers.

• Uniqueness. You can't have any duplicate ticket nums - which makes Random a bit difficult (you'll have to account for collisions and retry.)

But there's no reason you can't do both - you've got plenty of bit-space with 36^10. You could dedicate 4 bytes to Uniqueness, and 6 bytes to Randomness. Here's some sample code:

public partial class Form1 : Form
{

  private static Random random = new Random();
  private static int largeCoprimeNumber = 502277;
  private static int largestPossibleValue = 1679616;  // 36 ^ 4

  private static char[] Base36Alphabet = new char[] { '0','1','2','3','4','5','6','7','8','9',
    'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' };

  public static string GetTicket(int id)
  {
    int adjustedID = id * largeCoprimeNumber % largestPossibleValue;
    string ticket = IntToString(adjustedID);
    while (ticket.Length < 4) ticket = "0" + ticket;
    return ticket + new string(Enumerable.Repeat(Base36Alphabet, 6) .Select(s => s[random.Next(s.Length)]).ToArray());
  }

  private static string IntToString(int value)
  {
    string result = string.Empty;
    int targetBase = Base36Alphabet.Length;

    do
    {
        result = Base36Alphabet[value % targetBase] + result;
        value = value / targetBase;
    }
    while (value > 0);

    return result;
}

Quick rundown on what the code's doing. You're passing in your int id - which it then hashes in such a way that it looks random, but is guaranteed to never repeat a number for the first 1.68 million entries.

It then takes this hashed int value, and turns it into a 4-digit code; this is the "uniqueness part" - you're guaranteed a different 4 digit code at the beginning of the first 1.68 million IDs (the magic of coprime numbers.)

That leaves 6 more characters to play with. Just fill them in with random characters - that makes the whole 10-digit code awfully difficult to guess.

This solves both of your problems. It's guaranteed to be unique for the first million+ records. And it's not really "guessable" by the client, since even if they guessed the algorithm, they'd have 2 billion different possibilities for any given ID they wanted to crack.

Answer 3

Here's my approach that guarantees uniqueness and introduces some randomness.

1. Use a sequence generator that is guaranteed to give a unique number. Since you're working with SQL Server, this can be an IDENTITY column's value. You could alternatively increment an application-level value within your C# code to achieve this.
2. Generate a random integer to bring in some randomness to the result. This could be done with Random.Next() and any seed, even the number generated in the preceding step.
3. Use a method EncodeInt32AsString to convert the integers from the previous two steps into two strings (one is the unique string, one the random string). The method returns a string composed of only the allowed characters specified in the method. The logic of this method is similar to how number conversion between different bases takes place (for example, change the allowed string to only 0-9, or only 0-9A-F to get the decimal/hex representations). Therefore, the result is a "number" composed of the "digits" in allowedList.
4. Concatenate the strings returned. Keep the entire unique string as-is (to guarantee uniqueness) and add as many characters from the random string to pad the total length to the desired length. If required, this concatenation can be fancy, by injecting characters from the random string at random points into the unique string.

By retaining the entire unique string, this ensures uniqueness of the final result. By using a random string, this introduces randomness. Randomness cannot be guaranteed in case the target string's length is very close to the length of the unique string.

In my testing, calling EncodeInt32AsString for Int32.MaxValue returns a unique string 6 characters long:

2147483647: ZIK0ZJ

On that basis, a target string length of 12 will be ideal, though 10 is also reasonable.

The EncodeInt32AsString Method

    /// <summary>
    /// Encodes the 'input' parameter into a string of characters defined by the allowed list (0-9, A-Z) 
    /// </summary>
    /// <param name="input">Integer that is to be encoded as a string</param>
    /// <param name="maxLength">If zero, the string is returned as-is. If non-zero, the string is truncated to this length</param>
    /// <returns></returns>
    static String EncodeInt32AsString(Int32 input, Int32 maxLength = 0)
    {
        // List of characters allowed in the target string 
        Char[] allowedList = new Char[] {
            '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
            'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
            'U', 'V', 'W', 'X', 'Y', 'Z' };
        Int32 allowedSize = allowedList.Length;
        StringBuilder result = new StringBuilder(input.ToString().Length);

        Int32 moduloResult;
        while (input > 0)
        {
            moduloResult = input % allowedSize;
            input /= allowedSize;
            result.Insert(0, allowedList[moduloResult]);
        }

        if (maxLength > result.Length)
        {
            result.Insert(0, new String(allowedList[0], maxLength - result.Length));
        }

        if (maxLength > 0)
            return result.ToString().Substring(0, maxLength);
        else
            return result.ToString();
    }

The GetRandomizedString Method

Now, the preceding method just takes care of encoding a string. In order to achieve the uniqueness and randomness properties, the following logic (or similar) can be used.

In the comments, Kevin pointed out the following risk with the implementation of the EncodeInt32AsString method:

The code needs to be tweaked so that it returns a fixed-length string. Otherwise, you can never be guaranteed of the final result is unique. If it helps, picture one value generating ABCDE (Unique) + F8CV1 (Random)... and then later on, another value generating ABCDEF (Unique) + 8CV1 (Random). Both values are ABCDEF8CV1

This is a very valid point, and this has been addressed in the following GetRandomizedString method, by specifying lengths for the unique and random strings. The EncodeInt32AsString method has also been modified to pad out the return value to a specified length.

    // Returns a string that is the encoded representation of the input number, and a random value 
    static String GetRandomizedString(Int32 input)
    {
        Int32 uniqueLength = 6; // Length of the unique string (based on the input) 
        Int32 randomLength = 4; // Length of the random string (based on the RNG) 
        String uniqueString;
        String randomString;
        StringBuilder resultString = new StringBuilder(uniqueLength + randomLength);

        // This might not be the best way of seeding the RNG, so feel free to replace it with better alternatives. 
        // Here, the seed is based on the ratio of the current time and the input number. The ratio is flipped 
        // around (i.e. it is either M/N or N/M) to ensure an integer is returned. 
        // Casting an expression with Ticks (Long) to Int32 results in truncation, which is fine since this is 
        // only a seed for an RNG 
        Random randomizer = new Random(
                (Int32)(
                    DateTime.Now.Ticks + (DateTime.Now.Ticks > input ? DateTime.Now.Ticks / (input + 1) : input / DateTime.Now.Ticks)
                )
            );

        // Get a random number and encode it as a string, limit its length to 'randomLength' 
        randomString = EncodeInt32AsString(randomizer.Next(1, Int32.MaxValue), randomLength); 
        // Encode the input number and limit its length to 'uniqueLength' 
        uniqueString = EncodeInt32AsString(input, uniqueLength);

        // For debugging/display purposes alone: show the 2 constituent parts 
        resultString.AppendFormat("{0}\t {1}\t ", uniqueString, randomString);

        // Take successive characters from the unique and random strings and 
        // alternate them in the output 
        for (Int32 i = 0; i < Math.Min(uniqueLength, randomLength); i++)
        {
            resultString.AppendFormat("{0}{1}", uniqueString[i], randomString[i]);
        }
        resultString.Append((uniqueLength < randomLength ? randomString : uniqueString).Substring(Math.Min(uniqueLength, randomLength)));

        return resultString.ToString();
    }

Sample Output

Calling the above method for a variety of input values results in:

   Input Int     Unique String  Random String       Combined String 
------------ ----------------- -------------- --------------------- 
         -10            000000           CRJM            0C0R0J0M00
           0            000000           33VT            03030V0T00
           1            000001           DEQK            0D0E0Q0K01
        2147            0001NN           6IU8            060I0U18NN
       21474            000GKI           VNOA            0V0N0OGAKI
      214748            004LP8           REVP            0R0E4VLPP8
     2147483            01A10B           RPUM            0R1PAU1M0B
    21474836            0CSA38           RNL5            0RCNSLA538
   214748364            3JUSWC           EP3U            3EJPU3SUWC
  2147483647            ZIK0ZJ           BM2X            ZBIMK20XZJ
           1            000001           QTAF            0Q0T0A0F01
           2            000002           GTDT            0G0T0D0T02
           3            000003           YMEA            0Y0M0E0A03
           4            000004           P2EK            0P020E0K04
           5            000005           17CT            01070C0T05
           6            000006           WH12            0W0H010206
           7            000007           SHP0            0S0H0P0007
           8            000008           DDNM            0D0D0N0M08
           9            000009           192O            0109020O09
          10            00000A           KOLD            0K0O0L0D0A
          11            00000B           YUIN            0Y0U0I0N0B
          12            00000C           D8IO            0D080I0O0C
          13            00000D           KGB7            0K0G0B070D
          14            00000E           HROI            0H0R0O0I0E
          15            00000F           AGBT            0A0G0B0T0F

As can be seen above, the unique string is predictable for sequential numbers, given it is just the same number represented in a different base. However, the random string brings in some entropy to prevent users from guessing subsequent numbers. Moreover, by interleaving the "digits" of the unique string and random string it becomes slightly more difficult for users to observe any pattern.

In the above example, the length of the unique string is set to 6 (since that allows it to represent Int32.MaxValue), but the length of the random string is set to 4 because the OP wanted a total length of 10 characters.

Answer 4

You can achieve absolute uniqueness on the machine using UuidCreateSequential method(which deals with Uuid) in rpcrt4.dll as below. Check this link from microsoft to be sure about uniqueness. You will never get the same Id twice on your machine or the host where you upload your website.

The output format from following code is what Asp.Net MVC uses to create unique id for AspNetUsers table:

using System;
using System.Runtime.InteropServices;

public class SqlGuidUtil
    {
        [DllImport("rpcrt4.dll", SetLastError = true)]
        static extern int UuidCreateSequential(out Guid guid);

        public static Guid NewSequentialId()
        {
            Guid guid;
            UuidCreateSequential(out guid);
            var s = guid.ToByteArray();
            var t = new byte[16];
            t[3] = s[0];
            t[2] = s[1];
            t[1] = s[2];
            t[0] = s[3];
            t[5] = s[4];
            t[4] = s[5];
            t[7] = s[6];
            t[6] = s[7];
            t[8] = s[8];
            t[9] = s[9];
            t[10] = s[10];
            t[11] = s[11];
            t[12] = s[12];
            t[13] = s[13];
            t[14] = s[14];
            t[15] = s[15];
            return new Guid(t);
        }
    }

Usage:

Guid gid = SqlGuidUtil.NewSequentialId();
String sid = SqlGuidUtil.NewSequentialId().ToString();

Sample output:

637E3E78-23F5-E611-8278-506313F91120

This format is exactly the same as AspNet Identity user Id format.

You can also remove dashes(not a good idea) as below:

String sid = SqlGuidUtil.NewSequentialId().ToString().Replace("-","");

Answer 5

Put a unique index on the DB column, and keep generating until the DB accepts it without a unique constraint. Collisions will be very rare.

Answer 6

We needed to implement something like this for a different purpose in of our previous projects. We just pregenerated a number of unique identifiers into a new table (let's call it table A), and then when we wanted to insert a new record into table B, we just added the top 1 record from table A in a trigger.

Answer 7

user your code like

string referenceCode=Guid.NewGuid().ToString();
referenceCode=referenceCode.Replace('-', '');
db.Tickets.Add(new Ticket()
{
   ReferenceCode = referenceCode;
   //...
});

Answer 8

Try this one. It's work for me

var buffer = new byte[5];
new Random().NextBytes(buffer);
Console.WriteLine(string.Join("", buffer.Select(b => b.ToString("X2"))));

Answer 9

Using your ID for granting uniqueness and the System.Random class to get you a randomness you may expect something like :

private string GenerateRandomCode(int Key)
{
    Random rnd = new Random(Key);
    char[] values = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789".ToArray();

    string result = string.Empty;
    for(int i = 0; i < 10; i++)
    {
         result += values[rnd.Next(0, values.Length)];
    }

    return result;
}

The key value will ensure the same generated code and the random class has not enough period to worry about unicity.

Answer 10

Try this:

Guid.NewGuid().ToString("N").Substring(0, 10)

I used this while generating random strings for ID in SQL tables from C# codebase. This relies on randomness of C# Guid and everytime you will get a new alphanumeric String.

Answer 11

Using Guid I have create a function to generate unique string. Of course GUIDs can collide, so I am altering the string in the middle with new Guid.

     static string GenerateRandomCode(){
        string guid = Guid.NewGuid().ToString("N");     
        List<char> lst = new List<char>();
        int count = 1;

        foreach(char c in guid){                
            if(count==11) break;                
            if(count % 2 ==0){
                lst.Add(Guid.NewGuid().ToString().ToCharArray()[1]);
            }
            else{
                lst.Add(c);
            }
            count++;
        }           
        return string.Join("",lst.ToArray());
    }

Answer 12

Maybe this will help you

DECLARE @userReportId BIGINT

SET @userReportId = FLOOR(RAND()*(10000000000000-1) + 1);