0

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?

Reza Owliaei
  • 3,293
  • 7
  • 35
  • 55
  • Its not based on your Id field and would require changing to BigInteger (.net 4+) for the code field, but have you considered just using a GUID (converted to a BigInteger) ? – Matt Caton Jul 15 '14 at 09:57
  • @LongboatHarry I should generate a human readable code. GUID is not very handy for users to use. – Reza Owliaei Jul 15 '14 at 09:59
  • How about normalise the code off into a separate table (along with anything else that is related) and make it an identity field? E.g. Documents 1-1 DocumentCodes? – Matt Caton Jul 15 '14 at 10:22
  • Instead of that, I can use the Id itself then. As you note that, I have to use long and unique. Id is unique, that's right, however I require something different like this: `(DateTime.Now.Year*10000000)+Id` – Reza Owliaei Jul 15 '14 at 10:30
  • Ive put an idea into an answer. Perhaps identity field with your (DateTime..) as a seed value would be a nice and simple solution. – Matt Caton Jul 15 '14 at 10:33

2 Answers2

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
Matt Caton
  • 3,473
  • 23
  • 25
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.

Matt Caton
  • 3,473
  • 23
  • 25
  • Can I use Id of the entity in computation formula before entity insertion? If yes, this is the answer. – Reza Owliaei Jul 15 '14 at 12:15
  • ah - if you want to create the code before the entity then I think a combination of both answers would be best. I've updated the other answer accordingly. – Matt Caton Jul 15 '14 at 12:45