How to create a non-repeating sequential number, per user

Question

How to create a non-repeating sequential number, per user

Work on a bill issuance project, in this project there is the entity Emissor.

my database Sql-Server can have multiple issuers.
each issuer may issue its invoices.
each invoice must have a sequential and unique number.
this sequence is zeroed by emitter.
for each Issuer the invoice number must be sequential and unique. But for the database this number can repeat.

I.e.:

Issuer A has notes issued from 1 to 100
issuer B has notes issued from 1 to 50
if there is a new issuer, this invoice number sequence starts with 1.

I thought I'd use Sequence from Sql-Server, but with Sequence I can't keep a sequence separated by emitter.

I Use Entity Framework 6 .

Would anyone have any idea how to solve this?

6

c#sql-server entity-framework entity-framework-6

Author: perozzo, 2018-03-14

Source

2 answers

score 5 · Answer 1

Second the documentation:

The default value of a column is the value that will be entered if a new row is entered, but no value is specified for the column.

E,

You can also specify a SQL fragment that is used to calculate the default value

Example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Emissor>()
        .Property(b => b.Numero)
        .HasDefaultValueSql("(select coalesce(max(numero),0)+1 from notas_fiscais where serie = [Serie] and emissor_id = [EmissorId])");

}

I have no way to test, and I do not know if really the command can be like this, it is a hypothesis, but I believe that already give a north to your problem.

On the possibility of doubling the number, it would be very difficult in this way, but not impossible. If you set the IsolationLevel of the transaction to Serializable the bank can handle this, and only allow a second insert when the first is completed.

score 1 · Answer 2

To control this from the database side, you can create an after insert trigger that calculates this number.

To ensure that there is no repeated number in the same table you can create a unique index(https://docs.microsoft.com/pt-br/sql/relational-databases/indexes/create-unique-indexes?view=sql-server-2017) between the Issuer Id and the sequential number.

But stay tuned to the question of competition in access!