Developing a project using WebAPI and Entity Framework 6.

There is a controller with a method that adds a new Task entity to the database. At Task before adding the unique name is calculated. Part of the name is created on the basis of already saved Task in the database. If you make two parallel queries, you can generate the same name, this problem is solved through lock.

How to prevent the generation of identical names if two WebAPI servers running on one database are running?

Can I add an algorithm to add a Task to a serial queue at the database level?

Simplified code:

[RoutePrefix("api/tasks")] public class TaskController1 : ApiController { [HttpPost] [Route("add")] public IHttpActionResult AddTask() { using (var db = CreateDbContext()) { Task task = new Task(); lock (lockAdd) { DateTime dateTime = DateTime.UtcNow; string preName = dateTime.ToString("yyMMdd-HHmm-"); var query = db.Tasks.Where(t => t.Name.StartsWith(preName)).Select(t => t.Name); List<string> tasksNames = query.ToList(); task.Name = preName + (tasksNames.Count + 1).ToString("D4"); db.Tasks.Add(task); db.SaveChanges(); } return Ok(); } } }
  • Why not generate this name in the database itself? - tym32167
  • @ tym32167 How to generate it in the database to avoid duplicating the name? - Artem Nikolaevich
  • You have a very strange algorithm. I correctly understand that tasksNames.Count is the number of tasks with names in one date. But then if you add 3 records to the database and then delete the first one, your algorithm will give Count = 2, which will create the name date-3, but the record 3 is already there ... - Mike
  • @Mike The algorithm is simplified, we can assume that the Task is never deleted and such a problem will not arise. - Artem Nikolaevich
  • If you could display the receipt of the quantity in the form select count(1) from (select * from Task where name like 'дата%' for update) X then all records with the specified date would be blocked and another process in the database would wait for your transaction to complete . The truth remains the problem with the first record, when there is nothing more to block ... - Mike

1 answer 1

To generate a unique name within the database, you can use the system stored procedure NEWID () .

To ensure the uniqueness of your algorithm, it is necessary to make the column unique, and the insertion itself should be made as part of the transaction. With this implementation, you might encounter an OptimisticConcurrencyException. How this can be solved is described in an English-language article.

  • Thanks for the answer. This option was considered. The problem is that the column cannot be made unique. Because Names may be the same, but specifically in this method they must be created unique. - Artem Nikolaevich
  • Is it possible to make a restriction on several columns. For example, the restriction will include the column name and timestamp (string representation, up to seconds). In case the name and timestamp match the record will not be inserted. - Liashenko V