Let's say I have a database with a table called "invoices".

Then I have a web application where users can manually create invoices against said table. I also have a desktop application that can create invoices programatically. I may have other software modules that can all create invoices.

To manage the operations against the database I'm using an ORM framework (Entity Framework specifically). I want to do this for all the modules.

But, if each module has its own DbContext, the problem I face is that when two modules want to create invoices at the same time, the IDs of the new records will be the same, and then one of the CREATE operations will fail.

To prevent this, my approach is to develop a web API that will act as the only entry point to write new data to the database. The only DbContext I will use for writing data will live inside this API, so colliding IDs will not happen.

Is this solution right? Does this have any possible drawbacks?

  • Another option would be to use Guid as the primary key in order to handle the same primary keys problem. May 29 '19 at 10:11
  • All databases are designed to be transactional - they cannot create identical IDs, unless there is a fault in your approach to generating the IDs. And if IDs are required to be strictly sequential at all times, then it follows logically that your transactions must be strictly sequential (one transaction cannot begin, until the previous has either been committed in full or cancelled). If IDs are not required to be sequential, then each transaction is free to seize a range of IDs concurrently, and if the transaction fails the IDs are abandoned and not further used.
    – Steve
    May 29 '19 at 11:48
  • @BohdanStupak, Guids can be used, but they are less efficient overall unless it is an utmost requirement that the IDs be generated independently. In particular, they are not suitable for human use, whereas invoices often require an ID that can be handled by humans outside the computer system (thus the essential problem of how to generate one has not been solved). Laws also often mandate that business records such as invoices be numbered strictly sequentially.
    – Steve
    May 29 '19 at 11:56
  • @Steve I think that the problem is that the IDs are not generated by the database in this case, but by the DbContext instead, and are kept in memory until the changes are saved: then the transaction is executed. When I try to write a record into the DB and the ID is already taken, I get an Exception. This is the behavior I want to prevent.
    – Pesi442
    May 29 '19 at 15:06
  • @Pesi442, if that be the case, then you will have to stop the DbContext generating the IDs locally, and instead implement a mechanism for allocating them centrally on the database. It may be a case of having to query the database for a range of available IDs, setting these manually onto the client-side objects, and then finally inserting.
    – Steve
    May 29 '19 at 20:43

Options I see:

  • Thank you for the answer. I will look into it. I've been using incremental IDs for so long that I had forgotten about UUIDs, or candidate keys. Tunnel vision, I guess.
    – Pesi442
    May 29 '19 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.