CODEWITHSUNDEEP
c#entity-frameworkidentityoptimistic-locking
aasukisuki
aasukisuki

Reputation: 1243

Auto-increment a number when DatabaseGenerated.Identity has already been used with Entity Framework

We have a situation where we are using Guids for our table keys. Technically we selected GUIDs for their low chance of collision, and this application will some day be distributed, with a nightly data-sync between locations (hence, the need for the low collision chance ID)

However, the business wants a human readable number that can be used on-screen, printed repots, labels, and for use in conversation. For that we're using a 5-digit number, starting with 10000. These numbers can be duplicated across locations (Location A will have a Request 10000, Location B will too).

Our model looks something like this:

Public Class Request
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public int Number { get; set; }
}

Then in our save method, we're currently doing something like this:

newRequestNumber = (_dbContext.Requests.Max(r => r.RequestNumber)) + 1;

request.Number = newRequestNumber

_dbContext.Requests.Add(request);

_dbContext.SaveChanges()

Obviously this is very Naive, and we are now running into issues where that number is duplicated.

As far as I can tell, we can't annotate the Number property with DatabaseGeneratedOptions.Idnetity since it has already been used once. I also can't find anything I could set using the Fluent API to mark this field as an auto-generated number.

My initial thought was to switch to some sort of optimistic locking strategy. We would add a unique constraint to the request number, and then try to save it in a try/catch. if it doesn't save, grab the number again, rinse and repeat. The problem with this is that if the request fails to save for some other reason other than the unique number constraint, we'll be stuck in a loop.

I feel like I'm missing a fairly obvious solution here, any ideas?

Upvotes: 2

Views: 460

Answers (1)

T McKeown
T McKeown

Reputation: 12857

You could create a table with a single column to hold the integer key and another column for a name. You could increment as needed, you could add more rows to represent different types of surrogate keys.

I wouldn't use an identity because that would require you inserting many many rows... just update the column for the given row.

Upvotes: 1

Related Questions