The changes to the database were committed successfully, but an error occurred while updating the object contextRSS

[raysefo]

Hi,

I have a asp.net web API. I am using EF 6 code first approach. I am getting this error only when I load testing my web API with Jmeter. How can I fix this?

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: Saving or accepting changes failed because more than one entity of type 'BusinessEntity.GameResponse' have the same primary key value. Ensure that explicitly set primary key values are unique. Ensure that database-generated primary keys are configured correctly in the database and in the Entity Framework model. Use the Entity Designer for Database First/Model First configuration. Use the 'HasDatabaseGeneratedOption" fluent API or 'DatabaseGeneratedAttribute' for Code First configuration

Here is my GameResponse:

using System;

namespace BusinessEntity
{
    public class GameResponse
    {
        public int Id { get; set; }
        public string referenceId { get; set; }
        public string productCode { get; set; }
        public int quantity { get; set; }
        public string version { get; set; }
        public string signature { get; set; }
        public string ApplicationCode { get; set; }
        public string validatedToken { get; set; }
        public DateTime? responseDateTime { get; set; } = DateTime.Now;
        public string initiationResultCode { get; set; }
        public string companyToken { get; set; }
        public string estimateUnitPrice { get; set; }
        public string currency { get; set; }
    }
}

Here is the method I am load testing:

private async Task<HttpResponseMessage> CallRazerService(RequestDto requestDto)
        {
            HttpResponseMessage response = null;

            using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
            {
                try
                {
                    //Transform DTO into GameRequest for calling Razer Initiate
                    var config = new MapperConfiguration(cfg =>
                    {
                        cfg.CreateMap<RequestDto, GameRequest>();
                        cfg.CreateMap<GameRequest, GameConfirmRequest>();
                        cfg.CreateMap<GameConfirmResponse, GameConfirmResponseDto>();
                        cfg.CreateMap<Coupon, CouponDto>();
                    });
                    var iMapper = config.CreateMapper();
                    var gameRequest = iMapper.Map<RequestDto, GameRequest>(requestDto);
                    //Unique reference ID
                    gameRequest.referenceId = Guid.NewGuid().ToString();

                    //Create signature
                    gameRequest = Utilities.CreateSignature(gameRequest, RequestType.Initiate);

                    //Add initiation request into database
                    _unitOfWork.GameRepository.Insert(gameRequest);
                    

                    #region Call Razer initiate/confirm

                    //Call Razer for initiation
                    response = await Utilities.CallRazer(gameRequest, "purchaseinitiation");

                    //Read response
                    var htmlResponse = await response.Content.ReadAsStringAsync();
                    var gameResponse = JsonConvert.DeserializeObject<GameResponse>(htmlResponse);

                    //Adding initiation response into database
                    _unitOfWork.GameResponseRepository.Insert(gameResponse);
                    

                    if (gameResponse.initiationResultCode == "00")
                    {
                        gameRequest.validatedToken = gameResponse.validatedToken;
                        //Create signature
                        var gameConfirmRequest = Utilities.CreateSignature(gameRequest, RequestType.Confirm);

                        //Transform DTO into GameRequest for calling Razer Initiate
                        var gameConfirmRequests = iMapper.Map<GameRequest, GameConfirmRequest>(gameConfirmRequest);

                        //Add confirm request into database
                        _unitOfWork.GameConfirmRequestRepository.Insert(gameConfirmRequests);
                        

                        //Call Razer for confirm
                        response = await Utilities.CallRazer(gameRequest, "purchaseconfirmation");

                        //Read response
                        htmlResponse = await response.Content.ReadAsStringAsync();
                        var gameConfirmResponse = JsonConvert.DeserializeObject<GameConfirmResponse>(htmlResponse);

                        //Add confirm response into database
                        _unitOfWork.GameConfirmResponseRepository.Insert(gameConfirmResponse);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                #endregion

                await _unitOfWork.SaveAsync();
                scope.Complete();
            }

            return response;
        }

 

[Skydiver]

The warning that you got back hints at issues with primary key values. Is Id or referenceId? How are you generating those Id values? Are you letting the database generate the primary key value for you, or are you generating it yourself. If you are generating it yourself, how are you ensuring uniqueness? (I see that you are using GUIDs for referenceId.)

 

[raysefo]

for Id's database generate them and for GUID's I am generating them as follows:

//Unique reference ID
                gameRequest.referenceId = Guid.NewGuid().ToString();

I think referenceId generation causes this problem. How can I make it unique? SQL can create it for me?

 

[NoUserHere]

The problem seems to be that you are not using any ID management like auto increment (AI) in your database table. If you did, you wouldn't need to insert or update the id field in which is auto incremented.

That's what makes auto incremented values unique, is that they are normally not ever required to be changed once inserted along with the data for that row. They become the primary key for the row that holds the data. A table structure can have a primary key without AI, but you risk the nuisance of having to manage what the next ID should be on the next row insert statement, especially when the database is more than happy to do it for you.

I'll take a stab in the dark and assume your Id will be inserted into your database and is also not a primary key or auto incrementing?
And referenceId is assigned a GUID which should be a unique field, is it?
If they're not, I would rethink your logic in regards to this table structure. You should have an ID field which is AI and primary key as well as making your GUID field unique.

Can you post your table structure?

 

[Skydiver]

As an aside, the OP keeps on indicating problems during stress testing. I wonder if the errors also occur during normal integration tests. If it only appears during stress testing, I wonder if one of the following is true:
1. They are starting with a non-empty database. So either there are already pre-existing duplicate rows, or any new primary key values end up duplicating existing rows.
2. They have a concurrency issue where identical IDs are being generated.

 

[raysefo]

I changed a couple of things. First of all, I was setting referenceId which is a unique GUID manually. Then adding an entity with this referenceId. After that, I am using this same referenceId in another entity to add into the database.

Now SQL server generates referenceId for me as follows:

public class GameRequest
    {
        public int Id { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid referenceId { get; set; }
...

Here is my code now. But the problem is how can I get the generated refenerceId when I insert it into the database. AFAIK, after SaveAsync insertion is made. Somehow I need to use this SQL generated referenceId in order to use it calling Razer for confirming. (see gameRequest in the code below)

Forgot to mention that, I am getting this error when I changed the referenceId to GUID in the database.

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'GameRequests' when IDENTITY_INSERT is set to OFF.

...
var config = new MapperConfiguration(cfg =>
                {
                    cfg.CreateMap<RequestDto, GameRequest>();
                    cfg.CreateMap<GameRequest, GameConfirmRequest>();
                    cfg.CreateMap<GameConfirmResponse, GameConfirmResponseDto>();
                    cfg.CreateMap<Coupon, CouponDto>();
                    cfg.CreateMap<GameRequest, GameRequestDto>();
                });
                var iMapper = config.CreateMapper();
                var gameRequest = iMapper.Map<RequestDto, GameRequest>(requestDto);
                //Unique reference ID
                //gameRequest.referenceId = Guid.NewGuid().ToString();

                var gameRequestDto = iMapper.Map<GameRequest, GameRequestDto>(gameRequest);

                //Create signature
                gameRequest = Utilities.CreateSignature(gameRequestDto, RequestType.Initiate);

                //Add initiation request into database
                _unitOfWork.GameRepository.Insert(gameRequest);

                #region Call Razer initiate/confirm

                //Call Razer for initiation
                response = await Utilities.CallRazer(gameRequest, "purchaseinitiation");

                //Read response
                var htmlResponse = await response.Content.ReadAsStringAsync();
                var gameResponse = JsonConvert.DeserializeObject<GameResponse>(htmlResponse);

                //Adding initiation response into database
                _unitOfWork.GameResponseRepository.Insert(gameResponse);

                if (gameResponse.initiationResultCode == "00")
                {
                    gameRequestDto = iMapper.Map<GameRequest, GameRequestDto>(gameRequest);
                    gameRequestDto.validatedToken = gameResponse.validatedToken;
                    //Create signature
                    var gameConfirmRequest = Utilities.CreateSignature(gameRequestDto, RequestType.Confirm);

                    //Transform DTO into GameRequest for calling Razer Initiate
                    var gameConfirmRequests = iMapper.Map<GameRequest, GameConfirmRequest>(gameConfirmRequest);

                    //Add confirm request into database
                    _unitOfWork.GameConfirmRequestRepository.Insert(gameConfirmRequests);

                    //Call Razer for confirm
                    response = await Utilities.CallRazer(gameRequest, "purchaseconfirmation");
...
              
                   await _unitOfWork.SaveAsync();

 

[Skydiver]

The error seems to be pretty self-explanatory.

 

[NoUserHere]

SET IDENTITY_INSERT YOURTABLE_NAMEHERE ON;

See SET IDENTITY_INSERT (Transact-SQL) - SQL Server

 

[NoUserHere]

Based on the remarks covered in the docs, wouldn't it be best to set identity_insert back to off once you're done inserting? (Just for the sake of needing to do the same on some other table?)

Remarks state :

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

 

[raysefo]

I removed the id column and made referenceId PK. But still getting this error:

Cannot insert the value NULL into column 'referenceId', table 'GameAPI.dbo.GameRequests'; column does not allow nulls. INSERT fails.

What should I do more?

public class GameRequest
    {
        
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid referenceId { get; set; }
...

 

[raysefo]

I manually changed the migration file;
AlterColumn("dbo.GameRequests", "referenceId", c => c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()"));
This step is passed now I have to get the generated GUID and use it in the rest of the code.

 

[raysefo]

At this point can I somehow get the generated GUID?

_unitOfWork.GameRepository.Insert(gameRequest);

I think GUID generated when I call SaveAsync? But it is at the end, I need to get the GUID somehow?

//Add initiation request into database
                _unitOfWork.GameRepository.Insert(gameRequest);

                #region Call Razer initiate/confirm

                //Call Razer for initiation
                response = await Utilities.CallRazer(gameRequest, "purchaseinitiation");

                //Read response
                var htmlResponse = await response.Content.ReadAsStringAsync();
                var gameResponse = JsonConvert.DeserializeObject<GameResponse>(htmlResponse);

                //Adding initiation response into database
                _unitOfWork.GameResponseRepository.Insert(gameResponse);

                if (gameResponse.initiationResultCode == "00")
                {
                    gameRequestDto = iMapper.Map<GameRequest, GameRequestDto>(gameRequest);
                    gameRequestDto.validatedToken = gameResponse.validatedToken;
                    //Create signature
                    var gameConfirmRequest = Utilities.CreateSignature(gameRequestDto, RequestType.Confirm);

                    //Transform DTO into GameRequest for calling Razer Initiate
                    var gameConfirmRequests = iMapper.Map<GameRequest, GameConfirmRequest>(gameConfirmRequest);

                    //Add confirm request into database
                    _unitOfWork.GameConfirmRequestRepository.Insert(gameConfirmRequests);

                    //Call Razer for confirm
                    response = await Utilities.CallRazer(gameRequest, "purchaseconfirmation");

                    //Read response
                    htmlResponse = await response.Content.ReadAsStringAsync();
                    var gameConfirmResponse = JsonConvert.DeserializeObject<GameConfirmResponse>(htmlResponse);

                    //Add confirm response into database
                    _unitOfWork.GameConfirmResponseRepository.Insert(gameConfirmResponse);
                }

                #endregion

                await _unitOfWork.SaveAsync();

 

[raysefo]

Does it make sense if I use transaction scope and adding SaveAsync just after Insert?

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
 {
              ...

                //Add initiation request into database
                _unitOfWork.GameRepository.Insert(gameRequest);
               await _unitOfWork.SaveAsync();

                #region Call Razer initiate/confirm

                //Call Razer for initiation
                response = await Utilities.CallRazer(gameRequest, "purchaseinitiation");

                //Read response
                var htmlResponse = await response.Content.ReadAsStringAsync();
                var gameResponse = JsonConvert.DeserializeObject<GameResponse>(htmlResponse);

                //Adding initiation response into database
                _unitOfWork.GameResponseRepository.Insert(gameResponse);

                if (gameResponse.initiationResultCode == "00")
                {
                    gameRequestDto = iMapper.Map<GameRequest, GameRequestDto>(gameRequest);
                    gameRequestDto.validatedToken = gameResponse.validatedToken;
                    //Create signature
                    var gameConfirmRequest = Utilities.CreateSignature(gameRequestDto, RequestType.Confirm);

                    //Transform DTO into GameRequest for calling Razer Initiate
                    var gameConfirmRequests = iMapper.Map<GameRequest, GameConfirmRequest>(gameConfirmRequest);

                    //Add confirm request into database
                    _unitOfWork.GameConfirmRequestRepository.Insert(gameConfirmRequests);

                    //Call Razer for confirm
                    response = await Utilities.CallRazer(gameRequest, "purchaseconfirmation");

                    //Read response
                    htmlResponse = await response.Content.ReadAsStringAsync();
                    var gameConfirmResponse = JsonConvert.DeserializeObject<GameConfirmResponse>(htmlResponse);

                    //Add confirm response into database
                    _unitOfWork.GameConfirmResponseRepository.Insert(gameConfirmResponse);
                }

                #endregion

                await _unitOfWork.SaveAsync();
                scope.Complete();
}

 

[raysefo]

Does this rollback? Any suggestions?

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
 {
              ...

                //Add initiation request into database
                _unitOfWork.GameRepository.Insert(gameRequest);
               await _unitOfWork.SaveAsync();

                ...

                //Adding initiation response into database
                _unitOfWork.GameResponseRepository.Insert(gameResponse);
                
                ...
              
                    //Add confirm request into database
                    _unitOfWork.GameConfirmRequestRepository.Insert(gameConfirmRequests);
 
               ...
                  

                    //Add confirm response into database
                    _unitOfWork.GameConfirmResponseRepository.Insert(gameConfirmResponse);
                }

              ....
          

                await _unitOfWork.SaveAsync();
                scope.Complete();
}

 

[Skydiver]

Well, if it doesn't rollback if an exception is thrown or if you explicitly call a rollback with the transaction scope, then there is a bug in either the database transaction provider or the .NET Framework's transaction scope. Considering how database devs are obsessed with getting transactions and rollbacks to work correctly, I would say that the probability of the code failing to rollback would be pretty low. (This is all predicated on all the elements within the transaction scope cooperating on the transaction.)