In Entity Framework, how can I get a conditional sequence value?

Question

I have entities in my application that require a unique human friendly identifier that include a sequence integer unique to a subset. For example, in project management software you might have a project called "SUPPORT" and then cases related to this project are automatically sequentially numbered as "SUPPORT-1", "SUPPORT-2", etc.

Q: How can I get a unique identifier that is conditional to other properties on the entity instance?

---

I have (temporarily) implemented this in my application by overriding the DbContext.SaveChanges() method, check the latest value in the database for cases related to the project and then create a string with the project name and the sequence number. This doesn't seem robust and I am looking for a proper pattern.

Researching patterns I have found the modelBuilder.HasSequence<int>() method in the EF Core documentation but I don't see how I can implement this such that is conditional on the value of the project (so a unique sequence for cases in "SUPPORT", not overlapping with cases in "SOFTWARE" project).

I am currently using Entity Framework Core 3.1.

Answer 1

You can just store the latest case number as a field in your project record and combine them as you are creating a new instance of case. This would be trading the cleanliness of the model and a bit of space for efficiency. Be wary of concurrency.

You can also add the collection of child cases to your project entity and have EF populate it for you, so you can grab the count/latest child and adjust the number on your new entity. This preserves your model and space, but does cost more time and bandwidth. Be wary of concurrency.

Finally, you can just write a trigger for your database that does what you do in SaveChanges() but directly in the database. This would be more efficient than your current approach, and a bit more robust, but it will take a bit longer to insert a new case into your database. Your RDBMS should handle concurrency for you.