DbContext not update when multiple requests are sent in a short time

Question

Currently I'm developing a web system for my company using angularjs and web api 2.

In the system, we allow users to add account into database by using an api. This is its flow:

• Call api/user to add user into database.

• Add user information with specific information:

  • Id (auto generated by DB)
  • RepresentationCode (calculated by concatenating USER_REPRESENTATION_{MaxUserIndex}

Let me give you an example, let say the database is empty.

• Call api/user 20 times concurrently.

• These are the records that are created into database.

1 USER_REPRESENTATION_1 2018-03-17 01:51:02.140 NULL

2 USER_REPRESENTATION_1 2018-03-17 01:51:02.140 NULL

3 USER_REPRESENTATION_3 2018-03-17 01:51:02.417 NULL

4 USER_REPRESENTATION_3 2018-03-17 01:51:02.420 NULL

5 USER_REPRESENTATION_4 2018-03-17 01:51:02.427 NULL

6 USER_REPRESENTATION_6 2018-03-17 01:51:02.437 NULL

7 USER_REPRESENTATION_6 2018-03-17 01:51:02.437 NULL

8 USER_REPRESENTATION_8 2018-03-17 01:51:02.443 NULL

User representation codes are duplicated with some records.

• My expectation is:

1 USER_REPRESENTATION_1 2018-03-17 01:51:02.140 NULL

2 USER_REPRESENTATION_2 2018-03-17 01:51:02.140 NULL

3 USER_REPRESENTATION_3 2018-03-17 01:51:02.417 NULL

4 USER_REPRESENTATION_4 2018-03-17 01:51:02.420 NULL

5 USER_REPRESENTATION_5 2018-03-17 01:51:02.427 NULL

6 USER_REPRESENTATION_6 2018-03-17 01:51:02.437 NULL

7 USER_REPRESENTATION_7 2018-03-17 01:51:02.437 NULL

8 USER_REPRESENTATION_8 2018-03-17 01:51:02.443 NULL

This is the code for registering DI :

        // Initiate container builder to register dependency injection.
        var containerBuilder = new ContainerBuilder();

        #region Controllers & hubs

        // Controllers & hubs
        containerBuilder.RegisterApiControllers(typeof(Startup).Assembly);
        containerBuilder.RegisterWebApiFilterProvider(httpConfiguration);

        #endregion

        #region Unit of work & Database context

        // Database context initialization.
        containerBuilder.RegisterType<RelationalDbContext>().As<DbContext>().InstancePerLifetimeScope();

        // Unit of work registration.
        containerBuilder.RegisterType<UnitOfWork>().As<IUnitOfWork>().InstancePerLifetimeScope();

        #endregion

        #region IoC build

        // Container build.
        containerBuilder.RegisterWebApiFilterProvider(httpConfiguration);
        var container = containerBuilder.Build();

        // Attach DI resolver.
        httpConfiguration.DependencyResolver = new AutofacWebApiDependencyResolver(container);

This is my ApiUserController

    public IHttpActionResult AddUser()
    {
        // Get users list.
        var users = _unitOfWork.Users.Get();

        // Get max user id.
        var iMaxUserIndex = users.Max(x => (int?)x.Id) ?? 0;
        iMaxUserIndex++;

        var user = new User();
        user.RepresentationCode = $"USER_REPRESENTATION_{iMaxUserIndex}";
        user.CreatedTime = DateTime.Now;

        _unitOfWork.Users.Add(user);
        _unitOfWork.Commit();

        return Ok();
    }

After searching for solution, this is the temporary solution I'm using. I use trigger to update user representation code when record is created.

Below is my trigger:

IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'addUserRepresentationCode')
DROP TRIGGER addUserRepresentationCode
GO
CREATE TRIGGER addUserRepresentationCode ON [dbo].[Users] 
FOR INSERT, UPDATE
AS
BEGIN
 DECLARE @userId  INT;
 SELECT @userId = Id FROM INSERTED
 UPDATE [dbo].[Users] 
 SET  RepresentationCode = 'USER_REPRESENTATION_' + CAST(@userId as varchar(10))
    WHERE Id = @userId
END

My trigger works fine, meet my requirement.

But I still have one question:

• Can I add user representation code with pure Entity Framework without any helps from trigger ?

Thank you,

Answer 1

Your code does not work because multiple consecutive requests may end up getting the same iMaxUserIndex value.

For example, request A hits your controller and executes this line:

var iMaxUserIndex = users.Max(x => (int?)x.Id) ?? 0;

Then, request B hits your controller and executes the same line above before the thread for request A could add the entity and save the database. The result is that both requests have the same iMaxUserIndex value.

The easiest options to set RepresentationCode variable is either make it a computed column in your database, or if you don't need that column in the database, just make it a read only property in your domain model.

public string RepresentationCode { get; } = $"USER_REPRESENTATION_{Id}";