How to create a sequential number that does not repeat, per user

Asked

Viewed 905 times

6

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

  • My database Sql-Server may have several emitters.
  • Each issuer can issue their tax bills.
  • Each invoice shall have a sequential and unique number.
  • This sequence is zeroed by emitter.
  • For each issuer the invoice number has to be sequential and unique. But for the database this number can be repeated.

That is to say:

  • Issuer A has banknotes issued from 1 to 100
  • Issuer B has banknotes issued from 1 to 50
  • If there is a new issuer, this sequence of invoice number will start with 1.

I thought I’d use the Sequence of Sql-Server, but with the Sequence I can’t maintain a separate sequence per emitter.

Utilise Entity Framework 6.

Would anyone have any idea how to fix this?

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

    For each banknote issue, do a database search by the number of the last issued banknote, add 1, and re-enter the bank by the seller ID? In the case the search is made by the ID of the seller tb. In the case the field of the numbers of the notes would be a field or table related to the seller.

  • 1

    @Joãocarlos thanks for the tip, I already tried that. The problem is that if there is more than one user issuing notes at the same time to the same issuer, it may happen d0 number duplicate.

  • a custom hash function does not solve? you create a function that generates a 32-character token or more? or better, you can create serial numbers and divide them, you follow a sequence of 10 digits ending in a 32-character hash

  • 2

    if I am not mistaken, you have the option of ComputedColumn, should suit you. Note that in order to issue NF, you also have the Series field. Logo, issuer 1, you can have grade 1 in series 1, and grade 1 in series 2.

  • @Hebertdelima the number needs to be sequence starting with 1, then the next note would be number 2, so on. Ie I can’t hash.

  • @Rovannlinhalis thanks for the tip, I will search. If you have any examples for me. Please put as solution. Thank you!

  • 2

    Why not lock the table while incrementing the value?

  • @Rovannlinhalis did the research with Computedcolum, but he can only compute using columns of the table itself, example we would have a column computed Fullname that would be Firstname+Lastname

  • 2

    In my opinion and in my experience I believe that the best solution, and common in the market, is to follow @Joãocarlos' suggestion to consutlar and add, along with a block mentioned by Lazyfox

  • @Mfedatto with this solution, if there are two requests at the same time, a queue will be created, so the next request waits for the first unlocking the table, or I should do this control in hand?

  • You can create a Quence for each emitter if limited to emitter options

  • You must block the table from the time of the query to the time of insertion (otherwise you can introduce inconsistency in the bank), then you must study the case for your problem, because if the time between the query and the insertion is large can give a bottleneck in your application and compromise the performance of it. I can’t tell you for sure if this is the best way out but it’s a viable way out.

  • @Rodrigok. B if you create a transaction in the bank with lock in the table the bank manages the queue itself and becomes transparent.

  • @Rodrigok.B. A simple solution (if I understand your problem) is: You will only read the ID of the last note registered in the bank, exactly at the time of doing the Insert. The system does not need to be aware of this ID before the user clicks the Register/Send/Generate... Only when he clicks on the button, you will query to verify the ID of the last registered note. If you don’t want to keep making unnecessary queries in the database, you can save this id in a document, as if it were a log. This will give you performance in the system, or create a counter table.

Show 9 more comments

2 answers

5

According to the documentation:

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

and,

You can also specify an 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 it, and I don’t know if the command really could be like that, it’s a hypothesis, but I believe it already gives a north for your problem.

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

  • Rovann, I’ll take the test and tell you how it was.

  • 1

    Rovann, I use EF6, that property .HasDefaultValueSql does not exist. Reading saw that it is for Efcore. EF6 has another way to configure but does not accept Sql. But in terms of isolation, it solves the issue of duplicity. But I’m concerned about performance. I’m going to run some tests and tell you.

  • I did the test to find the number by sql, as directed, isolating the transaction. Actually there was no duplication , however it was not created queue, ie the second request was rejected. At least I avoid the duplicity problem that occurred before.

  • Which error gave in the second request, which was rejected ?

  • 1

    Ravann, I ended up undoing the update, but basically the error reported that the table was blocked by a transaction. And I also realized that the whole process has slowed down using IsolationLevel.Serializable. I think I will do as Marcelo said. Creating a sequeence by emitter. But I will still do some research to try to solve this in the application layer and not by the database.

1

Browser other questions tagged c# sql-server entity-framework entity-framework-6

You are not signed in. Login or sign up in order to post.