How to create a non-repeating sequential number per user

Question

How to create a non-repeating sequential number per user

Navigation

#1 by (5 votes)
#2 by (1 votes)

6

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

My database Sql-Server may have multiple emitters.
Each issuer can issue its invoices.
Each invoice must have a unique, sequential number.
This sequence is zeroed by sender.
For each issuer the invoice number has to be sequential and unique. However for the database this number may be repeated.

That is:

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 will start with 1.

I thought about using the Sequence of Sql-Server , however with Sequence I can not keep a sequence separated by sender.

I use Entity Framework 6 .

Would anyone have any idea how to solve this?

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

asked by anonymous 14.03.2018 / 19:16

2 answers

What happens if a binary search does not find the element? How do I disable Google Chrome Pop-Up to save passwords?

score 5 · Answer 1

Second Documentation :

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

e,

You can also specify an SQL snippet 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 do not know how to test, and I do not know if the command really can be like this, it's a hypothesis, but I think I'll give it a try.

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

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 you do not have a repeating number in the same table you can create a unique index ( link ) between the sender's Id and the sequential number.

But stay on top of the competition issue!