Violation of PRIMARY KEY constraint when published on Azure

Question

ASP yields a Violation of PRIMARY KEY constraint on a specific ID. (I randomly generate the ID's in code, while checking they don't already exist in the database).

When I debug this code on my local machine (with the same Azure hosted database) everything works fine. When I publish my code on Azure I get the error. Strange thing is that the random ID, yielded by the error, does NOT exist in my database.

Code (I copy a list of medication with a new id & new schemeID):

        List<medication> l = db.medication.AsNoTracking().Where(x => x.scheme_ID == schemeID).ToList();
            foreach (medication m in l) {
                int id = generateUniqueMedicationID();
                m.ID = id;
                m.scheme_ID = newSchemeID;
                db.medication.Add(m);
            }
        db.SaveChanges();

generateUniqueMedicationID() method:

    private int generateUniqueMedicationID()
    {
        bool stop = true;
        int id = -1;
        while (stop)
        {
            Random r = new Random();
            id = r.Next(100000000, 1000000000);
            if (!db.medication.Any(o => o.ID == id)) stop = false;
        }
        return id;
    }

What does `generateUniqueMedicationID` look like? Why aren't you following standard C# naming conventions? — mason, Feb 17 '17 at 16:35
Set your table `Primary Key` to `Identity(1,1)` so that it will auto incremented every time when a row is added in the table. — Saurabh Srivastava, Feb 17 '17 at 16:40
@SaurabhSrivastava the ID can't be auto incremented because of security matters. — ArneDEPR, Feb 17 '17 at 16:51
You have the same issue as this question: [Random number generator always picks the same value when run inside a loop](http://stackoverflow.com/questions/5398336/random-number-generator-always-picks-the-same-value-when-run-inside-a-loop). And [this one](http://stackoverflow.com/questions/5264412/why-does-random-next-always-return-the-same-number). And [this one](http://stackoverflow.com/questions/5264412/why-does-random-next-always-return-the-same-number). And [this one](http://stackoverflow.com/questions/767999/random-number-generator-only-generating-one-random-number). — mason, Feb 17 '17 at 16:55
When the database said you had primary key violations, didn't you think to check that the ID's you're generating actually end up being unique? — mason, Feb 17 '17 at 16:57
@mason that was the problem, thank you very much! I did not think of that, because it was working fine on my local machine. Does Azure handle the random function differently? — ArneDEPR, Feb 17 '17 at 18:09
Nope, nothing to do with Azure. Just has to do with timing and how Random gets its seed. — mason, Feb 17 '17 at 18:46

score -1 · Accepted Answer · answered Feb 17 '17 at 18:07

The C# 'Random' method, isn't a true random number generator. As Microsoft puts it, it is a "pseudo-random number generator".

This MSDN article[https://msdn.microsoft.com/en-us/library/system.random(v=vs.110).aspx] says:

Pseudo-random numbers are chosen with equal probability from a finite set of numbers. The chosen numbers are not completely random because a mathematical algorithm is used to select them, .....

To generate a cryptographically secure random number, such as one that's suitable for creating a random password, use the RNGCryptoServiceProvider class or derive a class from System.Security.Cryptography.RandomNumberGenerator.

In simple words, the numbers get repeated after a certain interval.

The best way to have non-sequential ID values, if you are using SQL server 2012 (or higher), is to change your ID column and use the uniqueidentifier type, which generates GUID and you should let your DB engine to create and assign the value, not your C# code.

This is by far the best and elegant solution. For future reference My problem could also be solved by: 1) initializing the random outside the loop - or - 2) call saveChanges() inside loop (not efficiënt at all) — ArneDEPR, Feb 17 '17 at 19:17

Violation of PRIMARY KEY constraint when published on Azure

1 Answers1