EF6: How to generate a unique number automatically

Question

Consider an entity like this:

 public class Document
 {

    public int Id { get; set; }

    public long code { get; set; }

    // Rest of Props

 }

What I need is to generate a unique long code, and I prefer to generate it based on Id.

One simple classic but unsafe solution is to get the last Id and increase it by one and use, but I'm looking for a better solution, such as computed column, or any other way to assign code generation to database.

• I cannot access to Id if I try define it as a compute column.
• Marking it as Identity is not possible, because Id is already an Identity column.
• Creating a method to Get the last Id is not safe and clean.

By now I'm trying to do former classic solution with the help of transactions to make it safe.

Any better suggestion?

Answer 1

Given the scope of the question and without knowing the entire context, the very simplest solution would be to have the code field as a computed property.

I.e. calculate the code field in the application by concatenating a seed value (based off date) with the Id field.

To search for Document based on code you would simply split the Id from the given code and use it to search on the Documents table.

Answer 2

A workaround to the limitation of a single identity field per table would simply be to normalise the Documents table into a further table containing the code field and any related fields.

In order to get the base value (based on date) for the code identity field could simply be seeded with the lowest desired value. Don't know the underlying RDMS, but for MySQL it would be along the lines of http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

So, something like

Documents 1-1 DocumentCodes

where:

DocumentCodes
-------------
code : identity (seeded to desired base value)
documentId : int FK UNIQUE 

A further alternative to allow the code field to be generated separately (including before) the actual Document entity would be as follows:

DocumentCodes
-------------
code : identity PK

Documents
---------
id : identity
code : id FK 

The actual code field could be seeded to the required base number, or used as part of a computed property as per my other answer.

1. Create an entity in the DocumentCodes table and get the generated code
2. Create a computed code for the end user based on the generated code and a number generated from the date
3. Create the Document entity, passing in the generated code

To search for a Document based on code:

1. Strip the date part off the computed code
2. Search in the Document table on code